How to merge Pandas DataFrame with dict of lists

Question

What is the best way to merge a df like this:

+------------+----------+
| domain     | username |
+------------+----------+
| @gmail.com | gagaga   |
+------------+----------+
| @mail.com  | bobo     |
+------------+----------+

with a dict like this:

domain_to_app = {
    '@gmail.com': ['gmail', 'youtube', 'gdrive'],
    '@mail.com': ['email', 'dropbox']
}

to get this:

+------------+----------+-----------+
| domain     | username | app       |
+------------+----------+-----------+
| @gmail.com | gagaga   | gmail     |
+------------+----------+-----------+
| @gmail.com | gagaga   | youtube   |
+------------+----------+-----------+
| @gmail.com | gagaga   | gdrive    |
+------------+----------+-----------+
| @mail.com  | bobo     | email     |
+------------+----------+-----------+
| @mail.com  | bobo     | dropbox   |
+------------+----------+-----------+

Is it recommended to convert the dict into a df with repeating rows and use merge, or should i use map then unstack the app column?

jezrael · Accepted Answer · 2018-11-26T12:15:17.430

3

You can use map for new Series and then chain.from_iterable with repeat for new DataFrame:

s = df['domain'].map(domain_to_app)

from itertools import chain

lens = s.str.len()
df = pd.DataFrame({
    'domain' : df['domain'].values.repeat(lens),
    'username' : df['username'].values.repeat(lens),
     'app' : list(chain.from_iterable(s))
})

print (df)
       domain username      app
0  @gmail.com   gagaga    gmail
1  @gmail.com   gagaga  youtube
2  @gmail.com   gagaga   gdrive
3   @mail.com     bobo    email
4   @mail.com     bobo  dropbox

If need repeat multiple columns create DaatFrame from mapped values, reshape by stack and 'repeat' by join:

df['app'] = df['domain'].map(domain_to_app)

df = (df.join(pd.DataFrame(df.pop('app')
                            .values.tolist())
               .stack()
               .reset_index(level=1, drop=True)
               .rename('app'))).reset_index(drop=True)
print (df)
       domain username      app
0  @gmail.com   gagaga    gmail
1  @gmail.com   gagaga  youtube
2  @gmail.com   gagaga   gdrive
3   @mail.com     bobo    email
4   @mail.com     bobo  dropbox

edited Nov 26 '18 at 12:15

answered Nov 26 '18 at 11:54

jezrael

822,522
95
1,334
1,252

1

never knew you could do `s.str.len()` to get the length of each Series item, i have been doing `s.apply(len)` all the while – iamanigeeit Nov 27 '18 at 06:58
`s.apply(len)` is faster by about 10% though – iamanigeeit Nov 27 '18 at 06:58
1

@iamanigeeit - yes, difference is `.apply(len)` not working with `NaN` :) – jezrael Nov 27 '18 at 06:59
Ahhh ok. I thought `s.str` was only for string operations. – iamanigeeit Nov 27 '18 at 07:09

Mohamed Thasin ah · Answer 2 · 2018-11-26T12:12:43.800

try this,

df2= pd.DataFrame.from_dict(domain_to_app,orient='index').unstack().reset_index()
result= pd.merge(df1,df2[df2[0].notnull()],left_on=['domain'],right_on=['level_1'])
result=result[['domain','username',0]].rename(columns={0:'app'})
print result

Output:

       domain username      app
0  @gmail.com   gagaga    gmail
1  @gmail.com   gagaga  youtube
2  @gmail.com   gagaga   gdrive
3   @mail.com     bobo    email
4   @mail.com     bobo  dropbox

explanation:

create Dataframe from you dictionary, perform pd.merge then clean the dataframe as it requires.

How to merge Pandas DataFrame with dict of lists

2 Answers2