CODEWITHSUNDEEP
pythonpandaspivot
Peter
Peter

Reputation: 155

pandas Aggregation using pivot

I have the data frame as the following snipping:

enter image description here

I want to convert it to be like the following:

enter image description here

I use the following code:

df2_SEX_AGE=df1.pivot(index='codprg', columns=['SEX','ETA'], values='counts')

but it raises the following error KeyError: 'Level SEX not found'

Upvotes: 1

Views: 140

Answers (2)

jezrael
jezrael

Reputation: 862611

For convert MultiIndex to Index use list comprehension with pivot_table for avoid error:

Index contains duplicate entries, cannot reshape 

df2_SEX_AGE=df1.pivot_table(index='codprg', 
                            columns=['SEX','ETA'], 
                            values='counts',  
                            aggfunc='sum')
df2_SEX_AGE.columns = [f'{a}{b}' for a, b in df2_SEX_AGE.columns]

Upvotes: 1

zipa
zipa

Reputation: 27869

This should sort you out:

(df1.assign(p=df['SEX'].astype(str) + df['ETA'].astype(str))
    .pivot(index='codprg', columns='p', values='counts'))

Upvotes: 1

Related Questions