CODEWITHSUNDEEP
pythonpandas
kms
kms

Reputation: 2024

Loop over pandas groupby and assign the operations back to parent DataFrame

I have a Month, Year, Market and Value columns in pandas dataframe. I'd perform / calculate percent change operation for each group and assign a new column back to parent DataFrame.

Here's a mock DataFrame:

df = pd.DataFrame({'Market': ['LA','SF','NY','LA','SF','NY'],
                   'Month': [4, 4, 4, 5, 5, 5],
                   'Year': [2017,2017,2017,2017,2017,2017],
                   'Value': [1000,1200,1400,1200,1400,1600] })

I am interested in Market, Month, Year level and calculate percent change from previous month for each group.

for name, group in df.groupby(['Market','Month','Year']):

    # Create and sort on Date column ascending
    group['Date'] = pd.to_datetime(group[['Year', 'Month']].assign(DAY=1))
    group.sort_values(by=['Date'], ascending=True)

    # Calc. percent change
    group['value_pct'] = group['Value'].pct_change(periods=1)

How do I assign this back to the parent DataFrame? or would you recommend a different way to do this?

Upvotes: 1

Views: 286

Answers (1)

mozway
mozway

Reputation: 261860

IIUC, you can try:

df['value_pct'] = (df.sort_values(by=['Year', 'Month'])
                     .groupby('Market')
                     ['Value']
                     .pct_change()
                  )

Output:

  Market  Month  Year  Value  value_pct
0     LA      4  2017   1000        NaN
1     SF      4  2017   1200        NaN
2     NY      4  2017   1400        NaN
3     LA      5  2017   1200   0.200000
4     SF      5  2017   1400   0.166667
5     NY      5  2017   1600   0.142857

Upvotes: 1

Related Questions