Pandas: make pivot table with percentage

pandas, percentage, pivot-table, python, unique

Solution

IIUC you can use parameter `margins` for sum values in `pivot_table` and then divide all values last row `All` by `div`:

group = pd.pivot_table(df, 
                       index='used_at', 
                       columns='domain', 
                       values='ID', 
                       aggfunc=len, 
                       margins=True)
print (group)
domain   avito.ru  mazdaspb.ru  vw-stat.ru   All
used_at                                         
2015-01       3.0          3.0         5.0  11.0
All           3.0          3.0         5.0  11.0

print (group.iloc[:-1])
domain   avito.ru  mazdaspb.ru  vw-stat.ru   All
used_at                                         
2015-01       3.0          3.0         5.0  11.0

print (group.iloc[-1])
domain
avito.ru        3.0
mazdaspb.ru     3.0
vw-stat.ru      5.0
All            11.0
Name: All, dtype: float64

print (group.iloc[:-1].div(group.iloc[-1], axis=1) * 100)
domain   avito.ru  mazdaspb.ru  vw-stat.ru    All
used_at                                          
2015-01     100.0        100.0       100.0  100.0

Solution with divide by individual count with `div` and `mul`:

group = pd.pivot_table(df, 
                       index='used_at',
                       columns='domain', 
                       values='ID', 
                       aggfunc=len)
          .div(len(df.index))
          .mul(100)
print (group)

domain    avito.ru  mazdaspb.ru  vw-stat.ru
used_at                                    
2015-01  27.272727    27.272727   45.454545

Problem

I have dataframe ``` ID,url,used_at,active_seconds,domain 61a77f9e5fd52a50c10cd2d4d886ec68,mazdaspb.ru,2015-01,6,mazdaspb.ru 61a77f9e5fd52a50c10cd2d4d886ec68,mazdaspb.ru/cars/mazda-cx-5/crossover/overview,2015-01,12,mazdaspb.ru 61a77f9e5fd52a50c10cd2d4d886ec68,mazdaspb.ru/cars/mazda-cx-5/crossover/overview,2015-01,19,mazdaspb.ru 61a77f9e5fd52a50c10cd2d4d886ec68,vw-stat.ru,2015-01,40,vw-stat.ru 61a77f9e5fd52a50c10cd2d4d886ec68,vw-stat.ru/models/new_tiguan,2015-01,12,vw-stat.ru 61a77f9e5fd52a50c10cd2d4d886ec68,vw-stat.ru/models/new_tiguan/comps,2015-01,48,vw-stat.ru 61a77f9e5fd52a50c10cd2d4d886ec68,vw-stat.ru/models/new_tiguan/comps/new_tiguan_track_field,2015-01,4,vw-stat.ru 61a77f9e5fd52a50c10cd2d4d886ec68,vw-stat.ru/models/new_tiguan/comps/new_tiguan_track_field?engine_type=DIESEL&DIESEL=engines_4e53a3c8e986d,2015-01,78,vw-stat.ru 41c2fd7a372729dfe336e44730169f28,avito.ru/saratov/avtomobili_s_probegom/volkswagen/golf?f=188_886b887,2015-01,8,avito.ru 41c2fd7a372729dfe336e44730169f28,avito.ru/saratov/avtomobili_s_probegom/volkswagen/golf?f=188_886b887,2015-01,1,avito.ru 41c2fd7a372729dfe336e44730169f28,avito.ru/saratov/avtomobili_s_probegom/volkswagen/golf?f=188_886b887,2015-01,2,avito.ru ``` I need to get to make pivot table, and there are should be values of percentage of all unique ID. I can get ``` group = pd.pivot_table(df, index='used_at', columns='domain', values='ID', aggfunc=(lambda x: x.count())) ``` but it return quantity of unique ID to every domain to every month. How can I convert that to percentage?

Original source