如何使用pandas的melt函数处理数值和误差数据

Question

我有一个宽数据集：

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) 

（我认为一定有更好的方法），但我还未想出如何有效地使用栈来获得我想要的结果。

有人知道官方解决方案吗？谢谢！

- Grant Goodyear

Answer 1

你可以分别融化每个部分的兴趣点，然后将它们拼接在一起。

a = df.loc[:, ~df.columns.str.endswith(r'relerr')].melt('P')
b = (df.loc[:, df.columns.str.contains(r'P|(?:relerr$)')]
       .melt('P')
       .drop(['P', 'variable'], axis=1))

(pd.concat([a, b], axis=1)
   .set_axis(['P', 'which', 'count', 'err'], axis=1, inplace=False))

    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

- cs95

Answer 2

你可以使用pd.wide_to_long，这对于那些需要"同时融合"的情况非常理想，只需稍微更改列名即可。

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)

#Rename columns with set_axis
d1 = df.set_axis(['P', 'Count_N', 'Err_N', 'Count_F', 'Err_F'], axis=1, inplace=False)

#Use pd.wide_to_long to reshape dataframe
pd.wide_to_long(d1, ['Count', 'Err'], 'P', 'which', sep='_', suffix='.+')

输出：

              Count     Err
P  which                   
10 N      6073.9800  0.0022
12 N      6412.9700  0.0021
4  N      4141.2400  0.0019
6  N      5009.8300  0.0019
8  N      5601.8700  0.0025
10 F        61.9730  0.0036
12 F        65.4050  0.0036
4  F        42.8202  0.0032
6  F        51.9615  0.0031
8  F        57.8129  0.0042

- Scott Boston

---

谢谢！那似乎是我正在寻找的优雅解决方案。 - Grant Goodyear