Odtnhj

I am attempting to concatenate multiple files together and output to an excel file. My plan was to read the data into a dataframe, perform a few calculations, then write the data to an excel sheet. I would like to add a second label to my dataframe that indicates the file from which it came. I believe that multiindexing is the way to go but I am unsure of how to add.

example of current dataframe:

 readout readout
0 1.098 4.514
1 3.185 2.124 
2 0.938 0.369
3 5.283 7.840

example of intended dataframe:

 file_1 file_2
 readout readout
0 1.098 4.514
1 3.185 2.124 
2 0.938 0.369
3 5.283 7.840

Here is the code I am currently using.

# import excel sheet into dataframe
well_reads = pd.read_excel('File.xls', header=0)

# pull positive control and negative control samples into new dataframe
positive_control = well_reads[well_reads['Well'].str.contains('01')]
negative_control = well_reads[well_reads['Well'].str.contains('12')]

# drop postive control and negative control rows from initial dataframe
positive_control_wells = well_reads[well_reads['Well'].str.contains('01')]
index = positive_control_wells.index
well_reads = well_reads.drop(well_reads.index[index])
well_reads = well_reads.reset_index(drop=True)

negative_control_wells = well_reads[well_reads['Well'].str.contains('12')]
index = negative_control_wells.index
well_reads = well_reads.drop(well_reads.index[index])
well_reads = well_reads.reset_index(drop=True)

# Create data frame just containing reads and well id
neutralization_data = well_reads[['CPS (CPS)', 'Well']]

# set index to well id
neutralization_data = neutralization_data.set_index(['Well'])

# identify the geometric mean of the plate
geomean = scipy.stats.gmean(well_reads['CPS (CPS)'])

# identify the IC50 of the plate
IC_50 = geomean/2

# identify the IC80 of the plate
IC_80 = geomean * 0.2


# create a pandas excel writer using xlsxwriter as the engine
writer = pd.ExcelWriter('neutralization data.xlsx', engine='xlsxwriter')

# convert the dataframe to an xlsxwriter excel object
neutralization_data.to_excel(writer, sheet_name='Neutralization Data', startrow=1)

# close the pandas excel writer and output the file
writer.save()

Like you said, adding the multi-index columns will solve your issue before you write the output:

df=pd.DataFrame(0:[1.098,3.185,0.938, 5.283],1:[4.514,2.124,0.369, 7.840])
df.columns=pd.MultiIndex.from_tuples([('file1','readout'),('file2','readout')])

gives

 file1 file2
 readout readout
0 1.098 4.514
1 3.185 2.124
2 0.938 0.369
3 5.283 7.840