Expand two columns that do not have matching elements

user_9798985 0
Viewed 33

I have a dataframe with column A and column B having some values separated by comma. I would like to expand these two together to get a row with all other information and one element per row from A and B.

I have tried to use expand but it does not work as I do not have all my variables in column A and B equal. For 2 elements in A I don't always have 2 elements in B.

Example of dataset:

data = {'col1': [1, 2, 3, 4], 'col2': [5, 6, 7, "NaN"], 'A': ["event", "event2, NaN", "event3, event4", "event5, event6"], 'B': ["01-05-2022", "02-05-2022, 03-05-2022", "04-05-2022", ""]}
df = pd.DataFrame(data=data)
df
col1 col2 A B
1 5 event 01-05-2022
2 6 event2 02-05-2022, 03-05-2022
3 7 event3, event4 04-05-2022
4 NaN event5, event6

I would like to get something that looks like this:

data = {'col1': [1, 2, 2, 3, 3, 4, 4], 'col2': [5, 6, 6, 7, 7, "NaN", "NaN"], 'A': ["event", "event2", "NaN", "event3", "event4", "event5", "event6"], 'B': ["01-05-2022", "02-05-2022", "03-05-2022", "04-05-2022", "04-05-2022", "", ""]}
df = pd.DataFrame(data=data)
df
col1 col2 A B
1 5 event 01-05-2022
2 6 event2 02-05-2022
2 6 NaN 03-05-2022
3 7 event3 04-05-2022
3 7 event4 04-05-2022
4 NaN event5
4 NaN event6
pandas dataframe python
1 Answers

With the dataframe you provided, here is one way to do it:

df = (
    df
    .assign(
        A=df["A"].apply(lambda x: x.split(", ")),
        B=df["B"].apply(lambda x: x.split(", ")),
    )
    .explode("A")
    .explode("B")
    .pipe(
        lambda df_: df_.replace("NaN", pd.NA)
        .dropna(subset="A", how="any")
        .reset_index(drop=True)
    )
)

print(df)
# Output
   col1  col2       A           B
0     1     5   event  01-05-2022
1     2     6  event2  02-05-2022
2     2     6  event2  03-05-2022
3     3     7  event3  04-05-2022
4     3     7  event4  04-05-2022
5     4  <NA>  event5
6     4  <NA>  event6
user_112460560
Related