Pandas: How to structure data in more than two dimensions?

Question

I have a dataframe of quantities for each of a number products (below labeled 'A','B','C',etc) for each month...

import pandas as pd
import numpy as np

np.random.seed(0)
range = pd.date_range('2020-01-31', periods=12, freq='M')
column_names = list('ABCDEFGH')
quantities = pd.DataFrame(np.random.randint(0,100,size=(12, 8)), index=range, columns=column_names)
quantities

# Output
#               A   B   C   D   E   F   G   H
# 2020-01-31    44  47  64  67  67  9   83  21
# 2020-02-29    36  87  70  88  88  12  58  65
# 2020-03-31    39  87  46  88  81  37  25  77
# 2020-04-30    72  9   20  80  69  79  47  64
# 2020-05-31    82  99  88  49  29  19  19  14
# 2020-06-30    39  32  65  9   57  32  31  74
# 2020-07-31    23  35  75  55  28  34  0   0
# 2020-08-31    36  53  5   38  17  79  4   42
# 2020-09-30    58  31  1   65  41  57  35  11
# 2020-10-31    46  82  91  0   14  99  53  12
# 2020-11-30    42  84  75  68  6   68  47  3
# 2020-12-31    76  52  78  15  20  99  58  23

I also have a dataframe of the unit costs for each product for each month. And from these, I have calculated a third dataframe of the costs (quantity x unit cost) for each product for each month.

unit_costs = pd.DataFrame(np.random.rand(12, 8), index=range, columns=column_names)
costs = quantities*unit_costs

The code below produces a dataframe of the bill for the first month (bill0)...

bill0 = pd.DataFrame({'quantity': quantities.iloc[0],'unit_cost': unit_costs.iloc[0],'cost': costs.iloc[0]})
bill0
# Output
#   quantity    unit_cost   cost
# A 44          0.338008    14.872335
# B 47          0.674752    31.713359
# C 64          0.317202    20.300911
# D 67          0.778345    52.149147
# E 67          0.949571    63.621261
# F 9           0.662527    5.962742
# G 83          0.013572    1.126446
# H 21          0.622846    13.079768

I would like to efficiently produce a dataframe of the bill for any specific month. It seems that a 3D data structure is required, and I'm too new to python to know how to approach it.

Perhaps an array of bill dataframes - one for each month? (If so, how?)

Or perhaps the quantities, unit_costs, and amounts dataframes should first be combined into a multi-indexed dataframe and then that could be filtered (or otherwise manipulated) to produce the bill dataframe for whichever month I'm after? (If so, how?)

Or is there a more elegant way of doing this?

Thanks so much for your time!

user_6361531 · Answer

IIUC, you can use MultiIndex column headers:

pd.concat(
    [quantities, unit_costs, costs], keys=["Quantity", "Unit Cost", "Cost"], axis=1
).swaplevel(0, 1, axis=1).sort_index(level=0, axis=1)

Output (just printed A and B but dataframe has all products):

                   A                             B                   
                 Cost Quantity Unit Cost       Cost Quantity Unit Cost
2020-01-31  14.872335       44  0.338008  31.713359       47  0.674752
2020-02-29  24.251747       36  0.673660  84.559215       87  0.971945
2020-03-31  38.203882       39  0.979587  31.271668       87  0.359444
2020-04-30  62.287384       72  0.865103   4.580721        9  0.508969
2020-05-31  53.068279       82  0.647174  83.297226       99  0.841386
2020-06-30  22.215118       39  0.569618  22.519593       32  0.703737
2020-07-31  20.505752       23  0.891554  23.801945       35  0.680056
2020-08-31   8.992666       36  0.249796  16.600572       53  0.313218
2020-09-30  35.890897       58  0.618809  14.720893       31  0.474868
2020-10-31   4.731714       46  0.102863   7.574659       82  0.092374
2020-11-30   5.933084       42  0.141264   8.169834       84  0.097260
2020-12-31  35.662937       76  0.469249  43.739287       52  0.841140

Pandas: How to structure data in more than two dimensions?

1 Answers