我有一个宽数据集:
import pandas as pd
from io import StringIO
testcsv = """P,N,N_relerr,F,F_relerr
10,6073.98,0.0022,61.973,0.0036
12,6412.97,0.0021,65.405,0.0036
4,4141.24,0.0019,42.8202,0.0032
6,5009.83,0.0019,51.9615,0.0031
8,5601.87,0.0025,57.8129,0.0042"""
csvfile = StringIO(testcsv)
df = pd.read_csv(csvfile)
P N N_relerr F F_relerr
0 10 6073.98 0.0022 61.9730 0.0036
1 12 6412.97 0.0021 65.4050 0.0036
2 4 4141.24 0.0019 42.8202 0.0032
3 6 5009.83 0.0019 51.9615 0.0031
4 8 5601.87 0.0025 57.8129 0.0042
我希望将其转换为一个具有“计数”(N和F列)和相关误差(N_relerr和F_relerr)的长数据集:
P which count err
0 10 N 6073.9800 0.0022
1 12 N 6412.9700 0.0021
2 4 N 4141.2400 0.0019
3 6 N 5009.8300 0.0019
4 8 N 5601.8700 0.0025
5 10 F 61.9730 0.0036
6 12 F 65.4050 0.0036
7 4 F 42.8202 0.0032
8 6 F 51.9615 0.0031
9 8 F 57.8129 0.0042
因为这是使用plotnine绘制带有“N”和“F”计数区分的误差线所需的格式。我目前的解决方案非常丑陋:
dflong = (df[['P', 'N', 'F']]
.melt(id_vars=['P'],
var_name='which',
value_name='count'))
dferr = (df[['P', 'N_relerr', 'F_relerr']]
.melt(id_vars=['P'],
var_name='which',
value_name='count_relerr'))
dflong['err'] = dferr['count_relerr'].copy()
我猜测,使用多级索引列和堆叠(stack)的方法可以优雅地解决这个问题,起点是具有以下形式的数据集:
N F
P counts relerr counts relerr
0 10 6073.98 0.0022 61.9730 0.0036
1 12 6412.97 0.0021 65.4050 0.0036
2 4 4141.24 0.0019 42.8202 0.0032
3 6 5009.83 0.0019 51.9615 0.0031
4 8 5601.87 0.0025 57.8129 0.0042
我可以从以下内容创建该数据框:
cols = {'P': 'P',
'N': ('N', 'counts'), 'N_relerr': ('N', "relerr"),
'F': ('F', 'counts'), 'F_relerr': ('F', 'relerr')}
nested_df = df.rename(columns=cols)
nested_df.columns = [c if isinstance(c, tuple)
else ('', c) for c in nested_df.columns]
nested_df.columns = pd.MultiIndex.from_tuples(nested_df.columns)
(我认为一定有更好的方法),但我还未想出如何有效地使用栈来获得我想要的结果。
有人知道官方解决方案吗?谢谢!