python - 向列添加多索引
问题描述
我正在尝试将多个文件连接在一起并输出到一个 excel 文件。我的计划是将数据读入数据框中,执行一些计算,然后将数据写入 Excel 工作表。我想在我的数据框中添加第二个标签,指示它来自哪个文件。我相信多索引是要走的路,但我不确定如何添加。
当前数据框的示例:
readout readout
0 1.098 4.514
1 3.185 2.124
2 0.938 0.369
3 5.283 7.840
预期数据框示例:
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
这是我目前正在使用的代码。
# 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()
解决方案
就像您说的那样,添加多索引列将在您编写输出之前解决您的问题:
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')])
给
file1 file2
readout readout
0 1.098 4.514
1 3.185 2.124
2 0.938 0.369
3 5.283 7.840
推荐阅读
- javascript - 如何在 VueJS 中处理(或忽略)空属性?
- javascript - 请求被 Chrome66 上的 pause() 调用中断
- c# - 如何优雅地从定时器事件中捕获异常?
- c++ - 如何为具有另一个类的对象的类编写复制构造函数
- php - 即使使用正确的凭据,PHP ftp_login 也会失败
- python-3.x - 字典列表上的数学运算(python 3.6)
- apache-flink - 在 flink 中使用翻转 Windows 时,我可以为每个键设置不同的时区偏移量吗?
- regex - 带有超链接 ActiveDocument.Range 和 Format 的正则表达式
- laravel - laravel 5.6 上的 cartalyst/转换器
- excel - Excel 2016 - QAT 和宏