Mapping dictionary with multiple key values to data frame

Question

I have a dictionary which has multiple key values.

d = {(0, 0, 'Shift 2 (2000 FT)'): 0.0, (0, 0, 'Shift 1 (0800 FT)'): 0.0, (0, 1, 'Shift 2 (2000 FT)'): 0.0, (0, 1, 'Shift 1 (0800 FT)'): 0.0, (0, 2, 'Shift 2 (2000 FT)'): 0.0, (0, 2, 'Shift 1 (0800 FT)'): 0.0}

I want to convert it to dataframe as follows where the first index is called 'WEEK', second called 'DAY' and the third goes to columns.

Please advise. Thanks!

Answer 1

You can read in the dict then turn the Index into a MultiIndex and reshape.

import pandas as pd

df = pd.DataFrame.from_dict(d, orient='index')
df.index = pd.MultiIndex.from_tuples(df.index)

df = (df[0].unstack(-1)
           .rename_axis(index=['Week', 'Day'])
           .reset_index())

---

   Week  Day  Shift 1 (0800 FT)  Shift 2 (2000 FT)
0     0    0                0.0                0.0
1     0    1                0.0                0.0
2     0    2                0.0                0.0

---

You can also create the initial DataFrame with the MultiIndex in one go with the normal constructor. Then do the unstacking.

df = pd.DataFrame(data=d.values(), index=d.keys())

Answer 2

Another option : create a flat list of list and then use pivot_table.

from itertools import chain
df = pd.DataFrame([list(chain(*[list(key),[val]])) for key,val in d.items()])
df = df.pivot_table(index=[0,1], columns=2).reset_index()
df.columns = ['Week','Day','Shift 1 (0800 FT)','Shift 2 (2000 FT)']