image
Using GROUP BY
Start course
Difficulty
Intermediate
Duration
57m
Students
1168
Ratings
4.2/5
Description

Pandas is Python’s ETL package for structured data and is built on top of NumPy, designed to mimic the functionality of R data frames. It provides a convenient way to handle tabular data and can perform all SQL functionalities, including group-by and join. Furthermore, it's compatible with many other Data Science packages, including visualization packages such as Matplotlib and Seaborn.

In this course, we are going to explore Pandas and show you how it can be used as a powerful data manipulation tool. We'll start off by looking at arrays, queries, and dataframes, and then we'll look specifically at the groupby function before rounding off the course by looking at how the merge and join methods can be used in Pandas.

If you have any feedback relating to this course, feel free to tell us about it at support@cloudacademy.com.

Learning Objectives

  • Understand the fundamentals of the Pandas library in Python and how it is used to handle data
  • Learn how to work with arrays, queries, and dataframes
  • Learn how to use the groupby, merge, and join methods in Pandas

Intended Audience

This Course is intended for data engineers, data scientists, or anyone who wants to use the Pandas library in Python for handling data.

Pre-requisites

To get the most out of this course, you should already have a good working knowledge of Python and data visualization techniques.

Resources

The dataset(s) used in the course can be found in the following GitHub repo: https://github.com/cloudacademy/practical-data-science-python 

Transcript

In this section, we're going to have a look at GROUP BY. So within the Titanic dataset, we can group by using a method called, family enough GROUP BY. And this is group by without a hyphen in between it. When we want to group by something, we choose a category of a column. If I group by class, then what you get out is this thing called a generic data frame group by object.

Now, the reason we get this object is because Python and Pandas, don't really know what to do, with a collection of data frames. They don't really know which one to put out. So you've got to perform a summary operation to be able to get a summary of your groups, for example. So I'm just going to run the count operation. It's one of the more simple ones that we can have.

So what we've done is we've grouped by class, class has values first, second, and third. And then what we're doing is we're counting every one within the number of entries we have for those values in each column. Can perform any summary operation, anything that's going to result in a numeric attribute so we can perform something like the mean, now it will only compute the mean for numeric columns. And some of our columns are numeric.

So, we're getting the mean of the passenger class, because that's a numeric column. So technically the mean of passenger class is two, and, we're getting the mean of survive because that is binary one or zero. But we'd get an idea when we take the mean or something binary, of the rough proportion of the people that did or did not survive. A lot of people in first class tended to survive in comparison with the other classes here.

So, any operation would work here, so we can have, for example, standard deviation across all our classes as well. All of the numeric columns. So it's a good thing to be able to do. And what else might we want to do? Well, we might want to group by more than one column, grouping by multiple axis. So I'm grouping by class first, and then whether or not people survived. So I could do a count of these values as well.

So what we can see here, is that we got a lot of first class people who survived and then, a lot of third class people who did not survive. And there's a smaller number of third class people who did survive in comparison with those in first class. Can realistically group by many as these as we want.

So what we should notice is that, when we have a look at this table that highlights appropriately, as to which grouping we're part of. So we can add more and more of these as we want to. We've got individual segments for everything. And we can perform calculations over the various axis in this data frame.

So I'm going to call this grouped_df. If I want to access elements of it, is generally going to be best to use dot loc. I want every column for the first class people who came, who didn't survive and who embarked in sea, which I believe is Cherbourg in France. And then I want to have a column for that. Then I want to work with that as something called a multi-index object. All that means is I've got to specify each individual path, that I want to go down. And if I leave one of them off like this, then I will get for example, I've pulled out all the first class people who died, I have now obtained all the various ports, which they embarked, et cetera, et cetera, Is the index where I've grouped, by where the people embarked.

I've taken a sort of subsection of the data. I have jumped the gun a little bit, and, I've sort of gone more complicated than I normally would. So now I just want to show you the simple way of obtaining your groups data. So you don't have to perform a summary operation. I could just group by something like class and then parse in, once I've got this guy here, I could just use the get group. Get group. Get group method. And then, the class as well.

So these were the first class people. It gives me all the second class people and so on and so forth. So, I can pull out individual groups. I could run a Lambda for example, a simple Lambda would be, X percentage two double equals zero. This is checking if the count of people in each of the boxes is even. But I could do something a bit more complicated, such as min max scaling. That's an example of how I could do this using a one line Lambda function, where I take every value away from the minimum value in the column. And then divide by the max minus the min for each column as well.

So, it's a form of scaling our values here. So I think I would actually want to do this without counting. Now we can group by our own boolean conditions as well, Titanic. I could group by titanic dot group by, only those values for which titanic dot loc fare is greater than 70. And then I can get the count of these, for example. And this gives me the total count of people. The total count of entries in each column, out of those whose fare was greater than 70 in those whose fair was below 70.

So this is me specifying my own condition, which I want to group by. So I'm not just restricted to say, categorical columns. So, anything that evaluates as a boolean condition, I can do this with. I can also map a function over a data frame, and have 10 groups based upon which bucket I want to put people in.

Lectures

About the Author

Delivering training and developing courseware for multiple aspects across Data Science curriculum, constantly updating and adapting to new trends and methods.