I have a pandas DataFrame with 3 level multi-index columns. The bottom level is always two columns and I want to calculate the difference of these two columns, within each of the groups defined by the first two levels.
import numpy as np
import pandas as pd
multi_idx = pd.MultiIndex.from_product([['A', 'B', 'C'], ['a', 'b'], ['L', 'S']],
names=['level_1', 'level_2', 'level_3'])
test_df = pd.DataFrame(np.random.randn(10, len(multi_idx)), columns=multi_idx)
So, in other words, I want to calculate 'L' - 'S' within each group.
Here is my attempt using pandas groupby:
net = test_df.groupby(level=['level_1', 'level_2'], axis=1, group_keys=False)\
.apply(lambda x: x.xs('L', axis=1, level='level_3', drop_level=True) -
x.xs('S', axis=1, level='level_3', drop_level=True))
but it is returning all NaN:
net
level_1 A B C
level_2 a b a b a b
level_3 L S L S L S L S L S L S
0 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
1 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
2 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
3 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
4 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
5 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
7 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
8 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
9 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
How can I achieve this?
I am using pandas 0.21 with Python 3.4
For me it working in pandas 0.23.0 version, but I think better is select by xs and subtract:
np.random.seed(10)
multi_idx = pd.MultiIndex.from_product([['A', 'B', 'C'], ['a', 'b'], ['L', 'S']],
names=['level_1', 'level_2', 'level_3'])
test_df = pd.DataFrame(np.random.randint(10, size=(10, len(multi_idx))), columns=multi_idx)
print (test_df)
level_1 A B C
level_2 a b a b a b
level_3 L S L S L S L S L S L S
0 9 4 0 1 9 0 1 8 9 0 8 6
1 4 3 0 4 6 8 1 8 4 1 3 6
2 5 3 9 6 9 1 9 4 2 6 7 8
3 8 9 2 0 6 7 8 1 7 1 4 0
4 8 5 4 7 8 8 2 6 2 8 8 6
5 6 5 6 0 0 6 9 1 8 9 1 2
6 8 9 9 5 0 2 7 3 0 4 2 0
7 3 3 1 2 5 9 0 1 0 1 9 0
8 9 2 1 1 0 0 5 9 0 4 6 6
9 0 2 3 3 2 6 0 5 1 3 6 5
df = test_df.xs('L', axis=1, level='level_3') - test_df.xs('S', axis=1, level='level_3')
print (df)
level_1 A B C
level_2 a b a b a b
0 5 -1 9 -7 9 2
1 1 -4 -2 -7 3 -3
2 2 3 8 5 -4 -1
3 -1 2 -1 7 6 4
4 3 -3 0 -4 -6 2
5 1 6 -6 8 -1 -1
6 -1 4 -2 4 -4 2
7 0 -1 -4 -1 -1 9
8 7 0 0 -4 -4 0
9 -2 0 -4 -5 -2 1
net = test_df.groupby(level=['level_1', 'level_2'], axis=1, group_keys=False)\
.apply(lambda x: x.xs('L', axis=1, level='level_3', drop_level=True) -
x.xs('S', axis=1, level='level_3', drop_level=True))
print (net)
level_1 A B C
level_2 a b a b a b
level_3 L S L S L S L S L S L S
0 5 5 -1 -1 9 9 -7 -7 9 9 2 2
1 1 1 -4 -4 -2 -2 -7 -7 3 3 -3 -3
2 2 2 3 3 8 8 5 5 -4 -4 -1 -1
3 -1 -1 2 2 -1 -1 7 7 6 6 4 4
4 3 3 -3 -3 0 0 -4 -4 -6 -6 2 2
5 1 1 6 6 -6 -6 8 8 -1 -1 -1 -1
6 -1 -1 4 4 -2 -2 4 4 -4 -4 2 2
7 0 0 -1 -1 -4 -4 -1 -1 -1 -1 9 9
8 7 7 0 0 0 0 -4 -4 -4 -4 0 0
9 -2 -2 0 0 -4 -4 -5 -5 -2 -2 1 1
If need new column with subtracted values one possible solution is drop_level=False for not remove S and L levels, rename for same new label name and concat together:
a = test_df.xs('L', axis=1, level='level_3', drop_level=False).rename(columns={'L':'L-S'}, level=2)
b = test_df.xs('S', axis=1, level='level_3', drop_level=False).rename(columns={'S':'L-S'}, level=2)
df = pd.concat([test_df, a - b], axis=1).sort_index(axis=1)
print (df)
level_1 A B C
level_2 a b a b a b
level_3 L L-S S L L-S S L L-S S L L-S S L L-S S L L-S S
0 9 5 4 0 -1 1 9 9 0 1 -7 8 9 9 0 8 2 6
1 4 1 3 0 -4 4 6 -2 8 1 -7 8 4 3 1 3 -3 6
2 5 2 3 9 3 6 9 8 1 9 5 4 2 -4 6 7 -1 8
3 8 -1 9 2 2 0 6 -1 7 8 7 1 7 6 1 4 4 0
4 8 3 5 4 -3 7 8 0 8 2 -4 6 2 -6 8 8 2 6
5 6 1 5 6 6 0 0 -6 6 9 8 1 8 -1 9 1 -1 2
6 8 -1 9 9 4 5 0 -2 2 7 4 3 0 -4 4 2 2 0
7 3 0 3 1 -1 2 5 -4 9 0 -1 1 0 -1 1 9 9 0
8 9 7 2 1 0 1 0 0 0 5 -4 9 0 -4 4 6 0 6
9 0 -2 2 3 0 3 2 -4 6 0 -5 5 1 -2 3 6 1 5