Concat dataframes by matching a value in a column to a list in another column

Question

I have 2 data frames with the following format:

    df1

    ID                     age
    [111, 222, 333]        15
    [444]                  9
    [555, 666, 777, 888]   8


    df2

    ID                school
    222               A
    777               B

I need to concat them by matching the IDs to get the following result

    df1_ID                 age   df2_ID   school 
    [111, 222, 333]        15    222      A
    [555, 666, 777, 888]   8     777      B

df1_ID could be a list of up to 10 IDs and I can't think of a way to concat the data frames efficiently. How would you approach this? Thanks.

score 0 · Accepted Answer · answered Mar 26 '19 at 10:12

If want working with data efficiently, is necessary change format, because working with lists in pandas is obviously slow.

from itertools import chain

df11 = pd.DataFrame({
    'ID' : list(chain.from_iterable(df1['ID'].tolist())), 
    'age' : df1['age'].values.repeat(df1['ID'].str.len())
})

print (df11)
    ID  age
0  111   15
1  222   15
2  333   15
3  444    9
4  555    8
5  666    8
6  777    8
7  888    8

df12 = df11.merge(df2, on='ID', how='left')
print (df12)
    ID  age school
0  111   15    NaN
1  222   15      A
2  333   15    NaN
3  444    9    NaN
4  555    8    NaN
5  666    8    NaN
6  777    8      B
7  888    8    NaN

Thanks for the suggestion. Following you advice, I found [this answer](https://stackoverflow.com/q/53218931) with further detail on unnesting data frames before merging. — markb, Mar 27 '19 at 08:34

score 0 · Answer 2 · answered Mar 26 '19 at 11:38

0

Try:

df3 = df1.apply(lambda x: x if set(x[0]).intersection(set(df2['ID'])) else None, axis = 1)
df3 = df3.dropna().set_index('ID')
df3.reset_index(inplace = True)
final_df = pd.concat([df3, df2], axis = 1)

answered Mar 26 '19 at 11:38

Loochie

2,414
13
20

`way to concat the data frames efficiently` - can you add some timings? – jezrael Mar 26 '19 at 11:39

Concat dataframes by matching a value in a column to a list in another column

2 Answers2