Grouping and pivoting DataFrame with additional column for ratio of counts

Question

I have a dataframe that looks like this:

id       status      year 
1        yes         2014
3        no          2013
2        yes         2014
4        no          2014

The actual dataframe is very large with multiple ids and years. I am trying to make a new dataframe that has the percents of 'yes's and 'no's grouped by year.

I was thinking of grouping the dataframe by the year, which would then put the statuses per year in a list and then analyzing the counts of yes's and no's that way, but I was wondering whether there is a more pythonic way to do this?

I would like for the end dataframe to look like this:

year      yes_count     no_count     ratio_yes_to_toal    
2013       0             1             0%
2014       2             1             67%

Answer 1

I'd suggest grouping by year and status, counting, pivoting, and then creating an additional column of the ratio:

df2 = df.groupby(['year', 'status']).count().pivot_table(index="year", columns=["status"]).fillna(0)
df2.columns = df2.columns.get_level_values(1)
df2['ratio'] = df2['yes'] / (df2['yes'] + df2['no'])

Output

status   no  yes     ratio
year                      
2013    1.0  0.0  0.000000
2014    1.0  2.0  0.666667