pandas groupby count and proportion of group total

Question

I'm trying to do the following with pandas. Counting item by state then expressing that number as a percentage of the subtotal. My dataframe has the raw data. I can get the counts but how to append another column for the percentages?

state_grp = df.groupby(by=['date', 'state','ad_type'])
state_grp.ad_type.agg(['count'])

I've wrote some sql which will do the same thing but how to do it in pandas?

with cte1 as
(
    select distinct date, state, ad_type, count(ad_type) over (partition by date, state, ad_type) as [# of Ads]
    from propertylistings
),

cte2 as
(
    select *, sum([# of Ads]) over (partition by state) as subtotal
    from dhg
)

select date, state, ad_type, [# of Ads], round(cast([# of Ads] as float)/cast(subtotal as float) * 100, 1) as [%]
from cte2
order by date, state, ad_type

Kindly post your samples in text not in images, it is NOT recommended on SO. Please edit it and let us know then. — RavinderSingh13, Dec 11 '18 at 02:42
its hard to post the output in text because I will lose all the formatting. How do you propose I do this? — user3381431, Dec 11 '18 at 04:02

score 1 · Answer 1 · answered Dec 11 '18 at 02:54

1

You can do with transform + sum

state_grp = df.groupby(by=['date', 'state','ad_type'])
state_grp=state_grp.ad_type.agg(['count'])
state_grp['%']=state_grp['count']/state_grp.groupby(level=[0,1])['count'].transform('sum')

answered Dec 11 '18 at 02:54

BENY

317,841
20
164
234

thanks do you have a good link as to where i can read up on how that syntax work? – user3381431 Dec 11 '18 at 04:05
@user3381431 https://stackoverflow.com/questions/27517425/apply-vs-transform-on-a-group-object – BENY Dec 11 '18 at 04:08

pandas groupby count and proportion of group total

1 Answers1