我有一个相当复杂的数据框,如下所示:
df = pd.DataFrame({'0': {('Total Number of End Points', '0.01um', '0hr'): 12,
('Total Number of End Points', '0.1um', '0hr'): 8,
('Total Number of End Points', 'Control', '0hr'): 4,
('Total Number of End Points', '0.01um', '24hr'): 18,
('Total Number of End Points', '0.1um', '24hr'): 12,
('Total Number of End Points', 'Control', '24hr'): 6,
('Total Vessel Length', '0.01um', '0hr'): 12,
('Total Vessel Length', '0.1um', '0hr'): 8,
('Total Vessel Length', 'Control', '0hr'): 4,
('Total Vessel Length', '0.01um', '24hr'): 18,
('Total Vessel Length', '0.1um', '24hr'): 12,
('Total Vessel Length', 'Control', '24hr'): 6},
'1': {('Total Number of End Points', '0.01um', '0hr'): 12,
('Total Number of End Points', '0.1um', '0hr'): 8,
('Total Number of End Points', 'Control', '0hr'): 4,
('Total Number of End Points', '0.01um', '24hr'): 18,
('Total Number of End Points', '0.1um', '24hr'): 12,
('Total Number of End Points', 'Control', '24hr'): 6,
('Total Vessel Length', '0.01um', '0hr'): 12,
('Total Vessel Length', '0.1um', '0hr'): 8,
('Total Vessel Length', 'Control', '0hr'): 4,
('Total Vessel Length', '0.01um', '24hr'): 18,
('Total Vessel Length', '0.1um', '24hr'): 12,
('Total Vessel Length', 'Control', '24hr'): 6},
'2': {('Total Number of End Points', '0.01um', '0hr'): 12,
('Total Number of End Points', '0.1um', '0hr'): 8,
('Total Number of End Points', 'Control', '0hr'): 4,
('Total Number of End Points', '0.01um', '24hr'): 18,
('Total Number of End Points', '0.1um', '24hr'): 12,
('Total Number of End Points', 'Control', '24hr'): 6,
('Total Vessel Length', '0.01um', '0hr'): 12,
('Total Vessel Length', '0.1um', '0hr'): 8,
('Total Vessel Length', 'Control', '0hr'): 4,
('Total Vessel Length', '0.01um', '24hr'): 18,
('Total Vessel Length', '0.1um', '24hr'): 12,
('Total Vessel Length', 'Control', '24hr'): 6}})
print(df)
0 1 2
Total Number of End Points 0.01um 0hr 12 12 12
24hr 18 18 18
0.1um 0hr 8 8 8
24hr 12 12 12
Control 0hr 4 4 4
24hr 6 6 6
Total Vessel Length 0.01um 0hr 12 12 12
24hr 18 18 18
0.1um 0hr 8 8 8
24hr 12 12 12
Control 0hr 4 4 4
24hr 6 6 6
我试图将每个值除以相应控制级别中列的平均值。我尝试了以下但没有用。
df2 = df.divide(df.xs('Control', level=1).mean(axis=1), axis='index')
我对 python 和 pandas 还很陌生,所以我倾向于用 MS Excel 术语来思考这个问题。
如果在 Excel 中,A1 的公式(“终点总数”、“0.01um”、“0hr”、0)看起来是:
=A1/AVERAGE($A$5:$C$5)
B1 ('Total Number of End Points', '0.01um', '0hr', 1) 将是:
=B1/AVERAGE($A$5:$C$5)
和 A2 ('Total Number of End Points', '0.01um', '24hr', 0) 将是
=A1/AVERAGE($A$6:$C$6)
这个例子的预期结果是:
0 1 2
Total Number of End Points 0.01um 0hr 3 3 3
24hr 3 3 3
0.1um 0hr 2 2 2
24hr 2 2 2
Control 0hr 1 1 1
24hr 1 1 1
Total Vessel Length 0.01um 0hr 3 3 3
24hr 3 3 3
0.1um 0hr 2 2 2
24hr 2 2 2
Control 0hr 1 1 1
24hr 1 1 1
注意:真实数据中有很多索引和列。
请您参考如下方法:
将 Control 值放在它们自己的列中会有所帮助。您可以使用 unstack 来做到这一点:
df.index.names = ['field', 'type', 'time']
df2 = df.unstack(['type']).swaplevel(0, 1, axis=1)
# type 0.01um 0.1um Control 0.01um 0.1um Control \
# 0 0 0 1 1 1
# field time
# Total Number of End Points 0hr 12 8 4 12 8 4
# 24hr 18 12 6 18 12 6
# Total Vessel Length 0hr 12 8 4 12 8 4
# 24hr 18 12 6 18 12 6
# type 0.01um 0.1um Control
# 2 2 2
# field time
# Total Number of End Points 0hr 12 8 4
# 24hr 18 12 6
# Total Vessel Length 0hr 12 8 4
# 24hr 18 12 6
现在求出每个对照的平均值:
ave = df2['Control'].mean(axis=1)
# field time
# Total Number of End Points 0hr 4
# 24hr 6
# Total Vessel Length 0hr 4
# 24hr 6
# dtype: float64
如您所料,您可以使用 df2.divide 来计算所需的结果。请务必使用 axis=0 告诉 Pandas 根据行索引匹配值(在 df2 和 ave 中)。
result = df2.divide(ave, axis=0)
# type 0.01um 0.1um Control 0.01um 0.1um Control \
# 0 0 0 1 1 1
# field time
# Total Number of End Points 0hr 3 2 1 3 2 1
# 24hr 3 2 1 3 2 1
# Total Vessel Length 0hr 3 2 1 3 2 1
# 24hr 3 2 1 3 2 1
# type 0.01um 0.1um Control
# 2 2 2
# field time
# Total Number of End Points 0hr 3 2 1
# 24hr 3 2 1
# Total Vessel Length 0hr 3 2 1
# 24hr 3 2 1
本质上有您所追求的值(value)观。但是,如果您想重新排列 DataFrame 使其看起来与您发布的完全一样,那么:
result = result.stack(['type'])
result = result.reorder_levels(['field','type','time'], axis=0)
result = result.reindex(df.index)
产量
0 1 2
field type time
Total Number of End Points 0.01um 0hr 3 3 3
24hr 3 3 3
0.1um 0hr 2 2 2
24hr 2 2 2
Control 0hr 1 1 1
24hr 1 1 1
Total Vessel Length 0.01um 0hr 3 3 3
24hr 3 3 3
0.1um 0hr 2 2 2
24hr 2 2 2
Control 0hr 1 1 1
24hr 1 1 1
综合起来:
df.index.names = ['field', 'type', 'time']
df2 = df.unstack(['type']).swaplevel(0, 1, axis=1)
ave = df2['Control'].mean(axis=1)
result = df2.divide(ave, axis=0)
result = result.stack(['type'])
result = result.reorder_levels(['field','type','time'], axis=0)
result = result.reindex(df.index)
