Selecting and Modifying Rows/Columns

This subchapter will go into greater detail on how you can select/modify rows and columns in pandas.

First, let us load in the same dataset as the first subchapter in the Pandas chapter. As a reminder, this dataset has beer sales across 50 continental states in the US. It is sourced from Salience and Taxation: Theory and Evidence by Chetty, Looney, and Kroft (AER 2009), and it includes 7 columns:

• st_name: the state abbreviation

• year: the year the data was recorded

• c_beer: the quantity of beer consumed, in thousands of gallons

• beer_tax: the ad valorem tax, as a percentage

• btax_dollars: the excise tax, represented in dollars per case (24 cans) of beer

• population: the population of the state, in thousands

• salestax: the sales tax percentage

Extracting Rows/Columns that Satisfy a Given Condition¶

An important task we want to accomplish when looking at data is to filter out all rows that satisfy a certain condition. In other words, we want to extract all the rows where a certain thing is accomplished. Conditional selection helps us achieve exactly this, extracting all rows that satisfy a certain condition.

In the previous subchapter, we briefly gave an example of how we can use .loc to extract all rows that match certain values. To be more precise, when we pass in certain values for .loc and iloc to use, they generate an array of True/False values and use that to determine which values to include. This is very similar to our prior discussion on NumPy slicing.

For example, let’s say we want all the data for the state of Alabama. Then, we would want all rows where st_name is CA. We can generate a Series of True/False values corresponding to whether each row has a st_name of AL using the code below.

Now, to extract all rows where the state is Alabama, we can simply pass in this boolean series into the .loc operator.

Similarly, we can also use the context dependent [] operator.

We could also use the same technique to extract all columns that are integers (but not floats, so they do not contain decimals).

Alternatively, if we wanted to extract all the rows where the index is less than 30, we could similarly use the .iloc operator.

As you can imagine, quickly and easily generating these boolean arrays/Series is very important for accessing our elements!

Logical Operators¶

What if we have multiple conditions we want to filter on? Let’s say we want to look at all the rows where beer_tax is less than 3 and btax_dollars is less than 0.5. We can generate boolean filters for both of those conditions.

How can we combine both of these boolean arrays? Well, since we’re interested in cases where both of these are true, we can use the & operator. The & operator is the logical way of expressing you only want the cases where elements from both the arrays are true.

The code is shown below. Note the parenthesis around both the boolean arrays - this is an absolutely crucial piece of syntax!

Alternatively, if we were looking for the states where either beer_tax is less than 3 or btax_dollars is less than 0.5, we could use the logical | operator. The | operator is the logical way of expressing you only want the cases where an element from either array is true.

What about cases where either beer_tax is less than 3 or btax_dollars is less than 0.5, but not both? Well, here we could use the ^ operator, which is the logical way of expressing you only want the cases where an element from either of the arrays is true, but both the elements are not true.

Notice how the number of columns with the & operator plus the number of columns with the ^ operator sum to the number of columns with the | operator (628 + 516 = 1144).

Finally, what if you’re interested in the cases where something isn’t true? For example, what if you’re interested in all the cases where the st_name isn’t AL? Well, then you can use the ~ operator, which logically represents the inverse of a boolean array (so it converts all true values into false and all false values intro true).

Here’s a table summarizing all the different logical operators to combine boolean arrays.

Multiple Labels¶

If you want to keep all rows where a certain column is in a list of given values, you can use the .isin() method. This is logically equivalent to using the | operator multiple times, but it’s easier. For example, below we get all rows corresponding to the states of California, Florida, Texas and Nebraska.

Editing Rows¶

We can also use conditional selection to edit certain rows. For example, let’s say we found out we made an error with measuring beer_tax for the state of Alabama, and the true beer tax is actually 0.1 higher than what we’ve measured. We can easily use boolean filtering to select the columns where we’ve made this mistake and fix them!

It’s that simple! However, since our data was (presumably) correctly measured, let us revert this change.

Modifying Columns and Indices¶

Now, we move on to modifying the columns in our DataFrames. The first task we will consider is how to add a new column.

Adding Columns¶

Let’s say we want to add a new column where we calculate the total percentage taxed by combining the beer_tax and salestax. Both of these % taxes compound, so we will need to be careful with the math! Below, we calculate a series with the sum of these two taxes, as a percent.

Now, we want to set this series equal to a new column named total_percent_tax in our dataframe. The syntax for doing this follows df[New_Column_Name] = Series/Array_Name, and is shown below.

Voila, our new column has been added!

Renaming Columns¶

Looking at the table above, c_beer feels like a confusing name. Instead, let us change to beer_consumption, a more informative name! To do so, we can use the DataFrame rename method, as shown below.

As we can see, the columns parameter of the rename method takes in a dictionary where all the keys are the old column names and all the values are the new column names.

Note, however, that the original DataFrame df has not changed yet. Instead, pandas methods just return a copy of the modified DataFrame for you to see. To change the original dataframe, you can either manually assign the new DataFrame to df or set the inplace parameter of the rename method to True.

Dropping Columns¶

Finally, let’s say we want to drop a column. For this, we can use the DataFrame drop method. For example, we drop the salestax column below.

Once again, the drop method has a columns parameter that takes in a list of the columns to drop (no need for a dictionary here as there are no key-value pairs).

However, you can also simply pass in a list of the columns to drop and then use the axis parameter to specify they represent column names, as done below. We encourage you to read the documentation if you’re interested in further detail!

Finally, once again the drop parameter does not modify the original table. To modify the original table, you can once again reassign it to the original table or use the inplace method.

Modifying the Index¶

To modify the index of a DataFrame, there are three main methods, as discussed below.

.set_index() Sets the index of the DataFrame to a particular column.

.reset_index() converts the current index into a column and makes the new index as a default numbering from 0 to the very last row in the DataFrame.

Finally, you can manually set the index equal to something else by simply reassigning the DataFrame/Series attribute.