Referencing multi-column locations in a pivot table dataFrame

Question

I have some data that I have imported into a dataFrame, from which I have created a multi-column pivot table CokePepsi.pivot_table(index=['Date'], columns=['Brand', 'Type'], values=['cratesSold'], aggfunc='sum')

Here is the resulting pivot table filtered for just Coke:

    cratesSold
Brand   Coke
Type    Diet    Regular
Date        
4/14/21 9928    10729
4/15/21 8526    8556
4/16/21 7556    7788
4/19/21 9709    9765
4/20/21 13234   13588
4/21/21 11946   11401
4/22/21 14407   14956
4/23/21 10956   11776
4/26/21 8324    7487
4/27/21 7705    8166
4/28/21 6771    6471
4/30/21 10988   10594
5/4/21  11351   11635
5/7/21  8809    9454
5/12/21 12081   13249
5/13/21 12674   11942
5/14/21 8729    10869
5/17/21 6217    6900
5/18/21 10502   8306
5/19/21 8990    11061
5/20/21 8210    8316

I would like to create two new columns in this pivot table: One containing the values of Coke(Diet) * -1 (i.e., the negative value for all values in the Coke-Diet column ) and another column that calculates the difference (i.e., delta) between Diet and Regular: Coke(Regular) – Coke(Diet)

I know how to add columns to a basic dataframe as well as run calculations using data from other columns in a basic dataframe, however I am struggling to find the syntax to properly reference the multi-column locations of the pivot table.

UPDATE:

As per @sammywemmy request, I am including the data as a dictionary.

