pandas - Python column hierarchy creation for groupby

Question

I have a question I can't get my head around, although I'm sure it's dead simple.
I import an excel file, with sales figures of cars.
I need to be able to report on it by country.
The country is not part of the file, but I have the info of all the cars of each country. (I can create another DataFrame from it, or a list, or dict...) My idea was to create a hierarchie in the columns. I just can't figure out how.

    import pandas as pd
    german=['BMW','Audi','Mercedes','Volkswagen']
    italian=['Fiat','Ferrari']
    
    toclean=pd.DataFrame([['car','4','5',10,20,15,50,20,13,24]],
            columns=['type','wheels','seats','BMW','Audi','Mercedes','Volkswagen','Fiat','Ferrari','SEAT'])

type	wheels	seats	BMW	Audi	Mercedes	Volkswagen	Fiat	Ferrari	SEAT
car.	4	5	10	20	15	50	20	13	24

question from:https://stackoverflow.com/questions/65835967/python-column-hierarchy-creation-for-groupby

Answer 1

Something like this?

def country(brand):
    if brand in german:
        return 'germany'
    elif brand in italian:
        return 'italy'
    else:
        return None

long_df = toclean.melt()
long_df['country'] = long_df['variable'].map(country)
long_df
     variable value  country
0        type   car     None
1      wheels     4     None
2       seats     5     None
3         BMW    10  germany
4        Audi    20  germany
5    Mercedes    15  germany
6  Volkswagen    50  germany
7        Fiat    20    italy
8     Ferrari    13    italy
9        SEAT    24     None

long_df.groupby('country')['value'].sum()
country
germany    95
italy      33
Name: value, dtype: int64