How to join Panda DataFrames based on List values in a column

Question

There are two Pandas DataFrame

df_A = pd.DataFrame([['r1', ['a','b']], ['r2',['aabb','b']], ['r3', ['xyz']]], columns=['col1', 'col2'])

 col1     col2
 r1     [a, b]
 r2  [aabb, b]
 r3      [xyz]


df_B = pd.DataFrame([['a', 10], ['b',2]], columns=['C1', 'C2'])

  C1  C2
   a  10
   b   2

I want to join both dataframes such as df_C is

col1 C1  C2
r1   a   10
r1   b    2
r2   aabb 0
r2   b    2
r3   xyz  0 

Answer 1

You need:

df = pd.DataFrame([['r1', ['a','b']], ['r2',['aabb','b']], ['r3', ['xyz']]], columns=['col1', 'col2'])

df= pd.DataFrame({'col1':np.repeat(df.col1.values, df.col2.str.len()),
                        'C1':np.concatenate(df.col2.values)})

df_B = pd.DataFrame([['a', 10], ['b',2]], columns=['C1', 'C2'])
df_B = dict(zip(df_B.C1, df_B.C2))
# {'a': 10, 'b': 2}

df['C2']= df['C1'].apply(lambda x: df_B[x] if x in df_B.keys() else 0)

print(df)

Output:

    col1  C1    C2
0   r1    a     10
1   r1    b     2
2   r2    aabb  0
3   r2    b     2
4   r3    xyz   0

Edit

The below code will give you the length of the list in each row.

print(df.col2.str.len())

# 0    2
# 1    2
# 2    1

np.repeat will repeat the values from col1 based length obtained using above. eg. r1,r2 will repeat twice.

print(np.repeat(df.col1.values, df.col2.str.len())
# ['r1' 'r1' 'r2' 'r2' 'r3']

Using np.concatenate on col2.values will result in plain 1D List

print(np.concatenate(df.col2.values))
# ['a' 'b' 'aabb' 'b' 'xyz']