Python difference one month if no value then take difference of last available?

user_17684445 0
Viewed 46

I have a dataframe with date as index and price in col 1. I want to add a second column that will take the 1 month difference in price. My problem is that some month have no value. If there's no value in the previous month I would like that it take the difference with the last price available.. But if at the month there's no price it keep no Value (nan). Is that possible ? Here's an exemple of my initial Dataframe

Date              Price       
2020-12-31         20              
2021-01-31         25              
2021-02-28         22              
2021-03-31         nan               
2021-04-30         32              
2021-05-31         33              
2021-06-30         nan               
2021-07-31         40

Here's the result I would like. You can see that on march the price difference is nan because there's no price on that date, BUT next month on april the price difference is equal to april minus february because theres no price on march (32-22). Same exemple with Jully :

Date              Price      Price_difference
2020-12-31         20           nan
2021-01-31         25            5
2021-02-28         22           -3
2021-03-31         nan          nan 
2021-04-30         32            10 
2021-05-31         34            2 
2021-06-30         nan          nan 
2021-07-31         40            6

Here's my code now but it don't work... :

df_diff = df.diff(periods=1)
pandas dataframe python
1 Answers

A trick you can do is to use ffill() on your price column before you use diff(). This ensures the previous available value gets picked up and situations like: month on april the price difference is equal to april minus february because theres no price on march (32-22) will be dealt with.

Then the last part would be to place nan in your Price_difference to rows where Price was also nan.

df['Price_difference'] = df.Price.ffill().diff()
df['Price_difference'] = np.where(df['Price'].isna(),np.nan,df.Price_difference)

Will print back:

         Date  Price  Price_difference
0  31/12/2020   20.0               NaN
1  31/01/2021   25.0               5.0
2  28/02/2021   22.0              -3.0
3  31/03/2021    NaN               NaN
4  30/04/2021   32.0              10.0
5  31/05/2021   34.0               2.0
6  30/06/2021    NaN               NaN
7  31/07/2021   40.0               6.0
user_91673820
Related