Python Pandas MultiIndexing: Duplicate rows and add differentiating information in new column

user_14890683 0
Viewed 55

I'm a python beginner trying to duplicate existing rows, while adding differentiating information in a new column. Currently, my DataFrame looks like so:

Patient  Visit
      1     V1
      1     V2
      1     V3
      2     V1
      2     V2

I'd like to add a new column Test which for V1 requires Test 1, but for V2 and V3 requires both Test 1 and Test 2:

Patient  Visit    Test
      1     V1  Test 1
      1     V2  Test 1
      1     V2  Test 2
      1     V3  Test 1
      1     V3  Test 2
      2     V1  Test 1
      2     V2  Test 1
      2     V2  Test 2

I'd then further like to add a column Sample which adds an A and B sample for each test:

Patient  Visit    Test  Sample
      1     V1  Test 1       A
      1     V1  Test 1       B
      1     V2  Test 1       A
      1     V2  Test 1       B
      1     V2  Test 2       A
      1     V2  Test 2       B
...
      2     V2  Test 2       A
      2     V2  Test 2       B

How do I duplicate the rows while adding new information in the additional columns? Thank you for your help!!

pandas python
2 Answers

You can manually create your Visits-Test-Sample dataframe, then merge with Patients dataframe:

pd.MultiIndex.from_product([['V2','V3'],['Test 1', 'Test 2'],['A', 'B']], names=['Visit', 'Test', 'Sample'])\
  .union(pd.MultiIndex.from_product([['V1'],['Test 1'],['A','B']], names=['Visit', 'Test', 'Sample']))\
  .to_frame().reset_index(drop=True)\
  .merge(df, on='Visit')\
  .sort_values('Patient')

Output:

   Visit    Test Sample  Patient
0     V1  Test 1      A        1
2     V1  Test 1      B        1
4     V2  Test 1      A        1
6     V2  Test 1      B        1
8     V2  Test 2      A        1
10    V2  Test 2      B        1
12    V3  Test 1      A        1
13    V3  Test 1      B        1
14    V3  Test 2      A        1
15    V3  Test 2      B        1
1     V1  Test 1      A        2
3     V1  Test 1      B        2
5     V2  Test 1      A        2
7     V2  Test 1      B        2
9     V2  Test 2      A        2
11    V2  Test 2      B        2
user_63615310

Trying to present this step by step using explode:

Create DF:

      t = pd.DataFrame({'Patient': [1,1,1,2,2],
             'Visit': ['V1', 'V2', 'V3', 'V1', 'V2']})

       Patient  Visit
      0    1    V1
      1    1    V2
      2    1    V3
      3    2    V1
      4    2    V2

Add Test column:

    t['Test'] = t['Visit'].apply(lambda x: ['Test 1'] if x == 'V1' else ['Test 1', 'Test 2'])

     Patient    Visit   Test
   0    1         V1    [Test 1]
   1    1         V2    [Test 1, Test 2]
   2    1         V3    [Test 1, Test 2]
   3    2         V1    [Test 1]
   4    2         V2    [Test 1, Test 2]

Use explode to get each of the tests as its own row:

       t = t.explode('Test')    # Since this cannot be done inline you need to copy this to the original DF.

       Patient  Visit   Test
       0    1   V1  Test 1
       1    1   V2  Test 1
       1    1   V2  Test 2
       2    1   V3  Test 1
       2    1   V3  Test 2
       3    2   V1  Test 1
       4    2   V2  Test 1
       4    2   V2  Test 2

Do the same for the 'Sample' column: Add 'Sample' Column:

      t['Sample'] = t['Test'].apply(lambda x: ['A', 'B'])

Explode 'Sample Column:

      t = t.explode('Sample')

Here is the final output:

         Patient    Visit   Test    Sample
       0    1   V1  Test 1  A
       0    1   V1  Test 1  B
       1    1   V2  Test 1  A
       1    1   V2  Test 1  B
       1    1   V2  Test 2  A
       1    1   V2  Test 2  B
       2    1   V3  Test 1  A
       2    1   V3  Test 1  B
       2    1   V3  Test 2  A
       2    1   V3  Test 2  B
       3    2   V1  Test 1  A
       3    2   V1  Test 1  B
       4    2   V2  Test 1  A
       4    2   V2  Test 1  B
       4    2   V2  Test 2  A
       4    2   V2  Test 2  B
user_114097410
Related