How to make dictionary entry a pandas dataframe by specifying columns?-CodePudding

I have a dictionary like this:

mydict ={'9788845278518': [['/book/show/24235201-numero-zero', 'Italian'], ['/book/show/10522.Il_nome_della_rosa', 'Italian']]}

And I would like to make it a dataframe like this:

ISBN, LINK, LANG
9788845278518, /book/show/24235201-numero-zero, Italian
9788845278518, /book/show/10522.Il_nome_della_rosa, Italian

So I tried by doing:

df = pd.DataFrame.from_dict(mydict)

And I wanted to use df.explode and then doing transpose, but it didn't really work because my column is not named. Also, there must be a simpler way to do it...

CodePudding user response：

You could try using a defaultdict before creating a Dataframe, since pandas operations are usually more expensive:

import pandas as pd
from collections import defaultdict

mydict ={'9788845278518': [['/book/show/24235201-numero-zero', 'Italian'], ['/book/show/10522.Il_nome_della_rosa', 'Italian']]}

d = defaultdict(list)
for key,value in mydict.items():
  for v in value:
    d.setdefault('ISBN', []).append(key) 
    d.setdefault('LINK', []).append(v[0])
    d.setdefault('LANG', []).append(v[1])    

df = pd.DataFrame(dict(d))
print(df)

            ISBN                                 LINK     LANG
0  9788845278518      /book/show/24235201-numero-zero  Italian
1  9788845278518  /book/show/10522.Il_nome_della_rosa  Italian

CodePudding user response：

df = pd.DataFrame.from_dict(mydict)
df = pd.melt(df, var_name='ISBN', value_name='value')
df[['LINK', 'LANG']] = df['value'].apply(pd.Series)
df.drop(columns='value')

melt to move column names to the new ISBN column, and explode the lists into the two new columns LINK, LANG

CodePudding user response：

Flatten the dictionary using list comprehension then create a new dataframe

df = pd.DataFrame(
    [(k, *l) for k, v in mydict.items() for l in v], 
    columns=['ISBN', "LINK", 'LANG']
)

print(df)
            ISBN                                 LINK     LANG
0  9788845278518      /book/show/24235201-numero-zero  Italian
1  9788845278518  /book/show/10522.Il_nome_della_rosa  Italian