Data Manipulation Part 1 - Groupby Operations¶

Chapter 9 in Python for Data Analysis demonstrates a variety of methods to analyze data via data aggregation and grouping operations. Those are the focus of this session. Our overall goal for this session is to do Exploratory Data Analysis, which is essentially looking at and probing our data to learn about the patterns we can discover in them. Often this can generate a better understanding of problems in the data, as well as revealing relationships that might be worth exploring in more depth.

We will use Census Summary File 1 data for much of this session to illustrate concepts using real data.

Below we load a table I created previously with an extract of data from the 2010 Census, Summary File 1, for the Bay Area. The file is being loaded from an HDF5 datastore - this and other file formats are covered in Chapter 6.

Groupby and Aggregation Operations¶

Groupby is a powerful method in pandas that follows the split-apply-combine approach to data. As shown in Figure 9-1 in the context of a sum operation, the data is first split into groups that share the same key values. Then an operation, in this case a sum, is applied to each group. Then the results are combined.

The built-in aggregation methods available for groupby operations include:

• count
• sum
• mean
• median
• std, var
• min, max
• first, last

You can also apply your own functions as aggregation methods.

Let's apply this approach to computing total population in each county in our dataset. We can do this in two steps to help explain what is happening. First we create a groupby object, using county codes to group all the census blocks in sf1 into groups that share the same county code.

Now that we have this grouping object that represents the split part of the workflow in the figure above, we can apply operations and combine the results using methods like sum:

Doing this in two steps like above is really just to clarify the two parts of the split and apply process that happen within a groupy operation. Normally we would not bother separately creating a groupby object -- we would just do this in one line:

Let's add county names to the dataframe so we get more readable output, and rerun this aggregation.

Let's add county_name as a column in the dataframe. It would be easy to append it as the last column with a merge, but let's see how to insert it in a specified location so that it is easier to read when we browse the data. We can insert it as the 4th column, between county and tract, like so:

Now we can print the results of summing population by county_name:

We might want to capture the result in a DataFrame if we want to use it in other processing, like merging the results to the original DataFrame.

Here we merge the county total population with sf1 and create a new DataFrame.

Let's say we wanted to compute the population per square mile by county using the groupby method. We could go ahead and create another dataframe with total area by county than then do the division of total population by total area.

Or of course we could have done this whole thing in one line:

Your turn to practice:¶

Count the number of census blocks per county.

Calculate total households per county.

Calculate percent renters by county. (Careful not to calculate the mean percent rental across blocks in a county)

Calculate percent vacant by county.

Calculate mean, min and max pop_sqmi (at the block level) by county.

Calculate the 90th percentile of pop_sqmi (at the block level) by county.

Transforming Data with Groupby¶

In some cases you may want to apply a function to your data, by group. An example would be to normalize a column by a mean of each group. Say we wanted to subtract the mean population density of each county from the population density of each census block. We could write a function to subtract the mean from each value, and then use the transform operation to apply this to each group:

Now we can apply this tranformation to columns in our dataframe. As examples, let's 'demean' the pop_sqmi and pct_rent columns, subtracting the county-wide mean of these values from the block-specific values, so that the result is transformed to have a mean of zero within each county.

To check the results, we print the means per county, then the original values for the first 5 rows, then the transformed results. The transformed results we should be able to calculate by subtracting the appropriate county mean from the block value.

We can merge these transformed results on to the original DataFrame, and check the means of the original variables and the tranformed ones. The transformed ones should be arbitrarily close to zero.

Apply is a method we have learned previously, which allows us to apply a function to each row in a DataFrame. We can also combine apply with groupby to apply functions based on group membership. For example, the function 'top' sorts an array and selects the top n rows from it. We provide some defaults for the arguments of how many rows, and the column to use for the selection:

Using this on the full dataset and setting the number of rows and the column to get the top values for, in this case using pct_rent to override the default argument, we get the top 10 blocks in the region in terms of percentage rental.

Below we apply this with groupby and use the defaults for n and column, and it applies the function within each county and concatenates the results, producing the top 5 blocks on pop_sqmi for each county in the region.

Here we pass arguments to the function to set n and the column to select the top value from.

Experimenting with Rental Listings Merged with SF1¶

Let's read the geocoded rental listings for the Bay Area to begin. We will make sure the fips_block column is read as a string dtype so we can merge properly with the census data. It has leading zeros and is a string in the census data.

And merge it with the census data using the FIPS block codes, which are named differently in the two DataFrames.

Now we can begin looking at this merged dataset. Let's start by computing mean rents by county.

Those patterns generally conform to our expectations, with San Francisco highest and Solano lowest.

Let's examine these patterns by county and number of bedrooms. We can just use two columns in the groupby to do this.

That output is a bit too long, since there are rows for many bedroom counts (and few units have > 6 bedrooms!). Let's trim this by looking only at units with less than 4 bedrooms.

Let's plot a bar chart of these rents by county and bedroom.

That bar chart is not ideal. Too small, and it would be nicer to make it separate colors for each number of bedrooms. Notice that we have a hierarchical index (county, then bedrooms within county). We can use unstack on this to make it easier to work with for the bar charts, or even to look at as a table:

Now we can plot a bar chart with the unstacked data, and add a title and set the figure size.

Pandas also provides a syntax for doing this that is familiar for spreadsheet users: the pivot_table. You can see below that using pivot_table achieves exactly the same result.

In addition to providing a convenience interface to groupby, pivot_table also can add partial totals, or margins.

Although mean is the default type of aggregation in pivot_table, you can use other methods like len, to get a frequency crosstab with the count of observations in each cell of the table, as well as the margins. Let's remove the filter on number of bedrooms to see the full frequency distribution.

A simpler way to generate a crosstabulation is also built in to pandas: the crosstab method.

Besides the simpler syntax, the other advantage of crosstab for doing this is that you can normalize the crosstab, dividing all the cell values by the grand total. This tells us the fraction of the region's total listings that are in each combination of county and number of bedrooms.

We have the option to normalize on the index, which is the county_name in this case, so we see the fraction of the county listings that are of each number of bedrooms. The rows sum to 1.

The counterpart is to normalize by column, in which case we see the fraction of 1 bedroom listings in the region that are in each county, and so on for different bedroom counts.

What if we want to look at more statistics than just mean? We can combine several aggregation methods and compute them at one time.

Remember the 'top' function we defined earlier? We can use it here to get the top rental listing in each county, for each bedroom size.

Exploring Correlations in the Data¶

Pandas provides simple ways of computing correlation coefficients among the columns in your DataFrame. If you use corr() on a full DF, it will produce a large correlation table. A bit hard to navigate and you mostly would not be interested in some of these results.