在相关列中保留值时熔化

Question

我有这样一个数据帧：

           POLY_KEY_I  SP1_Percent  SP2_Percent SP1_LOWER SP2_LOWER
0     FS01080100SM001         12.5         12.5    POAPRA    TOXRYD
1     FS01080100SM001          0.0          0.0       NaN       NaN
2     FS01080100SM001          2.5          2.5    TOXRYD    SYMOCC
3     FS01080100SM001         20.0         20.0    EUPESU    POAPRA
4     FS01080100SM001         12.5         12.5    BOUGRA    KOEPYR
5     FS01080100SM002         22.5         22.5    POAPRA    EUPESU
6     FS01080100SM002         12.5          0.0    POAPRA       NaN
7     FS01080100SM002         15.0         15.0    POAPRA    KOEPYR

我想基于POLY_KEY_I融化SP1_Lower和SP2_Lower，但我想保留相关的SP1_Percent和SP2_Percent。我可以用这个融化它：

df=pd.melt(df, id_vars='POLY_KEY_I', value_vars=['SP1_LOWER', 'SP2_LOWER'], value_name='SP')

但我希望我的输出是：

         POLY_KEY_I variable      SP   Percent
0   FS01080100SM001      SP1  POAPRA   12.5
1   FS01080100SM001      SP1     NaN   0
2   FS01080100SM001      SP1  TOXRYD   2.5
3   FS01080100SM001      SP1  EUPESU   20.0
4   FS01080100SM001      SP1  BOUGRA   12.5
5   FS01080100SM002      SP1  POAPRA   22.5
6   FS01080100SM002      SP1  POAPRA   12.5
7   FS01080100SM002      SP1  POAPRA   15.0
8   FS01080100SM001      SP2  TOXRYD   12.5
9   FS01080100SM001      SP2     NaN   0
10  FS01080100SM001      SP2  SYMOCC   2.5
11  FS01080100SM001      SP2  POAPRA   20.0
12  FS01080100SM001      SP2  KOEPYR   12.5
13  FS01080100SM002      SP2  EUPESU   22.5
14  FS01080100SM002      SP2     NaN   0
15  FS01080100SM002      SP2  KOEPYR   15.0

Answer 1

您可以创建每个子节的数据帧，然后将它们连接在一起：

>>> pd.concat([pd.DataFrame(df.loc[:, ['POLY_KEY_I', 'SP1_LOWER', 'SP1_Percent']].values, 
                            columns=['POLY_KEY_I', 'SP_LOWER', 'SP_Percent']), 
               pd.DataFrame(df.loc[:, ['POLY_KEY_I', 'SP2_LOWER', 'SP2_Percent']].values, 
                            columns=['POLY_KEY_I', 'SP_LOWER', 'SP_Percent'])], 
              ignore_index=True).sort(['POLY_KEY_I', 'SP_LOWER'])

         POLY_KEY_I SP_LOWER SP_Percent
4   FS01080100SM001   BOUGRA       12.5
3   FS01080100SM001   EUPESU         20
12  FS01080100SM001   KOEPYR       12.5
0   FS01080100SM001   POAPRA       12.5
11  FS01080100SM001   POAPRA         20
10  FS01080100SM001   SYMOCC        2.5
2   FS01080100SM001   TOXRYD        2.5
8   FS01080100SM001   TOXRYD       12.5
1   FS01080100SM001      NaN          0
9   FS01080100SM001      NaN          0
13  FS01080100SM002   EUPESU       22.5
15  FS01080100SM002   KOEPYR         15
5   FS01080100SM002   POAPRA       22.5
6   FS01080100SM002   POAPRA       12.5
7   FS01080100SM002   POAPRA         15
14  FS01080100SM002      NaN          0