Convert dictionary items to rows of pandas data frame where keys are tuples and values are integers

Question

I have a dictionary as follows:

d = {("Sam","Scotland","23") : 25,
     ("Oli","England","23") : 28,
     ("Ethan","Wales","18") : 19}

I would like to convert it into a pandas data frame which would look like this:

Name    Country    Age    Count
Sam     Scotland    23     25
Oli     England     23     28
Ethan   Wales       18     19

I tried to do it like this:

df = pd.DataFrame.from_items(d.items(),orient="index",
                              columns=["Name","Country","Age","Count"])

But I get this error:

ValueError: The value in each (key, value) pair must be an array, Series, or dict

I appreciate that it would be possible by looping through each item and each element of the tuple, but is there a cleaner way to do it?

Answer 1

You could build the rows of the DataFrame manually by concatenating the key and the value, for instance like this:

import pandas as pd

d = {("Sam", "Scotland", "23"): 25,
     ("Oli", "England", "23"): 28,
     ("Ethan", "Wales", "18"): 19}

df = pd.DataFrame([k + (v,) for k, v in d.items()], columns=['name', 'country', 'age', 'count'])
print(df)

Output

    name   country age  count
0  Ethan     Wales  18     19
1    Sam  Scotland  23     25
2    Oli   England  23     28

Or as an alternative:

import pandas as pd

d = {("Sam", "Scotland", "23"): 25,
     ("Oli", "England", "23"): 28,
     ("Ethan", "Wales", "18"): 19}

df = pd.DataFrame(
    [{"name": name, "country": country, "age": age, "count": value} for (name, country, age), value in d.items()])

print(df)

Output

  age  count   country   name
0  23     28   England    Oli
1  23     25  Scotland    Sam
2  18     19     Wales  Ethan

The idea is to transform the key value pairs into a list of dictionaries and then pass it to DataFrame.