CODEWITHSUNDEEP
pythonpandassumpandas-groupby
Naomi Fridman
Naomi Fridman

Reputation: 2144

sum pandas column by condition with groupby

I have large data frame, I need to sum 'view' column for the for time period from 'view_day' column. The data frame looks like:

size = 400
dtype = [('view_day', 'int32'), ('account', 'int32'), ('view', 'int32')]
values = np.ones(size, dtype=dtype)

dfo = pd.DataFrame(values)

dfo['view_day'] = np.random.randint(7605, 7605 + 180, dfo.shape[0])
dfo['account'] = np.random.randint(1548051, 1548051 + 10, dfo.shape[0])
dfo['view'] = np.random.randint(600, 1800, dfo.shape[0])
    view_day account        view
0   7651     1548055        1338
1   7698     1548054        1147

I need to create new data frame, with account as index, and sum the total view in the last 30 days. New data frame looks like:

accounts= dfo.account.unique()

size = len(accounts)
dtype = [('view_last_30', 'int32')]
values = np.zeros(size, dtype=dtype)
index = accounts
dfc1 = pd.DataFrame(values, index=index)
           view_last_30
1548058    sum of view of this account for the last 30 days
1548057             "

I manage to group and sum the data with groupby in the following way:

last_day= dfo['view_day'].max()
dfo['last_30'] = dfo['view_day'] > last_day- 30
gl = dfo.groupby(['account', 'last_30'])['view']
h = gl.sum()

I get:

account  last_30
1548051  False      30439
         True        6713
1548052  False      27491
         True        8477

How do I copy the sum's with last_30== True in to the new created data frame ? So I will get:

          view_last_30
1548051    6713
1548052    8477

Upvotes: 1

Views: 732

Answers (2)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210822

you can use IndexSlice accessor:

In [57]: s
Out[57]:
account  last_30
1548051  False      30439
         True        6713
1548052  False      27491
         True        8477
Name: val, dtype: int64

In [58]: s.loc[pd.IndexSlice[:,True]]
Out[58]:
account
1548051    6713
1548052    8477
Name: val, dtype: int64

Upvotes: 2

BENY
BENY

Reputation: 323226

get_level_values then using boolean select 

df.loc[df.index.get_level_values('last_30').values].\
       reset_index('last_30',drop=True)
Out[590]: 
         value
account       
1548051   6713
1548052   8477

Upvotes: 2

Related Questions