CODEWITHSUNDEEP
python-3.xpandaspivotbar-chart
Alaa J E
Alaa J E

Reputation: 41

pivot all the columns of a dataframe

I have this dataframe:

df= pd.DataFrame({'Jan' : ['US', 'GB', 'NL', 'CH', 'GB', 'US'],
                  'Feb': ['US', 'AU', 'RU', 'NO', 'AU', 0],
                  'Mar' : ['PL', 'AU', 'FI', 'US', 'CH', 'CH']})

I would like to create stacked barchart to show the count of countries per month. So I need first to transform this dataframe to this form : 

      Jan  Feb  Mar
  US   2    1    1
  GB   2    0    0
  NL   1    0    0
  CH   1    0    2
  AU   0    2    1
  RU   0    1    0 
  NO   0    1    0
  PL   0    0    1
  FI   0    0    1
  0    0    1    0

My dataframe is large but I want to display the most 10 common countries for each month on the stacked barplot. I noticed that pandas pivot isn't doing the Job.

Upvotes: 1

Views: 44

Answers (1)

Zero
Zero

Reputation: 76917

You could

In [46]: s = df.stack().reset_index()

In [47]: pd.crosstab(s[0], s['level_1']).rename_axis(None, 1).rename_axis(None, 0)
Out[47]:
    Feb  Jan  Mar
0     1    0    0
AU    2    0    1
CH    0    1    2
FI    0    0    1
GB    0    2    0
NL    0    1    0
NO    1    0    0
PL    0    0    1
RU    1    0    0
US    1    2    1

Upvotes: 2

Related Questions