0

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.

Improve this question
2
  • Look at pandas.Period for more information. Also there is discussion related to it at YearWithQuarter. I hope this will help you. Commented Sep 19, 2018 at 10:09
  • Seems iterating through the rows is the only solution here right? Sorry I cannot get my head round due to my use of pandas has been pretty simple. Like taking a whole column and multiply by another column to create a new column. Commented Sep 19, 2018 at 10:17

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for this. Unfortunately there will missing quarters and years. Looks like i do need to iterate down the rows with 2 loops...
Please update your question with the real input dataframe with all the conditions and the expected output dataframe. Have a look at MCVE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.