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

Question

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!!

question from:https://stackoverflow.com/questions/65644664/python-pandas-multiindexing-duplicate-rows-and-add-differentiating-information

Answer 1

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