“ValueError: Index contains duplicate entries, cannot reshape” error when I try to use pd.MultiIndex.arrays

Question

I have data which includes id , gender , collected time test name and Test values , Units of measurement

Test Names will include all tests that a patient taken and Value col will have its corresponding test result.

I want to analysis on only certain tests and retrieve corresponding test values from "value" col . The analysis will be on those tests and their values , so I thought it would be good idea to pivot on those test names and test values. However when I add TS col I get an error and adding any other test name in the multiindex code does not throw an error.

Steps:

 df_s.head(30).dropna()

Here in the below screenshot we can see there multiple test taken for each requisition id:

In the below code Iam only getting tests which I want to do analysis 1#

df_s2 = df_s[df_s['Test'].isin(['TOTAL TRIIODOTHYRONINE (T3)','TOTAL THYROXINE (T4)','FREE THYROID 3','FREE THYROID 4','Human Chorionic Gonadotropin (hCG)','BILRUBIN'])]

2# Resetting the index:

df_s3=df_s2.set_index(['ID', 'Name', 'Age', 'Sex', 'CT', 'RT', 'Test', 'Test_Result', 'Units']).reset_index()

3# applything multiindex

idx = pd.MultiIndex.from_arrays([df_s3['ID'], df_s3['Name'], df_s3['Age'], df_s3['Sex'], df_s3['CT'],df_s3['RT'], df_s3['Units'], df_s3['Test'],  ])
#, df_s3['Unit of Measure']
df_s5 = df_s3.set_index(idx).Test_Result.unstack(fill_value='')
df_s5.columns.name = None
df_s6= df_s5.reset_index()
df_s6.head(100)

Result:

I get this result if do not add TSH (from Test Col) Code with TSH test: Retry 1# with TSH

df_s2 = df_s[df_s['Test'].isin(['TOTAL TRIIODOTHYRONINE (T3)','TOTAL THYROXINE (T4)','THYROID STIMULATING HORMONE (TSH)','FREE THYROID 3','FREE THYROID 4','Human Chorionic Gonadotropin (hCG)','BILRUBIN'])]

df_s3=df_s2.set_index(['ID', 'Name', 'Age', 'Sex', 'CT', 'RT', 'Test', 'Test_Result', 'Units']).reset_index()

idx = pd.MultiIndex.from_arrays([df_s3['ID'], df_s3['Name'], df_s3['Age'], df_s3['Sex'], df_s3['CT'],df_s3['RT'], df_s3['Units'], df_s3['Test'],  ])
#, df_s3['Unit of Measure']
df_s5 = df_s3.set_index(idx).Test_Result.unstack(fill_value='')
df_s5.columns.name = None
df_s6= df_s5.reset_index()
df_s6.head(100)

Question1 (Retry 1# with TSH ): Please help me with the correct approach, what I understand the error is because once it convert it is not finding any unique index but not sure how to resolve it.

Question2: When I proceeded to go ahead without tsh, after conversion of test col- rows to cols, I get blank values in respective test col ( example T4 col) because 1) the person has taken the test but there is no value in the dataset(python is treating it as Null value and can be imputed/rejected - no issue 2) the patient has not taken this test but has taken atleast one other tests may be T3, hcg etc but not this test- this is considered as string '' . I want to get rid of these rows for amy analysis .. is there an approach while transforming the data to take care of so that I only want the result of the code to have T4 and its value( numeric or null). I do not want a scenario where the person has not taken test at all. OR is there a way to impute these values so I will know the person has taken T4, T3 but not Hcg , bilrubin etc?

Please advise. Long questions but I hope it this explanatory

Answer 1

Thank you very much to who ever had taken time into looking into this. I have resolved this issue by adding a new unique col before apply multiIndex

df_s2 = df_s[df_s['Test'].isin(['TOTAL TRIIODOTHYRONINE (T3)','TOTAL THYROXINE (T4)','THYROID STIMULATING HORMONE (TSH)','FREE THYROID 3','FREE THYROID 4','Human Chorionic Gonadotropin (hCG)','BILRUBIN'])]
[df_s2['ID1'] = range(1, len(df_s2.index)+1)

df_s2.set_index(['ID1'])

idx = pd.MultiIndex.from_arrays([df_s2['ID1'],[df_s2['ID'], df_s2['Name'], df_s2['Age'], df_s2['Sex'], df_s2['CT'],df_s2['RT'], df_s2['Units'], df_s2['Test'],  ])

df_s5 = df_s2.set_index(idx).Test_Result.unstack(fill_value='')
df_s5.columns.name = None
df_s6= df_s5.reset_index()
df_s6.head(100)
```