How To Create New Columns From Subcategories In Pandas?

I'm trying to take subcategories and place them into columns so I can compute values for each column. For example, what I have now is: c1 c2 c3 0 123 Orange 12 1 123 C

Solution 1:

Something like this will do the trick for you:

First if you need breakdown per Colour/Transport - you need to classify it accordingly, so:

>>> df
    c1      c2  c3         c4
0  123  Orange  12     Colour
1  123     Car  15  Transport
2  123    Blue  14     Colour
3  123    Bike  13  Transport
4  234     Red   9     Colour
5  234     Bus   4  Transport
6  234   Train  19  Transport
7  234  Purple  17     Colour

Then in order to get exactly what you want (so kind of aggregation with "sumif"):

>>> df.assign(c3_Colour=df["c3"][df["c4"]=="Colour"], c3_Transport=df["c3"][df["c4"]=="Transport"]).fillna(0).groupby(c1).agg({"c3_Colour":sum, "c3_Transport": sum})
     c3_Colour  c3_Transport
123       26.0          28.0
234       26.0          23.0