Operations with Columns

cereal

	name	mfr_type	calories	protein	...	fat	carbo	rating	hot
0	100% Bran	N-Cold	70	4	...	1	5.0	68.402973	False
1	100% Natural Bran	Q-Cold	120	3	...	5	8.0	33.983679	False
2	All-Bran	K-Cold	70	4	...	1	7.0	59.425505	False
...	...	...	...	...	...	...	...	...	...
74	Wheat Chex	R-Cold	100	3	...	1	17.0	49.787445	False
75	Wheaties	G-Cold	100	3	...	1	17.0	51.592193	False
76	Wheaties Honey Gold	G-Cold	110	2	...	1	16.0	36.187559	False

77 rows × 9 columns

We’ve just learned what Python values work with certain operations, but what about dataframes?

Let’s take a look at our cereal dataframe once more.

cereal.dtypes

name         object
mfr_type     object
calories     object
             ...   
carbo       float64
rating      float64
hot            bool
Length: 9, dtype: object

cereal.describe()

	protein	fiber	fat	carbo	rating
count	77.000000	77.000000	77.000000	77.000000	77.000000
mean	2.545455	2.151948	1.012987	14.623377	42.665705
std	1.094790	2.383364	1.006473	4.188138	14.047289
...	...	...	...	...	...
50%	3.000000	2.000000	1.000000	14.000000	40.400208
75%	3.000000	3.000000	2.000000	17.000000	50.828392
max	6.000000	14.000000	5.000000	23.000000	93.704912

8 rows × 5 columns

It’s always a good idea to see what column types we have before operating on them, since they may not be of the type we expect.

In this case, we discover that the calories column is of dtype object, which isn’t the int64 category we expected. This can sometimes happen when reading in data - pandas doesn’t always correctly guess what the type of a column should be.

Since calories is not a numerical type, it will not show up in our summary statistics if we call .describe():

cereal['calories'].sum()

'70120705011011011013090901201101201101101101001101101101001101001001101101001201201101001101001101201201101101101401101001101001501501601001201409013012010050501001001201009011011080909011011090110140100110110100100110'

cereal['calories'].astype('int')

0      70
1     120
2      70
     ... 
74    100
75    100
76    110
Name: calories, Length: 77, dtype: int64

If we attempt to sum the column, we get a concatenation of the column because the values are being treated as strings.

We saw earlier that when we add strings, they concatenate together.

To go forward with any analysis, we are going to have to convert this column to a numeric type.

Luckily, there is an easy way to do it using the verb .astype().

cereal = cereal.assign(calories=cereal['calories'].astype('int'))

cereal.dtypes

name         object
mfr_type     object
calories      int64
             ...   
carbo       float64
rating      float64
hot            bool
Length: 9, dtype: object

We can now use the verb .assign() to add the casted column back to the cereal dataframe. By using the same name calories, we overwrite the previous column, but now as dtype int.

Great, it looks like we are back on track.

cereal.head(5)

	name	mfr_type	calories	protein	...	fat	carbo	rating	hot
0	100% Bran	N-Cold	70	4	...	1	5.0	68.402973	False
1	100% Natural Bran	Q-Cold	120	3	...	5	8.0	33.983679	False
2	All-Bran	K-Cold	70	4	...	1	7.0	59.425505	False
3	All-Bran with Extra Fiber	K-Cold	50	4	...	0	8.0	93.704912	False
4	Almond Delight	R-Cold	110	2	...	2	14.0	34.384843	False

5 rows × 9 columns

cereal['calories'].sum()

np.int64(8230)

If we take the sum of the column now, we get the actual sum.

object columns

cereal['mfr_type'].mean()

TypeError: Could not convert 'N-ColdQ-ColdK-ColdK-ColdR-ColdG-ColdK-ColdG-ColdR-ColdP-ColdQ-ColdG-ColdG-ColdG-ColdG-ColdR-ColdK-ColdK-ColdG-ColdK-ColdN-HotK-ColdG-ColdR-ColdK-ColdK-ColdK-ColdP-ColdK-ColdP-ColdP-ColdG-ColdP-ColdP-ColdP-ColdQ-ColdG-ColdP-ColdK-ColdK-ColdG-ColdQ-ColdG-ColdA-HotR-ColdR-ColdK-ColdG-ColdK-ColdK-ColdK-ColdG-ColdP-ColdK-ColdQ-ColdQ-ColdQ-ColdQ-HotK-ColdG-ColdK-ColdR-ColdK-ColdN-ColdN-ColdN-ColdK-ColdK-ColdN-ColdG-ColdG-ColdG-ColdG-ColdG-ColdR-ColdG-ColdG-Cold' to numeric

