What would be the best way of creating a dataframe from dictionary, where dict keys are tuples: (index, column)?

I have a dictionary in the following format:

 tuple_key_dict = {
     (0, 'Q2'): 0.41,
     (1, 'Q2'): 0.52,
     (2, 'Q2'): 0.61,
     (0, 'Q3'): 0.66,
     (1, 'Q3'): 0.53,
     (3, 'Q3'): 0.66,
     (0, 'Q4'): 0.47,
     (1, 'Q4'): 0.52,
     (2, 'Q4'): 0.67
          }

I also have a list of column names:

interesting_columns=['Q2', 'Q3', 'Q4']

… and know that index is in range (0,1000). All colmn names and indices are used, but a few combinations are missing (eg. in example above: (2, ‘Q2’).)

The job here is to have all these in a nice dataframe where the first element of the tuple (key) is the index and the second one is the column name (and values are the dicitionary values). I tried a number of ways I don’t even wanna mention here – all failed miserably 🙂

>Solution :

I assume that you made a typo when you wrote that the combination (2, 'Q2') is missing, I kept the cells that don’t have values as NaN.

Code

tuple_key_dict = {
     (0, 'Q2'): 0.41,
     (1, 'Q2'): 0.52,
     (2, 'Q2'): 0.61,
     (0, 'Q3'): 0.66,
     (1, 'Q3'): 0.53,
     (3, 'Q3'): 0.66,
     (0, 'Q4'): 0.47,
     (1, 'Q4'): 0.52,
     (2, 'Q4'): 0.67
          }
columns = ["Q2", "Q3", "Q4"]
# our indexes are a set that contains all the first item of the keys
indexes = {key[0] for key in tuple_key_dict}
df = pd.DataFrame(index=indexes, columns=columns)
for key, value in tuple_key_dict.items():
     df.at[key[0], key[1]] = value
print(df)

Output

     Q2    Q3    Q4
0  0.41  0.66  0.47
1  0.52  0.53  0.52
2  0.61   NaN  0.67
3   NaN  0.66   NaN

Leave a Reply