Pandas Denormalization

Asked Nov 12 '21 at 05:33

Active Nov 12 '21 at 05:33

Viewed 15 times

I have two dataframes I'd like to denormalize into one, e.g given:

>>> data1 = [['Akbar', 42], ['Akbar', 99], ['Jeff', 1]]
>>> df1 = pd.DataFrame(data1, columns = ["who", "number"])
>>> data2 = [['Akbar', "SF"], ["Jeff", "NYC"]]
>>> df2 = pd.DataFrame(data2, columns = ["who", "where"])
>>> df1
     who  number
0  Akbar      42
1  Akbar      99
2   Jeff       1
>>> df2
     who where
0  Akbar    SF
1   Jeff   NYC
>>>

I want to end up with

>>> df1
     who  number  where
0  Akbar      42     SF
1  Akbar      99     SF
2   Jeff       1    NYC

df1 has ~500k records and df2 has ~20k records so I'm looking for an efficient approach.

Any help greatly appreciated!

asked Nov 12 '21 at 05:33

Kylo

1

`df1['where'] =df1['who'].map(dict(zip(df2['who'], df2['where'])))` – wwnde Nov 12 '21 at 05:36
Use `df1.merge(df2)`. – Mayank Porwal Nov 12 '21 at 05:37
Try the following df1 = pd.merge(df1, df2, on = 'who') or df1 = df1.merge(df2, on = 'who') – Abhiram Nov 12 '21 at 05:38
Or `df1['where'] = df1.map(df2.set_index('who')['where'])` this might be faster than merge ig. – Ch3steR Nov 12 '21 at 05:39
1

Thanks @wwnde - I went with your suggestion which worked great. Whoever closed it with the pointer to Merging 101 - appreciate it and I have bookmarked that to circle back to. – Kylo Nov 12 '21 at 06:05

Pandas Denormalization

0 Answers0