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

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.

Answer

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')