CODEWITHSUNDEEP
pythonpivotpandas-groupby
Spleen
Spleen

Reputation: 21

Pandas: adding a column to pivot and filtering

I'm starting with input data

mid      outid    Selected  Target_vol
201711  16942098  After      12
201712  16942102  After      14
20176   1389276   Before     16
20177   1389277   Before     0

etc, around 37k rows. I need a pivot which takes only rows with 'Selected'='Before', where Column labels = 'mid', row labels = 'outid', sum of values - sum of 'Target_vol'. I use a code in Python:

df.groupby(['outid', 'mid'])
[['Target_vol']].sum().reset_index().pivot(index='outid', columns='mid', 
values = 'Target_vol').reset_index()

The question is how to add filter, for example 'Selected'='Before' (or 'After', or without blanks). Then I need to insert a column "Target volume sales' where I can sum the sales ('Target_vol') by each 'mid', or if there a zeros, just right 'Non'.

Upvotes: 1

Views: 63

Answers (1)

Spleen
Spleen

Reputation: 21

Thanks, the suggested answer was

m = df[df.selected=='After']
new_df = pd.pivot_table(m, 'Target_vol', 'outid',  'mid',  aggfunc='sum')
new_df.assign(TargetVolumeSales = new_df.sum(axis=1))

Upvotes: 1

Related Questions