Pandas: 1 dataframe comparing rows to create new column

Question

I have a problem which I cannot seem to get my head round.

df1 is as follows:

Group     item     Quarter    price    quantity

1         A        2017Q3     0.10     1000 
1         A        2017Q4     0.11     1000 
1         A        2018Q1     0.11     1000
1         A        2018Q2     0.12     1000 
1         A        2018Q3     0.11     1000

Result desired is a new dataframe call it df2 with an additional column.

Group     item     Quarter    price    quantity    savings/lost

1         A        2017Q3     0.10     1000         0.00   
1         A        2017Q4     0.11     1000         0.00
1         A        2018Q1     0.11     1000         0.00
1         A        2018Q2     0.12     1000         0.00
1         A        2018Q3     0.11     1000         10.00
1         A        2018Q4     0.13     1000         -20.00

Essentially, I want to go down each row, look at the quarter and find last year's similar quarter and do a calculation (price this quarter - price last quarter * quantity). If there are no previous quarter data, just have in the last column.

And to complete the picture, there are more groups and items in there, and even more quarters like 2016Q1, 2017Q1, 2018Q1 although i only need compare the year before. Quarters are in string format.

Answer 1

Use pandas.DataFrame.shift

The code below assumes that your column Quarter is sorted and there is no missing quarters. You can try with the below code:

# Input dataframe
  Group item Quarter  price  quantity
0     1    A  2017Q3   0.10      1000
1     1    A  2017Q4   0.11      1000
2     1    A  2018Q1   0.11      1000
3     1    A  2018Q2   0.12      1000
4     1    A  2018Q3   0.11      1000
5     1    A  2018Q4   0.13      1000

# Code to generate your new column 'savings/lost'
df['savings/lost'] =  df['price'] * df['quantity'] - df['price'].shift(4) * df['quantity'].shift(4)

# Output dataframe
  Group item Quarter  price  quantity  savings/lost
0     1    A  2017Q3   0.10      1000           NaN
1     1    A  2017Q4   0.11      1000           NaN
2     1    A  2018Q1   0.11      1000           NaN
3     1    A  2018Q2   0.12      1000           NaN
4     1    A  2018Q3   0.11      1000          10.0
5     1    A  2018Q4   0.13      1000          20.0

Update

I have updated my code to handle two things, first sort the Quarter and second handle the missing Quarter scenario. For grouping based on columns you can refer pandas.DataFrame.groupby and many pd.groupby related questions already answered in this site.

#Input dataframe
  Group item Quarter  price  quantity
0     1    A  2014Q3   0.10       100
1     1    A  2017Q2   0.16       800
2     1    A  2017Q3   0.17       700
3     1    A  2015Q4   0.13       400
4     1    A  2016Q1   0.14       500
5     1    A  2014Q4   0.11       200
6     1    A  2015Q2   0.12       300
7     1    A  2016Q4   0.15       600
8     1    A  2018Q1   0.18       600
9     1    A  2018Q2   0.19       500

#Code to do the operations
df.index = pd.PeriodIndex(df.Quarter, freq='Q')
df.sort_index(inplace=True)
df2 = df.reset_index(drop=True)
df2['Profit'] = (df.price * df.quantity) - (df.reindex(df.index - 4).price * df.reindex(df.index - 4).quantity).values
df2['Profit'] = np.where(np.in1d(df.index - 4, df.index.values),
                        df2.Profit, ((df.price * df.quantity) - (df.price.shift(1) * df.quantity.shift(1))))
df2.Profit.fillna(0, inplace=True)

#Output dataframe
  Group item Quarter  price  quantity  Profit
0     1    A  2014Q3   0.10       100     0.0
1     1    A  2014Q4   0.11       200    12.0
2     1    A  2015Q2   0.12       300    14.0
3     1    A  2015Q4   0.13       400     0.0
4     1    A  2016Q1   0.14       500    18.0
5     1    A  2016Q4   0.15       600     0.0
6     1    A  2017Q2   0.16       800    38.0
7     1    A  2017Q3   0.17       700    -9.0
8     1    A  2018Q1   0.18       600   -11.0
9     1    A  2018Q2   0.19       500     0.0