I have a dataframe
A B
Date Price
2019-8-1 1000 1.1 0.0
1500 2.3 2.2
2200 4.5 0.5
3100 4.4 0.9
2019-8-2 1100 2.2 1.2
1400 2.5 1.3
2200 0.9 1.6
3500 1.1 0.1
I need to get a third column 'C', that will be a diff() from level=1 (Price) in multiindex:
A B C
Date Price
2019-8-1 1000 1.1 0.0 NaN
1500 2.3 2.2 500
2200 4.5 0.5 700
3100 4.4 0.9 900
2019-8-2 1100 2.2 1.2 NaN
1400 2.5 1.3 300
2200 0.9 1.6 800
3500 1.1 0.1 1300
I tried to use groupby(level=0), but I can't figure out how then to aply diff() to the multiindex level.
Thanks.
Create new MultiIndex Series with Index.to_series and seelct second values of tuples by str[1] and then use DataFrameGroupBy.diff:
df['C'] = df.index.to_series().str[1].groupby(level=0).diff()
Or crete new column by DataFrame.assign and Index.get_level_values:
df['C'] = df.assign(P=df.index.get_level_values(1)).groupby(level=0)['P'].diff()
print (df)
A B C
Date Price
2019-8-1 1000 1.1 0.0 NaN
1500 2.3 2.2 500.0
2200 4.5 0.5 700.0
3100 4.4 0.9 900.0
2019-8-2 1100 2.2 1.2 NaN
1400 2.5 1.3 300.0
2200 0.9 1.6 800.0
3500 1.1 0.1 1300.0
Details:
print (df.index.to_series())
Date Price
2019-8-1 1000 (2019-8-1, 1000)
1500 (2019-8-1, 1500)
2200 (2019-8-1, 2200)
3100 (2019-8-1, 3100)
2019-8-2 1100 (2019-8-2, 1100)
1400 (2019-8-2, 1400)
2200 (2019-8-2, 2200)
3500 (2019-8-2, 3500)
print (df.index.to_series().str[1])
Date Price
2019-8-1 1000 1000
1500 1500
2200 2200
3100 3100
2019-8-2 1100 1100
1400 1400
2200 2200
3500 3500
dtype: int64
print (df.assign(P=df.index.get_level_values(1)))
A B P
Date Price
2019-8-1 1000 1.1 0.0 1000
1500 2.3 2.2 1500
2200 4.5 0.5 2200
3100 4.4 0.9 3100
2019-8-2 1100 2.2 1.2 1100
1400 2.5 1.3 1400
2200 0.9 1.6 2200
3500 1.1 0.1 3500
EDIT:
Another solution from comments:
df.index.to_frame().groupby(level=0)['Price'].diff()