Combine two excel files with different information for the same SampleID to yield one excel file with one row with ALL info for each SampleID

user_17702336 0
Viewed 22

I have two excel files that contain information for the same SampleIDs -- one file is location information, the other is clinical information. I want to combine the two files into a third, such that each Sample row contains its respective information from each of the original files.

File 1

SampleID Location
101 Fridge
102 Freezer
  • File 2
SampleID Diagnosis
101 None
102 Tumor

--> File 3

SampleID Diagnosis Location
101 None Fridge
102 Tumor Freezer

I've tried this:

import pandas as pd
import glob

df1 = pd.read_excel('Sample Storage.xlsx')
print(df1)

df2 = pd.read_excel('Sample log.xlsx')
print(df2)

df3 = pd.concat([df1,df2], ignore_index=False)
print(df3)

df3.to_excel('CombinedSamples.xlsx')

But get an excel file that has two rows for the same SampleID - first row contains location, second row contains diagnosis.

Thoughts on how to combine the rows by SampleID and keep all the data?

pandas dataframe excel python
1 Answers

Given two dataframes as shown below:

df1 = 

SampleId    Location
0   101 Fridge
1   102 Freezer  

df2 = 

SampleId    Diagnosis
0   101 None
1   102 Tumor  

df3 = df1.merge(df2)  

Yields:
    SampleId    Location    Diagnosis
0   101 Fridge  None
1   102 Freezer Tumor
user_142490870
Related