Raise TypeError when adding new column to a pandas DataFrame

Question

I've been watching an online course about data analysis using Python. I came across a problem when following exactly what the instructor did. Basically, I pulled a data frame called "flights" from seaborn and set the index "year" and "month" and unstacked it. The following codes are used:

import seaborn
import pandas as pd
flights = seaborn.load_dataset("flights")
flights_indexed = flights.set_index(["year","month"])
flights_unstacked = flights_indexed.unstack()
flights_unstacked

the final data frame is like this

Then I am trying to add a new column called "Total" at the end for the sum of each year using the following code:

flights_unstacked["passengers"]["Total"] = flights_unstacked.sum(axis = 1)

But it raised a TypeError: cannot insert an item into a CategoricalIndex that is not already an existing category.

I am new to data manipulation using pandas. Anyone can tell me how to fix this? Is this a version issue, because the online instructor did exactly the same thing but his works just fine. PS: I use Python 2.7 and pandas 0.20.3.

Peter Leimbigler · Accepted Answer · 2018-01-15 04:47:19Z

4

The seaborn.load_dataset line detects the month column as a category data type. To get around this error, cast categorical to str with this line right after flights = seaborn.load_dataset("flights"):

flights["month"] = flights["month"].astype(str)

To sort the month strings in chronological order, first drop the top level (level=0) of the columns of flights_unstacked (this level holds the single value passengers):

import seaborn
import pandas as pd

flights = seaborn.load_dataset("flights")
flights["month"] = flights["month"].astype(str)

flights_indexed = flights.set_index(["year", "month"])
flights_unstacked = flights_indexed.unstack()
flights_unstacked.columns = flights_unstacked.columns.droplevel(0)

Then reindex the month-string columns according to a list of month strings that you pre-built in chronological order:

import calendar
months = [calendar.month_name[i] for i in range(1, 13)]
flights_unstacked = flights_unstacked[months]

Finally, you can add a column of totals:

flights_unstacked["Total"] = flights_unstacked.sum(axis=1)

Result:

In [329]: flights_unstacked
Out[329]:
month  January  February  March  April  May  June  July  August  September  October  November  December  Total
year                                                                                                          
1949       112       118    132    129  121   135   148     148        136      119       104       118   1520
1950       115       126    141    135  125   149   170     170        158      133       114       140   1676
1951       145       150    178    163  172   178   199     199        184      162       146       166   2042
1952       171       180    193    181  183   218   230     242        209      191       172       194   2364
1953       196       196    236    235  229   243   264     272        237      211       180       201   2700
1954       204       188    235    227  234   264   302     293        259      229       203       229   2867
1955       242       233    267    269  270   315   364     347        312      274       237       278   3408
1956       284       277    317    313  318   374   413     405        355      306       271       306   3939
1957       315       301    356    348  355   422   465     467        404      347       305       336   4421
1958       340       318    362    348  363   435   491     505        404      359       310       337   4572
1959       360       342    406    396  420   472   548     559        463      407       362       405   5140
1960       417       391    419    461  472   535   622     606        508      461       390       432   5714

edited Jan 15, 2018 at 4:47

answered Jan 14, 2018 at 20:35

Peter Leimbigler

11.1k1 gold badge27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MaxU - stand with Ukraine Over a year ago

I hope you don't mind that I've slightly modified your answer?

richard Over a year ago

Thank you for the help. But it seems that the unstack method automatically sorts the month names as they are now string instead of categorical type. Is there any way to fix this since I still want the month names in the original order. Also, is there a way to cast the type of the month column right before the unstack method so that I won't change anything about the original dataset "flights". Thank you.

Peter Leimbigler Over a year ago

@MaxU, no problem at all. Thanks for improving the answer!

Peter Leimbigler Over a year ago

@richard, I've edited my answer to sort the month columns in chronological order.

Ajitsen Over a year ago

Thanks a lot for the answer. The final statement still gave me "SettingWithCopyWarning" warning hence had to switch off Copy Warning pd.options.mode.chained_assignment = None flights_unstacked["Total"] = flights_unstacked.sum(axis=1)

Collectives™ on Stack Overflow

Raise TypeError when adding new column to a pandas DataFrame

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related