Applying new row within second level index

Question

I have a data frame that looks something like:

+-----------+---------+-------+-------+-------+
|           |         | Day 1 | Day 2 | Day 3 |
+-----------+---------+-------+-------+-------+
| Product 1 | Revenue |     0 |     0 |     0 |
|           | Cost    |     0 |     0 |     0 |
| Product 2 | Revenue |     0 |     0 |     0 |
|           | Cost    |     0 |     0 |     0 |
| Product 3 | Revenue |     0 |     0 |     0 |
|           | Cost    |     0 |     0 |     0 |
+-----------+---------+-------+-------+-------+

Essentially a two level index on the vertical. The first level is the product and the second either revenue or cost.

I would like to add a Profit row to all products below Revenue and Cost (simply Revenue - Cost). Or even the average of that products Revenue etc. However, after much experimentation with apply, I cannot seem to get this to work with the multi levels.

Product 1 Revenue    0
          Cost       0
          Profit     0

How could this be achieved?

FYI - see https://stackoverflow.com/a/20159305/4049396 for tips on how to write a good question :) — Little Bobby Tables, Oct 05 '18 at 08:29

score 1 · Accepted Answer · answered Oct 05 '18 at 08:23

It does depend on how many times you want to do this and how your current other values are stored.

If you wish to add just a small number of Profits to each of the above you could use this method. However, this method uses ix which is to be deprecated (I believe). Therefore, I would suggest using at,

df = pd.DataFrame({('A', 'b'): [1, 2, 3], ('A', 'a'): [7, 2, 9]}).T
df.at[('B', 'a'), :] = [1, 4, 5]

Out[1]:     
                0    1   2
         A  b   1    2   3
            a   7    2   9
         B  a   1    4   5

If your Profit information is stored in another DataFrame, it would be easiest to use concat, like so,

df = pd.DataFrame({('A', 'b'): [1, 2, 3], ('A', 'a'): [7, 2, 9]}).T
df2 = pd.DataFrame({('B', 'a'): [1, 4, 5]}).T

pd.concat([df, df2])

Out[1]: 
        0   1   2
A   b   1   2   3
    a   7   2   9
B   a   1   4   5

Applying new row within second level index

1 Answers1