[SOLVED] How to explode Python Pandas Dataframe and merge strings from other dataframe?

How to explode Python Pandas Dataframe and merge strings from other dataframe?

Dataframe1 has a lot of rows and columns of data. One column is Text. Certain rows in Text column have strings and some strings include within the strings this {ExplodeEList2}

How to explode (expand) those specific rows of Dataframe1 and replace {ExplodeEList2} in each string with each name contained in the separate dataframe EList2['Name']? Thank you! I've been banging my head against my keyboard all day trying to solve this.

Dataframe1:

Text
Unrelated data
Random sample text {ExplodeElist2} and more random sample text.
Other unrelated data

EList2:

Name
Jack
Jon
Sally

How do I generate this in Dataframe1:

Text
Unrelated data
Random sample text Jack and more random sample text.
Random sample text Jon and more random sample text.
Random sample text Sally and more random sample text.
Other unrelated data

Solution

You can use apply to process all the Text values in DataFrame1 which contain the string ExplodeElist2, replacing the string with a list of replaced values. You can then explode that list:

mask = DataFrame1['Text'].str.contains('{ExplodeElist2}')
DataFrame1.loc[mask, 'Text'] = DataFrame1.loc[mask, 'Text'].apply(lambda s:[s.replace('{ExplodeElist2}', n) for n in Elist2['Name']])
DataFrame1 = DataFrame1.explode('Text').reset_index(drop=True)

Output (for your sample data):

                                                Text
0                                     Unrelated data
1  Random sample text Jack and more random sample...
2  Random sample text Jon and more random sample ...
3  Random sample text Sally and more random sampl...
4                               Other unrelated data