Vfrdtyky

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 

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