Zipcodes with a cumulative column over time: add column that contains the added value

Question

I have a dataframe that consists of zipcodes, with a quarter and a cumulative number. This number contains all the events that happened in that zipcode up and including that quarter. I want to add a column, e.g., 'new', that notes the change in events in that quarter. My current dataframe looks like this:

Zipcode      Quarter       Cumulative_number
9999         2020 Q1             1
9999         2020 Q2             1
9999         2020 Q3             2
9999         2020 Q4             3
2000         2018 Q2             0
2000         2018 Q3             4
2000         2018 Q4             0

Ideally the dataframe would look like this:

Zipcode      Quarter       Cumulative_number     New
9999         2020 Q1             1                0    
9999         2020 Q2             1                0
9999         2020 Q3             2                1
9999         2020 Q4             3                1
2000         2018 Q2             0                0
2000         2018 Q3             4                4
2000         2018 Q4             4                0

The code used to create the cumulative number looks like this:

df = (df.reindex(pd.date_range(df.columns.min(), until, freq='QS', name='Quarter'), axis=1)
        .ffill(axis=1)
        .stack()
        .astype(int)
        .reset_index(name='Cumulative_number'))

how is the cumulative number = 0 for zip code 2000 and quarter 2018 Q4? — Shubham Periwal, Sep 13 '21 at 12:44
Thanks, this was a mistake in the mock-up data frame. I updated the post. — TvCasteren, Sep 13 '21 at 12:48

score 1 · Accepted Answer · answered Sep 13 '21 at 12:47

First you can sort according to zip code and quarter. Then get the diff between quarters after grouping by zip code. Finally, assign value 0 to the first value in every zip code.

df.sort_values(['Zipcode', 'Quarter'], inplace=True)
df['diffs'] = df.groupby('Zipcode')['Cumulative_number'].diff()
df.sort_index(inplace=True)
df.fillna(0, inplace=True)

NOTE that Im assuming 2018 Q4 should be 4 not 0 logically.

    Zipcode Quarter Cumulative_number   diffs
0   9999    2020 Q1 1   0.0
1   9999    2020 Q2 1   0.0
2   9999    2020 Q3 2   1.0
3   9999    2020 Q4 3   1.0
4   2000    2018 Q2 0   0.0
5   2000    2018 Q3 4   4.0
6   2000    2018 Q4 4   0.0

Answer inspired by here so if you want alternatives can check this link out.

Zipcodes with a cumulative column over time: add column that contains the added value

1 Answers1