Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.5k views
in Technique[技术] by (71.8m points)

pandas - Python column hierarchy creation for groupby

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...