I have two dataframes look like this (real are bigger):

DF1:

DF2:

I have to merge these two dataframes with alliances and company columns. If it is company in alliance I have to add this information to the row. (There is /n delimiter between companies in DF1)
The result should be like this:

I need to duplicate alliances name for every company in this. I tried to separate companies in "alliances name" and make another column with lists of companies in every cell, but "isin" didn't work good with it and I couldn't megre dataframes with duplicates. Thanks in advance for your help!

CodePudding user response：

1. Create a separate column (called "Company") with individual company names using split and explode.
2. merge the two DataFrames on the "Company" column.

df1["Company"] = df1["Alliances_names"].str.split("/n")
df1 = df1.explode("Company")
output = df1.merge(df2, on="Company", how="left")

>>> output
                 Alliances_names  Value1     Company  Number1 Number2
0  cgc inc/nshow ltd/noracle inc   500.0     cgc inc   4565.0       n
1  cgc inc/nshow ltd/noracle inc   500.0    show ltd      1.0       n
2  cgc inc/nshow ltd/noracle inc   500.0  oracle inc      NaN     NaN
3                 steam/nsoap jv     NaN       steam     15.0       y
4                 steam/nsoap jv     NaN     soap jv   2000.0       n
5                      saints bd     8.0   saints bd      NaN     NaN
6  watrloo jv/ncgc inc/nflow inc    19.0  watrloo jv   6756.0       n
7  watrloo jv/ncgc inc/nflow inc    19.0     cgc inc   4565.0       n
8  watrloo jv/ncgc inc/nflow inc    19.0    flow inc   1111.0       y