CODEWITHSUNDEEP
pythonpandasnumpy
Lucas Dresl
Lucas Dresl

Reputation: 1170

Groupby with condition

I am trying to create a new variable which performs the SALES_AMOUNT difference between years-month on the following dataframe. I think my code should be think with this groupby but i dont know how to add the condition [df2 df.Control - df.Control.shift(1) == 12] after the groupby so as to perform a correct difference between years

df['LY'] = df.groupby(['month']).SALES_AMOUNT.shift(1)

Dataframe:

    SALES_AMOUNT Store  Control  year  month
0       16793.14     A        3  2013      3
1       42901.61     A        5  2013      5
2       63059.72     A        6  2013      6
3      168471.43     A       10  2013     10
4       58570.72     A       11  2013     11
5       67526.71     A       12  2013     12
6       50649.07     A       14  2014      2
7       48819.97     A       18  2014      6
8       97100.77     A       19  2014      7
9       67778.40     A       21  2014      9
10      90327.52     A       22  2014     10
11      75703.12     A       23  2014     11
12      26098.50     A       24  2014     12
13      81429.36     A       25  2015      1
14      19539.85     A       26  2015      2
15      71727.66     A       27  2015      3
16      20117.79     A       28  2015      4
17      44252.19     A       29  2015      6
18      68578.82     A       30  2015      7
19      91483.39     A       31  2015      8
20      39220.87     A       32  2015     10
21      12224.11     A       33  2015     11

result should look like this:

    SALES_AMOUNT Store  Control  year  month  year_diff
0       16793.14     A        3  2013      3        Nan
1       42901.61     A        5  2013      5        Nan
2       63059.72     A        6  2013      6        Nan
3      168471.43     A       10  2013     10        Nan      
4       58570.72     A       11  2013     11        Nan
5       67526.71     A       12  2013     12        Nan
6       50649.07     A       14  2014      2        Nan
7       48819.97     A       18  2014      6  -14239.75    
8       97100.77     A       19  2014      7        Nan
9       67778.40     A       21  2014      9        Nan
10      90327.52     A       22  2014     10  -78143.91
11      75703.12     A       23  2014     11    17132.4
12      26098.50     A       24  2014     12  -41428.21
13      81429.36     A       25  2015      1        Nan
14      19539.85     A       26  2015      2  -31109.22   
15      71727.66     A       27  2015      3        Nan
16      20117.79     A       28  2015      4        Nan
17      44252.19     A       29  2015      6   -4567.78   
18      68578.82     A       30  2015      7  -28521.95
19      91483.39     A       31  2015      8        Nan
20      39220.87     A       32  2015     10  -51106.65 
21      12224.11     A       33  2015     11  -63479.01

Upvotes: 0

Views: 89

Answers (2)

Ido S
Ido S

Reputation: 1352

I think what you're looking for is the below:

df = df.sort_values(by=['month', 'year'])
df['SALES_AMOUNT_shifted'] = df.groupby(['month'])['SALES_AMOUNT'].shift(1).tolist()
df['LY'] = df['SALES_AMOUNT'] - df['SALES_AMOUNT_shifted']

Once you sort by month and year, the month groups will be organized in a consistent way and then the shift makes sense.

-- UPDATE --

After applying the solution above, you could set to None all instances where the year difference is greater than 1.

df['year_diff'] = df['year'] - df.groupby(['month'])['year'].shift()
df['year_diff'] = df['year_diff'].fillna(0)
df.loc[df['year_diff'] != 1, 'LY'] = None

Using this I'm getting the desired output that you added.

Upvotes: 1

Evan
Evan

Reputation: 2151

Does this work? I would also greatly appreciate a pandas-centric solution, as I spent some time on this and could not come up with one.

df = pd.read_clipboard().set_index('Control')
df['yoy_diff'] = np.nan
for i in df.index:
    for j in df.index:
        if j - i == 12:
            df['yoy_diff'].loc[j] = df.loc[j, 'SALES_AMOUNT'] - df.loc[i, 'SALES_AMOUNT']
df

Output:

         SALES_AMOUNT Store  year  month  yoy_diff
Control                                           
3            16793.14     A  2013      3       NaN
5            42901.61     A  2013      5       NaN
6            63059.72     A  2013      6       NaN
10          168471.43     A  2013     10       NaN
11           58570.72     A  2013     11       NaN
12           67526.71     A  2013     12       NaN
14           50649.07     A  2014      2       NaN
18           48819.97     A  2014      6 -14239.75
19           97100.77     A  2014      7       NaN
21           67778.40     A  2014      9       NaN
22           90327.52     A  2014     10 -78143.91
23           75703.12     A  2014     11  17132.40
24           26098.50     A  2014     12 -41428.21
25           81429.36     A  2015      1       NaN
26           19539.85     A  2015      2 -31109.22
27           71727.66     A  2015      3       NaN
28           20117.79     A  2015      4       NaN
29           44252.19     A  2015      6       NaN
30           68578.82     A  2015      7  19758.85
31           91483.39     A  2015      8  -5617.38
32           39220.87     A  2015     10       NaN
33           12224.11     A  2015     11 -55554.29

Upvotes: 0

Related Questions