I have a table with weekly data like below:
| Date | A | B | C | D |
|---|---|---|---|---|
| 1/1/2022 | 4 | 5 | 5 | 2 |
| 1/7/2022 | 3 | 5 | 9 | 4 |
| 1/14/2022 | 4 | 8 | 5 | 6 |
| 1/21/2022 | 4 | 6 | 1 | 4 |
I want to create an YTD change table like the below where YTD change is calculated as ('last value of the year' - 'first value of the year') / 'first value of the year' (i.e., basic % change). I have just started out so am not sure how to approach this in the most efficient manner.
Desired output format:
| Date | A | B | C | D |
|---|---|---|---|---|
| 2022 | x | x | x | x |
| 2021 | x | x | x | x |
| 2020 | x | x | x | x |
| 2019 | x | x | x | x |
CodePudding user response：
You could achieve this by using groupby with apply (the pct_change method is closely related by can only be applied on consecutive rows in a group).
df['Date'] = pd.to_datetime(df['Date'])
df = df.sort_values('Date').set_index('Date')
df.groupby(df.index.year).apply(lambda x: x.iloc[-1].subtract(x.iloc[0]).div(x.iloc[0]))
Result on the sample data:
A B C D
Date
2022 0.0 0.2 -0.8 1.0