I have a dataframe df
df:
| Date | Type | AVG1 | AVG2 | AVG3 | AVG4 | AVG5 |
|---|---|---|---|---|---|---|
| 2022-05 | ROL1 | 0.33 | 0.45 | 0.12 | 0.96 | 1.33 |
| 2022-05 | ROL2 | 1.43 | 0.11 | 0.75 | 1.99 | 3.01 |
| 2022-05 | ROL3 | 0.11 | 0.32 | 0.55 | 1.26 | 4.22 |
| 2022-04 | ROL1 | 1.66 | 0.71 | 0.87 | 5.88 | 1.11 |
| 2022-04 | ROL2 | 2.31 | 0.89 | 2.20 | 4.36 | 4.87 |
| 2022-04 | ROL3 | 5.40 | 1.22 | 4.45 | 0.01 | 0.31 |
And I need to create the columns AVG1_ROL1_MoM, AVG1_ROL2_MoM, AVG3_ROL1_MoM, AVG1_ROL2_MoM and so on. Where AVG1_ROL1_MoM is the difference in AVG1 where TYPE = ROL1 from one month to the other:
| Date | Type | AVG1 | AVG2 | AVG3 | AVG4 | AVG5 | AVG1_ROL1_MoM | AVG1_ROL2_MoM |
|---|---|---|---|---|---|---|---|---|
| 2022-05 | ROL1 | 0.33 | 0.45 | 0.12 | 0.96 | 1.33 | -1.33 | NaN |
| 2022-05 | ROL2 | 1.43 | 0.11 | 0.75 | 1.99 | 3.01 | NaN | -0.88 |
| 2022-05 | ROL3 | 0.11 | 0.32 | 0.55 | 1.26 | 4.22 | NaN | NaN |
| 2022-04 | ROL1 | 1.66 | 0.71 | 0.87 | 5.88 | 1.11 | NaN | NaN |
| 2022-04 | ROL2 | 2.31 | 0.89 | 2.20 | 4.36 | 4.87 | NaN | NaN |
| 2022-04 | ROL3 | 5.40 | 1.22 | 4.45 | 0.01 | 0.31 | NaN | NaN |
I tried to do that with mask() and shift(), but it didn't work:
df['AVG1_ROL1_MoM'] = df.mask(df['Type']=="ROL1", df['AVG1'] - df['AVG1'].shift(), inplace=True)
This returns that an axis must be defined, but when I define and axis it returns that: "Cannot do inplace boolean setting on mixed-types with a non np.nan value"
What would be the best approach for this?
CodePudding user response：
meltthe dataframe to get all the values in a single column- Create the new column names
groupbyto find the monthly differencespivotto get back the original structuremergewith the original dataframe
melted = df.melt(["Date","Type"])
melted["column"] = melted["variable"] "_" melted["Type"] "_MoM"
melted["diff"] = melted.groupby(["Type","variable"])["value"].diff(-1)
pivoted = melted.pivot(["Date","Type"],"column","diff").sort_index(ascending=[False,True]).reset_index()
output = df.merge(pivoted, on=["Date","Type"])
>>> output
Date Type AVG1 ... AVG5_ROL1_MoM AVG5_ROL2_MoM AVG5_ROL3_MoM
0 2022-05 ROL1 0.33 ... 0.22 NaN NaN
1 2022-05 ROL2 1.43 ... NaN -1.86 NaN
2 2022-05 ROL3 0.11 ... NaN NaN 3.91
3 2022-04 ROL1 1.66 ... NaN NaN NaN
4 2022-04 ROL2 2.31 ... NaN NaN NaN
5 2022-04 ROL3 5.40 ... NaN NaN NaN
[6 rows x 22 columns]
CodePudding user response：
IUUC, you can try group by Type column and then compare the subgroup AVG shifted value and rename the outcome columns:
out = (df.filter(like='AVG')
.groupby(df['Type'])
.apply(lambda g: (g-g.shift(-1)).rename(columns=lambda col: f'{col}_{g.name}_MOM'))
)
print(out)
AVG1_ROL1_MOM AVG2_ROL1_MOM AVG3_ROL1_MOM AVG4_ROL1_MOM AVG5_ROL1_MOM \
0 -1.33 -0.26 -0.75 -4.92 0.22
1 NaN NaN NaN NaN NaN
2 NaN NaN NaN NaN NaN
3 NaN NaN NaN NaN NaN
4 NaN NaN NaN NaN NaN
5 NaN NaN NaN NaN NaN
AVG1_ROL2_MOM AVG2_ROL2_MOM AVG3_ROL2_MOM AVG4_ROL2_MOM AVG5_ROL2_MOM \
0 NaN NaN NaN NaN NaN
1 -0.88 -0.78 -1.45 -2.37 -1.86
2 NaN NaN NaN NaN NaN
3 NaN NaN NaN NaN NaN
4 NaN NaN NaN NaN NaN
5 NaN NaN NaN NaN NaN
AVG1_ROL3_MOM AVG2_ROL3_MOM AVG3_ROL3_MOM AVG4_ROL3_MOM AVG5_ROL3_MOM
0 NaN NaN NaN NaN NaN
1 NaN NaN NaN NaN NaN
2 -5.29 -0.9 -3.9 1.25 3.91
3 NaN NaN NaN NaN NaN
4 NaN NaN NaN NaN NaN
5 NaN NaN NaN NaN NaN
out = pd.concat([df, out], axis=1)
print(out)
Date Type AVG1 AVG2 AVG3 AVG4 AVG5 AVG1_ROL1_MOM AVG2_ROL1_MOM \
0 2022-05 ROL1 0.33 0.45 0.12 0.96 1.33 -1.33 -0.26
1 2022-05 ROL2 1.43 0.11 0.75 1.99 3.01 NaN NaN
2 2022-05 ROL3 0.11 0.32 0.55 1.26 4.22 NaN NaN
3 2022-04 ROL1 1.66 0.71 0.87 5.88 1.11 NaN NaN
4 2022-04 ROL2 2.31 0.89 2.20 4.36 4.87 NaN NaN
5 2022-04 ROL3 5.40 1.22 4.45 0.01 0.31 NaN NaN
AVG3_ROL1_MOM AVG4_ROL1_MOM AVG5_ROL1_MOM AVG1_ROL2_MOM AVG2_ROL2_MOM \
0 -0.75 -4.92 0.22 NaN NaN
1 NaN NaN NaN -0.88 -0.78
2 NaN NaN NaN NaN NaN
3 NaN NaN NaN NaN NaN
4 NaN NaN NaN NaN NaN
5 NaN NaN NaN NaN NaN
AVG3_ROL2_MOM AVG4_ROL2_MOM AVG5_ROL2_MOM AVG1_ROL3_MOM AVG2_ROL3_MOM \
0 NaN NaN NaN NaN NaN
1 -1.45 -2.37 -1.86 NaN NaN
2 NaN NaN NaN -5.29 -0.9
3 NaN NaN NaN NaN NaN
4 NaN NaN NaN NaN NaN
5 NaN NaN NaN NaN NaN
AVG3_ROL3_MOM AVG4_ROL3_MOM AVG5_ROL3_MOM
0 NaN NaN NaN
1 NaN NaN NaN
2 -3.9 1.25 3.91
3 NaN NaN NaN
4 NaN NaN NaN
5 NaN NaN NaN