Pandas: create a pivot table in two different sizes?

Question

I am new to pandas. I have a date exam conducted by the sponsor and company:

import pandas pd

df = pd.DataFrame({
  'sponsor': ['A71991', 'A71991', 'A71991', 'A81001', 'A81001'],
  'sponsor_class': ['Industry', 'Industry', 'Industry', 'NIH', 'NIH'],
  'year': [2012, 2013, 2013, 2012, 2013],
  'passed': [True, False, True, True, True],
})

Now I want to display a CSV file with a line for each sponsor and his class, as well as columns for the passage and total rates for years:

sponsor,sponsor_class,2012_total,2012_passed,2013_total,2013_passed
A71991,Industry,1,1,2,1
A81001,NIH,1,1,1,1

How do I get out dfof this restructured frame? It seems to me that I need to group by sponsorand sponsor_class, and then rotate the total counter, and the counter, for which it passedis equal Trueby year, and then smooth these columns. (I know I'm ending up pd.write_csv(mydf).)

I tried to start with this:

df_g = df.groupby(['sponsor', 'sponsor_class', 'year', 'passed'])

But it gives me an empty data frame.

It seems to me that I need a pivot table somewhere to display the year and convey the status ... but I do not know where to start.

UPDATE . Somehow:

df_g = df_completed.pivot_table(index=['lead_sponsor', 'lead_sponsor_class'], 
                                columns='year', 
                                aggfunc=len, fill_value=0)
df_g[['passed']]

(1), , passed (2) CSV.

Answer 1

# set index to prep for unstack
df1 = df.set_index(['sponsor', 'sponsor_class', 'year']).astype(int)

# groupby all the stuff in the index
gb = df1.groupby(level=[0, 1, 2]).passed

# use agg to get sum and count    
# swaplevel and sort_index to get stuff sorted out
df2 = gb.agg({'passed': 'sum', 'total': 'count'}) \
          .unstack().swaplevel(0, 1, 1).sort_index(1)

# collapse multiindex into index
df2.columns = df2.columns.to_series().apply(lambda x: '{}_{}'.format(*x))

print df2.reset_index().to_csv(index=None)


sponsor,sponsor_class,2012_passed,2012_total,2013_passed,2013_total
A71991,Industry,1,1,1,2
A81001,NIH,1,1,1,1

Answer 2

, :

import numpy as np, pandas as pd
df['total'] = df['passed'].astype(int)
ldf = pd.pivot_table(df,index=['sponsor','sponsor_class'],columns='year',
                     values=['total'],aggfunc=len) # total counts
rdf = pd.pivot_table(df,index=['sponsor','sponsor_class'],columns='year',
                     values=['total'],aggfunc=np.sum) # number passed 
cdf = pd.concat([ldf,rdf],axis=1) # combine horizontally
cdf.columns = cdf.columns.get_level_values(0) # flatten index
cdf.reset_index(inplace=True)
columns = ['sponsor','sponsor_class']
yrs = sorted(df['year'].unique())
columns.extend(['{}_total'.format(yr) for yr in yrs])
columns.extend(['{}_passed'.format(yr) for yr in yrs])
cdf.columns = columns

:

>>> cdf
  sponsor sponsor_class  2012_total  2013_total  2012_passed  2013_passed
0  A71991      Industry           1           2            1            1
1  A81001           NIH           1           1            1            1

:

cdf.to_csv('/path/to/file.csv',index=False)