How to have a value in a dataframe repeat so that the values in a new column are mapped to it

Question

I have three lists for a dataframe. I am trying to map the multiple values in one list to one value in another list to then have the value repeat in the dataframe.

Here is my list and expected out:

ID_lst = [1234, 1235, 1236, ...]

Reason_lst = [[Happiness, Sadness, Depression, Love], [Anger, Grumpy], [Grieving, Sarcastic, Love], ....]

TestDate_lst = [2022-03-24, 2022-07-25, 2022-08-26, ....]

Expected Output in Dataframe:


ID         Reason_lst       TestDate
1234       Happiness        2022-03-24
1234       Sadness          2022-03-24
1234       Depression       2022-03-24
1234       Love             2022-03-24
1235       Anger            2022-07-25
1235       Grumpy           2022-07-25
1236       Grieving         2022-08-26
1236       Sarcastic        2022-08-26
1236       Love             2022-08-26

I am imagining that I can use the map function to do this but haven't figured out how to wrap my head around this one.

score 1 · Accepted Answer · answered Dec 27 '22 at 17:57

Use pd.DataFrame.explode:

import pandas as pd

ID_lst = [1234, 1235, 1236]
Reason_lst = [["Happiness", "Sadness", "Depression", "Love"], ["Anger", "Grumpy"], ["Grieving", "Sarcastic", "Love"]]
TestDate_lst = ["2022-03-24", "2022-07-25", "2022-08-26"]

df = pd.DataFrame({"ID": ID_lst, "Reason_lst": Reason_lst, "TestDate": TestDate_lst})
out = df.explode("Reason_lst")

output:

     ID  Reason_lst    TestDate
0  1234   Happiness  2022-03-24
0  1234     Sadness  2022-03-24
0  1234  Depression  2022-03-24
0  1234        Love  2022-03-24
1  1235       Anger  2022-07-25
1  1235      Grumpy  2022-07-25
2  1236    Grieving  2022-08-26
2  1236   Sarcastic  2022-08-26
2  1236        Love  2022-08-26

How to have a value in a dataframe repeat so that the values in a new column are mapped to it

1 Answers1