I have a bizarre mutliindex such as follows:
test_df = {}
for letter in ['A', "B"]:
for number in range(2, 4):
for other_number in range(number):
for id_ in range(100, 105):
test_df[(letter, number, other_number, "int", id_)] = np.random.randint(10)
test_df[(letter, number, other_number, "float", id_)] = np.random.random(1)
# note: there is prob a better way of doing this, but it works
test_df = pd.DataFrame(test_df).stack().loc[0]
I would like to add the total and average at the other_number level.
For example, for total I can easily compute
totals = test_df.loc[:, (slice(None), slice(None), slice(None), 'int')].sum(axis=1, level=[0, 1])
i.e.
A B
2 3 2 3
100 7 16 5 15
101 10 13 15 11
102 4 10 16 16
103 4 14 11 14
104 13 22 4 7
but the assignment is giving me problem.
test_df.loc[:, (slice(None), slice(None), slice(None), 'total')] = totals
specifically
KeyError: 'total'
I understand that total was not in my keys, but this is the whole point: I want to create a new column.
So my questions are:
- how do I assign to the total?
- how do I assign the average of the float (at the same level?)
