How to create multiple new dataframe columns from a nested dict, based on the dict key

Question

Building on this question, starting with this dataframe:

import pandas as pd
data = {'key':[111, 112, 113, 114],'other_data':[1, 2, 5, 7]}
df = pd.DataFrame(data)

df

   key  other_data
0  111           1
1  112           2
2  113           5
3  114           7

and I want to map multiple new columns based on the dictionary key and one specified column in the dataframe.

d = {
    "111": {
        "en": 4,
        "es": 2
    },
    "112": {
        "en": 1,
        "es": 8
    },
    "113": {
        "en": 0,
        "es": 11
    },
    "114": {
        "en": 2,
        "es": 3
    }
}

The expected output dataframe is

key,other_data,en,es
111,1,4,2
112,2,1,8
113,5,0,11
114,7,2,3

The dataframe is large so I'd prefer to do this in one loop or operation if possible.

I tried various combinations of map() and apply() but couldn't get it to work.

jezrael · Accepted Answer · 2021-07-13T08:21:42.377

5

Create DataFrame from dictionary, transpose and convert index to integers for match key column by DataFrame.join (need same types):

df = df.join(pd.DataFrame(d).T.rename(index=int), on='key')
print (df)
   key  other_data  en  es
0  111           1   4   2
1  112           2   1   8
2  113           5   0  11
3  114           7   2   3

Thank you @ThePyGuy for suggesting use DataFrame.from_dict:

df = df.join(pd.DataFrame.from_dict(d, orient='index').rename(index=int), on='key')

edited Jul 13 '21 at 08:21

answered Jul 13 '21 at 08:02

jezrael

822,522
95
1,334
1,252

If the column key contains strings, then just change to rename(index=str)? – Pranab Jul 13 '21 at 08:11
1

@Pranab - then remove `.rename(index=int)` – jezrael Jul 13 '21 at 08:12
1

@Pranab - It is index created from keys in dict, if in original dataframe `key` columns are strings then remove `.rename(index=int)` – jezrael Jul 13 '21 at 08:13
3

I think `pd.DataFrame.from_dict(d, orient='index')` would be better rather than creating the dataframe then transposing. – ThePyGuy Jul 13 '21 at 08:20

score 2 · Answer 2 · answered Jul 13 '21 at 08:24

I would recommend using the same data types for the column on which you are planning to merge as this matters in pandas, if that's not an issue and you plan to do it manually then you can try this:

import pandas as pd
df1 = pd.DataFrame.from_dict(d, orient="index")
df1.reset_index(inplace=True)

df = df.merge(df1, left_on="key", right_on="index")
df.drop(columns="index", inplace=True)

However, if you wish not to change the columns, this is a hacky way. Use pandas concat.

df = pd.concat((df, df1), axis=1).drop(columns="index")

Output in both the scenarios:

   key  other_data  en  es
0  111           1   4   2
1  112           2   1   8
2  113           5   0  11
3  114           7   2   3

How to create multiple new dataframe columns from a nested dict, based on the dict key

2 Answers2