Transform Pandas DataFrame of categorical variables to MultiIndex with count and proportion

493
December 29, 2017, at 03:35 AM

I have a Pandas DataFrame containing several categorical variables. For example:

import pandas as pd
d = {'grade':['A','B','C','A','B'], 
    'year':['2013','2013','2013','2012','2012']}
df = pd.DataFrame(d)

I would like to transform this to a MultiIndex DataFrame with the following properties:

  • First level index is the variable name (e.g. 'grade')
  • Second level index is the levels within the variable (e.g. 'A', 'B', 'C')
  • One column contains 'n', a count of the number of times the level appears
  • A second column contains 'proportion', the proportion represented by this level.

For example:

Could anyone suggest a method for creating this MultiIndex DataFrame?

Answer 1

You can try this ..

df1=df.apply(pd.value_counts).stack().swaplevel(0,1).to_frame('n')
df1['pct']=df1['n'].div(df1.n.sum(level=0),level=0)
df1
Out[89]: 
              n  pct
year  2012  2.0  0.4
      2013  3.0  0.6
grade A     2.0  0.4
      B     2.0  0.4
      C     1.0  0.2
Answer 2

Another way you can do this to use melt and groupby:

df_out = df.melt().groupby(['variable','value']).size().to_frame(name='n')
df_out['proportion'] = df_out['n'].div(df_out.n.sum(level=0),level=0)
print(df_out)

Output:

                n  proportion
variable value               
grade    A      2         0.4
         B      2         0.4
         C      1         0.2
year     2012   2         0.4
         2013   3         0.6

And, if you really want to get crazy and do it in a one-liner:

(df.melt().groupby(['variable','value']).size().to_frame(name='n')
  .pipe(lambda x: x.assign(proportion = x[['n']]/x.groupby(level=0).transform('sum'))))

Upgraded solution using @Wen pct calculation:

(df.melt().groupby(['variable','value']).size().to_frame(name='n')
  .pipe(lambda x: x.assign(proportion = x['n'].div(x.n.sum(level=0),level=0))))
Answer 3

Stey by step method:

df1 = df.groupby("grade").count()
df2 = df.groupby("year").count() 
df1.columns = ['n']
df2.columns = ['n']
df1['proportion'] = df1.divide(df1.sum())
df2['proportion'] = df2.divide(df2.sum())
df_new = pd.concat([df1, df2], keys=['grade', 'year'], names=['variable'])
  • while using concat, one can assign keys that would be the outermost-layer index. Also assign name to this new index with names=.

Answer 4

The DataFrame can be created by stacking each variable in a loop, but this seems inefficient. e.g.:

d_end = []
for c in df.columns:
    temp_df = pd.DataFrame(df[c].value_counts().rename('n'))
    temp_df['proportion'] = temp_df['n'] / temp_df['n'].sum()
    temp_df['variable'] = c
    temp_df.set_index(['variable',temp_df.index],inplace=True)
    d_end.append(temp_df)
df_end = pd.concat(d_end,axis=0)

I'm hoping someone can suggest a better way, avoiding the loop.

Rent Charter Buses Company
READ ALSO
benefits of manually running multiple instances of a program

benefits of manually running multiple instances of a program

So i've googled multithreading for python3 and not quite found what i'm looking for

206
strange behavior of opencv 3.x 's function findChessBoardCorners()

strange behavior of opencv 3.x 's function findChessBoardCorners()

so I have two images that are exact the same except one has a black band on the top and the other does not, I then run the following python lines

249
Axes3D Plot Wireframe from DateTime

Axes3D Plot Wireframe from DateTime

I'm working with Axes3D, and I want to create a 3d plot of two floats and a timestamp from a pandas seriesThe plot function looks like this:

369
Make new pandas columns based on pipe-delimited column with possible repeats

Make new pandas columns based on pipe-delimited column with possible repeats

This question pertains to the fine solution to my previous question, Create Multiple New Columns Based on Pipe-Delimited Column in Pandas

240