[{' Date': '25-Jun-21', ' Time': '23:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Pepsi', ' cratePrice': 2330.6}, {' Date': '25-Jun-21', ' Time': '22:00:00', ' cratesSold': 3.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2330.43}, {' Date': '25-Jun-21', ' Time': '21:00:00', ' cratesSold': 3.0, ' Type': 'Regular', ' Brand': 'Pepsi', ' cratePrice': 2330.9}, {' Date': '25-Jun-21', ' Time': '20:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2330.6}, {' Date': '25-Jun-21', ' Time': '19:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2330.7}, {' Date': '25-Jun-21', ' Time': '18:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2331.0}, {' Date': '25-Jun-21', ' Time': '17:00:00', ' cratesSold': 5.0, ' Type': 'Regular', ' Brand': 'Pepsi', ' cratePrice': 2331.5}, {' Date': '25-Jun-21', ' Time': '16:00:00', ' cratesSold': 4.0, ' Type': 'Regular', ' Brand': 'Pepsi', ' cratePrice': 2331.97}, {' Date': '25-Jun-21', ' Time': '15:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Pepsi', ' cratePrice': 2334.03}, {' Date': '25-Jun-21', ' Time': '14:00:00', ' cratesSold': 1.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2334.57}, {' Date': '25-Jun-21', ' Time': '13:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2334.95}, {' Date': '25-Jun-21', ' Time': '12:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Pepsi', ' cratePrice': 2335.8}, {' Date': '25-Jun-21', ' Time': '11:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2335.8}, {' Date': '25-Jun-21', ' Time': '10:00:00', ' cratesSold': 5.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2335.8}, {' Date': '25-Jun-21', ' Time': '9:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Pepsi', ' cratePrice': 2335.67}, {' Date': '25-Jun-21', ' Time': '8:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2335.47}, {' Date': '25-Jun-21', ' Time': '7:00:00', ' cratesSold': 4.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2335.4}, {' Date': '25-Jun-21', ' Time': '6:00:00', ' cratesSold': 6.0, ' Type': 'Regular', ' Brand': 'Pepsi', ' cratePrice': 2334.72}, {' Date': '25-Jun-21', ' Time': '5:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2334.87}, {' Date': '25-Jun-21', ' Time': '4:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2334.77}, {' Date': '25-Jun-21', ' Time': '3:00:00', ' cratesSold': 8.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2334.63}, {' Date': '25-Jun-21', ' Time': '2:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2334.57}, {' Date': '25-Jun-21', ' Time': '1:00:00', ' cratesSold': 5.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2334.4}, {' Date': '25-Jun-21', ' Time': '0:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2334.27}, {' Date': '24-Jun-21', ' Time': '23:00:00', ' cratesSold': 3.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2333.9}, {' Date': '24-Jun-21', ' Time': '22:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2333.8}, {' Date': '24-Jun-21', ' Time': '21:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2333.37}, {' Date': '24-Jun-21', ' Time': '20:00:00', ' cratesSold': 3.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2330.6}, {' Date': '24-Jun-21', ' Time': '19:00:00', ' cratesSold': 3.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2330.43}, {' Date': '24-Jun-21', ' Time': '18:00:00', ' cratesSold': 7.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2330.9}, {' Date': '24-Jun-21', ' Time': '17:00:00', ' cratesSold': 5.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2330.6}, {' Date': '24-Jun-21', ' Time': '16:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2330.7}, {' Date': '24-Jun-21', ' Time': '15:00:00', ' cratesSold': 1.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2331.0}, {' Date': '24-Jun-21', ' Time': '14:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2331.5}, {' Date': '24-Jun-21', ' Time': '13:00:00', ' cratesSold': 6.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2331.97}, {' Date': '24-Jun-21', ' Time': '12:00:00', ' cratesSold': 9.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2334.03}, {' Date': '24-Jun-21', ' Time': '11:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2334.57}, {' Date': '24-Jun-21', ' Time': '10:00:00', ' cratesSold': 3.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2334.95}, {' Date': '24-Jun-21', ' Time': '9:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2335.27}, {' Date': '24-Jun-21', ' Time': '8:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2335.47}, {' Date': '24-Jun-21', ' Time': '7:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2335.4}, {' Date': '24-Jun-21', ' Time': '6:00:00', ' cratesSold': 8.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2335.22}, {' Date': '24-Jun-21', ' Time': '5:00:00', ' cratesSold': 3.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2335.3}, {' Date': '24-Jun-21', ' Time': '4:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2334.77}, {' Date': '24-Jun-21', ' Time': '3:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2334.93}, {' Date': '24-Jun-21', ' Time': '2:00:00', ' cratesSold': 4.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2335.07}, {' Date': '24-Jun-21', ' Time': '1:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2335.73}, {' Date': '24-Jun-21', ' Time': '0:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2336.73}, {' Date': '23-Jun-21', ' Time': '23:00:00', ' cratesSold': 3.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2336.7}, {' Date': '23-Jun-21', ' Time': '22:00:00', ' cratesSold': 5.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2336.8}, {' Date': '23-Jun-21', ' Time': '21:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2336.8}, {' Date': '23-Jun-21', ' Time': '20:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2336.8}, {' Date': '23-Jun-21', ' Time': '19:00:00', ' cratesSold': 12.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2336.83}, {' Date': '23-Jun-21', ' Time': '18:00:00', ' cratesSold': 3.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2337.17}, {' Date': '23-Jun-21', ' Time': '17:00:00', ' cratesSold': 4.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2337.14}, {' Date': '23-Jun-21', ' Time': '16:00:00', ' cratesSold': 6.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2337.1}, {' Date': '23-Jun-21', ' Time': '15:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2336.97}, {' Date': '23-Jun-21', ' Time': '14:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Pepsi', ' cratePrice': 2336.8}, {' Date': '23-Jun-21', ' Time': '13:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2336.6}, {' Date': '23-Jun-21', ' Time': '12:00:00', ' cratesSold': 3.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2337.0}, {' Date': '23-Jun-21', ' Time': '11:00:00', ' cratesSold': 11.0, ' Type': 'Regular', ' Brand': 'Pepsi', ' cratePrice': 2337.0}, {' Date': '23-Jun-21', ' Time': '10:00:00', ' cratesSold': 3.0, ' Type': 'Regular', ' Brand': 'Pepsi', ' cratePrice': 2336.6}, {' Date': '23-Jun-21', ' Time': '9:00:00', ' cratesSold': 7.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2336.8}, {' Date': '23-Jun-21', ' Time': '8:00:00', ' cratesSold': 5.0, ' Type': 'Regular', ' Brand': 'Pepsi', ' cratePrice': 2336.97}, {' Date': '23-Jun-21', ' Time': '7:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2337.1}, {' Date': '23-Jun-21', ' Time': '6:00:00', ' cratesSold': 1.0, ' Type': 'Regular', ' Brand': 'Pepsi', ' cratePrice': 2337.14}, {' Date': '23-Jun-21', ' Time': '5:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2337.17}, {' Date': '23-Jun-21', ' Time': '4:00:00', ' cratesSold': 6.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2336.83}, {' Date': '23-Jun-21', ' Time': '3:00:00', ' cratesSold': 9.0, ' Type': 'Regular', ' Brand': 'Pepsi', ' cratePrice': 2336.8}, {' Date': '23-Jun-21', ' Time': '2:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2336.8}, {' Date': '23-Jun-21', ' Time': '1:00:00', ' cratesSold': 3.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2336.8}, {' Date': '23-Jun-21', ' Time': '0:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2334.93}, {' Date': '22-Jun-21', ' Time': '23:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Pepsi', ' cratePrice': 2334.77}, {' Date': '22-Jun-21', ' Time': '22:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2335.3}, {' Date': '22-Jun-21', ' Time': '21:00:00', ' cratesSold': 8.0, ' Type': 'Diet', ' Brand': 'Pepsi', ' cratePrice': 2335.22}, {' Date': '22-Jun-21', ' Time': '20:00:00', ' cratesSold': 3.0, ' Type': 'Diet', ' Brand': 'Pepsi', ' cratePrice': 2335.4}, {' Date': '22-Jun-21', ' Time': '19:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Pepsi', ' cratePrice': 2335.47}, {' Date': '22-Jun-21', ' Time': '18:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2335.27}, {' Date': '22-Jun-21', ' Time': '17:00:00', ' cratesSold': 1.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2334.95}, {' Date': '22-Jun-21', ' Time': '16:00:00', ' cratesSold': 1.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2334.57}, {' Date': '22-Jun-21', ' Time': '15:00:00', ' cratesSold': 3.0, ' Type': 'Diet', ' Brand': 'Pepsi', ' cratePrice': 2334.03}, {' Date': '22-Jun-21', ' Time': '14:00:00', ' cratesSold': 3.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2334.03}, {' Date': '22-Jun-21', ' Time': '13:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Pepsi', ' cratePrice': 2334.57}, {' Date': '22-Jun-21', ' Time': '12:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2334.95}, {' Date': '22-Jun-21', ' Time': '11:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2335.27}, {' Date': '22-Jun-21', ' Time': '10:00:00', ' cratesSold': 8.0, ' Type': 'Diet', ' Brand': 'Pepsi', ' cratePrice': 2335.47}, {' Date': '22-Jun-21', ' Time': '9:00:00', ' cratesSold': 3.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2335.4}, {' Date': '22-Jun-21', ' Time': '8:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2335.22}, {' Date': '22-Jun-21', ' Time': '7:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2335.3}, {' Date': '22-Jun-21', ' Time': '6:00:00', ' cratesSold': 4.0, ' Type': 'Diet', ' Brand': 'Pepsi', ' cratePrice': 2334.77}, {' Date': '22-Jun-21', ' Time': '5:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2335.47}, {' Date': '22-Jun-21', ' Time': '4:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2335.27}, {' Date': '22-Jun-21', ' Time': '3:00:00', ' cratesSold': 3.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2334.95}, {' Date': '22-Jun-21', ' Time': '2:00:00', ' cratesSold': 5.0, ' Type': 'Diet', ' Brand': 'Pepsi', ' cratePrice': 2334.57}, {' Date': '22-Jun-21', ' Time': '1:00:00', ' cratesSold': 2.0, ' Type': 'Diet', ' Brand': 'Coke', ' cratePrice': 2334.03}, {' Date': '22-Jun-21', ' Time': '0:00:00', ' cratesSold': 2.0, ' Type': 'Regular', ' Brand': 'Coke', ' cratePrice': 2334.03}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}, {' Date': nan, ' Time': nan, ' cratesSold': nan, ' Type': nan, ' Brand': nan, ' cratePrice': nan}]

