CODEWITHSUNDEEP
pandasdataframepandas-groupbypivot-table
Saif
Saif

Reputation: 95

groupby results to custom dataframe

I have teh below data sample. I would like to groupby using d=df.groupby(['id2','type','id1']).sum()['clicks'] or d=df.groupby(['id2','type','id1']).agg({'clicks':'sum'}. id2 is a user id. There are many types, and each has different ids. I would like to use the result of a groupby and make a pivot table. the new table index is id2, the columns will be values I of type columns. the values will be the sum of clicks for each type .

df = pd.DataFrame({"id1": [493,303,814,810,303,930,821,493,303,814],
                   "module": ["DDD", "DDD", "AAA", "BBB", "DDD","CCC", "CCC", "DDD", "AAA","DDD"],
                   "present": ["13J", "14J", "13J", "14B", "13B","13J", "14J", "13J", "14B","13B"],
                   "id2": [516, 516, 516, 388, 388,388, 388, 388,695,386],
                   "type": ["t1", "t2", "t3", "t3","t2", "t4", "t3", "t1","t2","t3"],
                   "clicks": [3,6,2,1,3,1,2,2,5,1]})

I tried pivoted_t=d.pivot(index='id2',columns='type'), but I get this error None of ['id2', 'type'] are in the columns

Upvotes: 1

Views: 41

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150755

You can either pivot_table from your original data:

df.pivot_table(index=['id2','id1'], columns='type', values='clicks', aggfunc='sum')

Output:

type      t1   t2   t3   t4
id2 id1                    
386 814  NaN  NaN  1.0  NaN
388 303  NaN  3.0  NaN  NaN
    493  2.0  NaN  NaN  NaN
    810  NaN  NaN  1.0  NaN
    821  NaN  NaN  2.0  NaN
    930  NaN  NaN  NaN  1.0
516 303  NaN  6.0  NaN  NaN
    493  3.0  NaN  NaN  NaN
    814  NaN  NaN  2.0  NaN
695 303  NaN  5.0  NaN  NaN

Or from your groupby result, do a unstack:

d['clicks'].unstack('type')

which also gives you the same output.

Upvotes: 1

Related Questions