-1

I understand how to convert my data from wide to long and vice versa; however, where I'm struggling is in keeping the relationship between 2 columns in the conversion.

Data is in a Pandas Data Frame. I can split each column independently using .split() and .expand(). Where I'm struggling is maintaining the relationship across the columns that the first value in the string list is related to the first value in the string list of the second column, and so on.

Original Data:

ID History Relationship
123 555 - Pancreatic cancer,444 - Hypertension 1 - Mother,2 - Father
567 77 - Stroke 2 - Father

This is what I want:

ID History Relationship
123 555 - Pancreatic cancer 1 - Mother
123 444 - Hypertension 2 - Father
567 77 - Stroke 2 - Father

I've tried searching multiple terms, but can't seem to find a similar question.

wjandrea
  • 28,235
  • 9
  • 60
  • 81
luxoperon
  • 1
  • 2
  • Please [edit] to add your code. We need to know what format the data is in (Pandas DataFrame?) and what you've already tried. For reference, see [mre]. For more tips, see [ask]. – wjandrea Sep 29 '21 at 16:42
  • If you're using Pandas, this may be a duplicate: [Split (explode) pandas dataframe string entry to separate rows](/q/12680754/4518341) – wjandrea Sep 29 '21 at 16:49
  • Edited. Looks like explore will work. Thank you. – luxoperon Sep 29 '21 at 17:05
  • Please provide enough code so others can better understand or reproduce the problem. – Community Oct 07 '21 at 08:58

1 Answers1

0

From the explode post suggested above. This does seem to work. Open for more efficient options.

df= df.assign(var1=df.History.str.split(','))
df= df.assign(var2=df.Relationship.str.split(','))
df_new = df.explode(["var1", "var2"])
luxoperon
  • 1
  • 2