And here is the idea of what I would like to achieve (produced in excel), with the two colored columns being the newly added ones that calculate based on the existing pivot columns.

UPDATE 2:

After a lot of searching, I've cobbled together a solution that partially works—but I would appreciate help resolving the remaining error.

Using some simplified source data:

np.random.seed(1)
df = pd.DataFrame(np.random.random((10, 12)))
df.columns = pd.MultiIndex.from_product(
    [
        ['one', 'two', 'three'],
        ['A', 'B', 'C', 'D']
    ]
)

I am able to add a new column and sub-column, and populate those new columns with a calculation based on a existing multi-index column (thereby solving the first part of my original question of getting a new column containing the negative values of an existing column):

df[('newMeta', 'newSub')] = df.loc[0:len(df.index), [('one', 'C')]] * -1

        one                      ...     three             newMeta
          A         B         C  ...         C         D    newSub
0  0.209685  0.719405  0.313690  ...  0.223464  0.177383 -0.313690
1  0.280559  0.788768  0.679686  ...  0.332770  0.866036 -0.679686
2  0.763944  0.990348  0.103789  ...  0.067546  0.134955 -0.103789
3  0.945861  0.220135  0.929363  ...  0.669494  0.743312 -0.929363
4  0.455665  0.903104  0.652752  ...  0.610537  0.887649 -0.652752
5  0.070355  0.401810  0.841703  ...  0.288189  0.711274 -0.841703
6  0.700615  0.213194  0.156292  ...  0.127861  0.363358 -0.156292
7  0.738146  0.878900  0.317653  ...  0.354384  0.492631 -0.317653
8  0.766122  0.518608  0.247643  ...  0.736120  0.952106 -0.247643
9  0.669162  0.474207  0.599781  ...  0.658298  0.913724 -0.599781