Detailed traceback: 
  File "<string>", line 1, in <module>
  File "/usr/local/lib/python3.12/site-packages/pandas/core/series.py", line 6549, in mean
  ...

cereal['mfr_type'].sum()

'N-ColdQ-ColdK-ColdK-ColdR-ColdG-ColdK-ColdG-ColdR-ColdP-ColdQ-ColdG-ColdG-ColdG-ColdG-ColdR-ColdK-ColdK-ColdG-ColdK-ColdN-HotK-ColdG-ColdR-ColdK-ColdK-ColdK-ColdP-ColdK-ColdP-ColdP-ColdG-ColdP-ColdP-ColdP-ColdQ-ColdG-ColdP-ColdK-ColdK-ColdG-ColdQ-ColdG-ColdA-HotR-ColdR-ColdK-ColdG-ColdK-ColdK-ColdK-ColdG-ColdP-ColdK-ColdQ-ColdQ-ColdQ-ColdQ-HotK-ColdG-ColdK-ColdR-ColdK-ColdN-ColdN-ColdN-ColdK-ColdK-ColdN-ColdG-ColdG-ColdG-ColdG-ColdG-ColdR-ColdG-ColdG-Cold'

What happens if we try to take summary statistics of some of the other columns?

Yikes! Let’s not take the mean of columns of dtype object.

As we saw before, taking a .sum() of a column concatenates the values together.

Bool

cereal['hot'].mean()

np.float64(0.03896103896103896)

What about the column of type bool?

Since Booleans are cast to 0 and 1 for True and False values respectively, we can take the sum of a column to obtain the total number of True values.

.mean() works by summing up all the values and divides them by the total number of rows. In the case where the column is of dtype bool since True has a value of 1 and False has a value of 0, the mean is calculated as the total number of True values divided by the total number of True and False values. In other words, this gives you the fraction of cases that are True.

Axis Argument

cereal.head(3)

	name	mfr_type	calories	protein	...	fat	carbo	rating	hot
0	100% Bran	N-Cold	70	4	...	1	5.0	68.402973	False
1	100% Natural Bran	Q-Cold	120	3	...	5	8.0	33.983679	False
2	All-Bran	K-Cold	70	4	...	1	7.0	59.425505	False

3 rows × 9 columns

cereal.loc[:, 'protein': 'carbo'].sum(axis=1)

0     20.0
1     18.0
2     21.0
      ... 
74    24.0
75    24.0
76    20.0
Length: 77, dtype: float64

We are quite familiar with taking the mean and sum of entire columns now, but there are times where we want the mean or sum of the values in a row.

Perhaps we wanted the total grams of protein,fiber, fat, and carbo for each cereal?

Remember, when we discussed the argument axis in Module 3?

We can use it in our operations as well.

axis=1 refers to the calculation being done for each row, across multiple columns, whereas axis=0 (which is the default for aggregation verbs) refers to the calculation for each column across multiple rows.

cereal.loc[:, 'protein': 'carbo'].sum(axis=1)

0     20.0
1     18.0
2     21.0
      ... 
74    24.0
75    24.0
76    20.0
Length: 77, dtype: float64

cereal = cereal.assign(total_pffc=cereal.loc[:, 'protein': 'carbo'].sum(axis=1))
cereal.head(4)

	name	mfr_type	calories	protein	...	carbo	rating	hot	total_pffc
0	100% Bran	N-Cold	70	4	...	5.0	68.402973	False	20.0
1	100% Natural Bran	Q-Cold	120	3	...	8.0	33.983679	False	18.0
2	All-Bran	K-Cold	70	4	...	7.0	59.425505	False	21.0
3	All-Bran with Extra Fiber	K-Cold	50	4	...	8.0	93.704912	False	26.0

4 rows × 10 columns

Although this produces the totals, we may want the result as an additional column in the dataframe.

That means we will have to combine it with .assign().

Here we add it as a column named total_pffc into the cereal dataframe.

This syntax works for calculating the mean over multiple columns too.

Let’s apply what we learned!