0

I have a file, df, that I wish to take the delta of every 7 day period

df:

Date          Value
10/15/2020    75
10/14/2020    70
10/13/2020    65
10/12/2020    60
10/11/2020    55
10/10/2020    50
10/9/2020     45
10/8/2020     40
10/7/2020     35
10/6/2020     30
10/5/2020     25
10/4/2020     20
10/3/2020     15
10/2/2020     10
10/1/2020     5

Desired Output:

Date          Value

10/9/2020     30
10/2/2020     30

This is what I am doing, thanks to the help of someone on this platform:

df.Date = pd.to_datetime(df.Date)   

s = df.set_index('Date')['Value']
df['New'] = s.shift(freq = '-6 D').reindex(s.index).values  
df['Delta'] = df['New'] - df['Value'] 

df[['Date','Delta']].dropna()

However, this gives me a running delta, I wish to have the delta displayed for every 7 day period, as shown in the Desired Output. Any suggestion is appreciated

Lynn
  • 4,292
  • 5
  • 21
  • 44

1 Answers1

1

i think the way you have done is the perfect way. I think modifying it a bit will give you the desired result. Try this:

 df.Date = pd.to_datetime(df.Date)
 s = df.set_index('Date')['Value']
 df['New'] = s.shift(freq = '-6 D').reindex(s.index).values
 df['Delta'] = df['New'] - df['Value'] 
 df_new=df[['Date','Delta']].dropna()
 df_new.iloc[::7, :]
Sahil Jain
  • 48
  • 7
  • Hi @sahil , could you explain what this is doing? [::7, :] ? Does it give the desired output of only the Date and Value columns? thanks - I have tried this, but nothing seems to have changed from the original undesired output – Lynn Oct 21 '20 at 07:56
  • 1
    it is extended slices option in python and can even check palindrome string. Read this article here https://stackoverflow.com/questions/3453085/what-is-double-colon-in-python-when-subscripting-sequences – Sahil Jain Oct 21 '20 at 08:00