I am similarly able to create another multi-indexed column which adds the values of an existing multi-index column to itself (shorthand: new multi-column [('Meta2', 'Sub2')] = [('one', 'C')] + [('one', 'C')]

df[('Meta2', 'Sub2')] = (df.loc[0:len(df.index), [('one', 'C')]]) + (df.loc[0:len(df.index), [('one', 'C')]])

        one                      ...     three   newMeta     Meta2
          A         B         C  ...         D    newSub      Sub2
0  0.209685  0.719405  0.313690  ...  0.177383 -0.313690  0.627380
1  0.280559  0.788768  0.679686  ...  0.866036 -0.679686  1.359372
2  0.763944  0.990348  0.103789  ...  0.134955 -0.103789  0.207578
3  0.945861  0.220135  0.929363  ...  0.743312 -0.929363  1.858727
4  0.455665  0.903104  0.652752  ...  0.887649 -0.652752  1.305503
5  0.070355  0.401810  0.841703  ...  0.711274 -0.841703  1.683407
6  0.700615  0.213194  0.156292  ...  0.363358 -0.156292  0.312584
7  0.738146  0.878900  0.317653  ...  0.492631 -0.317653  0.635306
8  0.766122  0.518608  0.247643  ...  0.952106 -0.247643  0.495286
9  0.669162  0.474207  0.599781  ...  0.913724 -0.599781  1.199561

However, the very frustrating and seemingly inexplicable barrier is the inability to change a single index reference from the previous [('one', 'C')] + [('one', 'C')].

If I attempt something like [('one', 'C')] + [('two', 'C')] I am slapped with a series of errors (copied at the end).

Can anyone explain why [('one', 'C')] + [('one', 'C')] should result in a correctly calculated value in a successfully added new column, whereas [('one', 'C')] + [('two', 'C')] throws an error?

This is the official call of the problematic statement:

df[('Meta2', 'Sub2')] = (df.loc[0:len(df.index), [('one', 'C')]]) + (df.loc[0:len(df.index), [('two', 'C')]])

and the full list of errors:

Traceback (most recent call last):
  File "/Users/user/opt/anaconda3/lib/python3.8/site-packages/pandas/core/indexes/base.py", line 3081, in get_loc
    return self._engine.get_loc(casted_key)
  File "pandas/_libs/index.pyx", line 70, in pandas._libs.index.IndexEngine.get_loc
  File "pandas/_libs/index.pyx", line 101, in pandas._libs.index.IndexEngine.get_loc
  File "pandas/_libs/hashtable_class_helper.pxi", line 4554, in pandas._libs.hashtable.PyObjectHashTable.get_item
  File "pandas/_libs/hashtable_class_helper.pxi", line 4562, in pandas._libs.hashtable.PyObjectHashTable.get_item
KeyError: 'Meta2'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "pandas/_libs/index.pyx", line 705, in pandas._libs.index.BaseMultiIndexCodesEngine.get_loc
  File "/Users/user/opt/anaconda3/lib/python3.8/site-packages/pandas/core/indexes/base.py", line 3083, in get_loc
    raise KeyError(key) from err
KeyError: 'Meta2'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/user/opt/anaconda3/lib/python3.8/site-packages/pandas/core/generic.py", line 3826, in _set_item
    loc = self._info_axis.get_loc(key)
  File "/Users/user/opt/anaconda3/lib/python3.8/site-packages/pandas/core/indexes/multi.py", line 2886, in get_loc
    return self._engine.get_loc(key)
  File "pandas/_libs/index.pyx", line 708, in pandas._libs.index.BaseMultiIndexCodesEngine.get_loc
KeyError: ('Meta2', 'Sub2')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/user/Desktop/ScratchSpace/ExtractIce/multiIndexTests.py", line 156, in <module>
    df[('Meta2', 'Sub2')] = (df.loc[0:len(df.index), [('one', 'C')]]) + \
  File "/Users/user/opt/anaconda3/lib/python3.8/site-packages/pandas/core/frame.py", line 3163, in __setitem__
    self._set_item(key, value)
  File "/Users/user/opt/anaconda3/lib/python3.8/site-packages/pandas/core/frame.py", line 3243, in _set_item
    NDFrame._set_item(self, key, value)
  File "/Users/user/opt/anaconda3/lib/python3.8/site-packages/pandas/core/generic.py", line 3829, in _set_item
    self._mgr.insert(len(self._info_axis), key, value)
  File "/Users/user/opt/anaconda3/lib/python3.8/site-packages/pandas/core/internals/managers.py", line 1203, in insert
    block = make_block(values=value, ndim=self.ndim, placement=slice(loc, loc + 1))
  File "/Users/user/opt/anaconda3/lib/python3.8/site-packages/pandas/core/internals/blocks.py", line 2751, in make_block
    return klass(values, ndim=ndim, placement=placement)
  File "/Users/user/opt/anaconda3/lib/python3.8/site-packages/pandas/core/internals/blocks.py", line 142, in __init__
    raise ValueError(
ValueError: Wrong number of items passed 2, placement implies 1

Does this answer your question? [pandas dataframe select columns in multiindex](https://stackoverflow.com/questions/25189575/pandas-dataframe-select-columns-in-multiindex) — Cornelius Roemer, Jul 13 '21 at 00:32
Thanks, Cornelius. This seems promising, but I am still having trouble implementing MultiIndex.get_level_values(). Could you please provide a quick example of how I can use in it my case? — ZwiTrader, Jul 13 '21 at 01:21
kindly share the source dataframe as code, along with ur expected output — sammywemmy, Jul 13 '21 at 04:02
@sammywemmy I am not sure what you mean by sharing the dataframe as code. Are you asking for the raw data? — ZwiTrader, Jul 13 '21 at 05:25
Your multiindex data frame can be converted to a dict: `do.to_dict(‘records’)` makes it much easier to reproduce — sammywemmy, Jul 13 '21 at 06:35
@sammywemmy I have converted my data via `do.to_dict(‘records’)` and pasted it in to the bottom of my original question. Please let me know if you were asking for something different. Thanks. — ZwiTrader, Jul 13 '21 at 14:36
@CorneliusRoemer are you able to provide any feedback following UPDATE2 , which I have posted in my original? Thanks. — ZwiTrader, Jul 13 '21 at 23:00
so, no need for the first question? ... do u mind adding np.random.seed(1) to your update2, to make it reproducible? — sammywemmy, Jul 13 '21 at 23:31
Sure—where do I add `np.random.seed(1)`? *Maybe* I have question 1 solved (although the errors suggest there may be something deeper at play, warranting another solution). Also, I discovered that if I create the multi-column first with `df[('Meta2', 'Sub2')] = ''` then I avoid that huge run of errors. However, subsequently attempting `df[('Meta2', 'Sub2')] = (df.loc[0:len(df.index), [('one', 'C')]]) + (df.loc[0:len(df.index), [('two', 'C')]])` results in `NaN` values in the new multi-column. Seems like every step forward is only half the distance of the previous step forward... — ZwiTrader, Jul 14 '21 at 00:10

score 1 · Accepted Answer · answered Jul 15 '21 at 02:40

If you convert the two columns to numpy arrays rather than pandas dataframes, this should solve your issue. You can use the following:

df[('Meta2', 'Sub2')] = (df.loc[0:len(df.index), [('one', 'C')]]).to_numpy() \ + (df.loc[0:len(df.index), [('two', 'C')]]).to_numpy()

The important part to note is the .to_numpy() function. This will add the values of the columns together, rather than the columns themselves.

Referencing multi-column locations in a pivot table dataFrame

1 Answers1