python Pandas : Aggregate rows conditional value picking

Question

我有一个这样的数据框:

df = pd.DataFrame({'dim': {0: 'A', 1: 'B', 2: 'A', 3: 'B', 4: 'A'},
                   'id': {0: 1, 1: 1, 2: 2, 3: 2, 4: 3},
                   'value1': {0: nan, 1: 1.2, 2: 2.0, 3: nan, 4: 3.0},
                   'value2': {0: 1.0, 1: 2.0, 2: nan, 3: nan, 4: nan}})

  dim  id  value1  value2
0   A   1     NaN     1.0
1   B   1     1.2     2.0
2   A   2     2.0     NaN
3   B   2     NaN     NaN
4   A   3     3.0     NaN

我现在想通过 id 聚合不同维度的值，以便满足以下条件:如果 dim == 'A' 不是 None 则从 dim == 'A' 获取值，否则从 dim == 'B' 获取值(如果它不是 None)。如果两者都为 None，则取 None。

所以结果应该是:

   id  value1  value2
0   1     1.2     1.0
1   2     2.0     NaN
2   3     3.0     NaN

我的猜测是，我需要使用某种形式的按功能分组，但我不太确定。也许有一些应用？

Answer 1

您可以使用 set_index与 unstack和 swaplevel reshape 然后combine_first :

df1 = df.set_index(['id','dim']).unstack().swaplevel(0,1,axis=1)
#alternative
#df1 = df.pivot('id','dim').swaplevel(0,1,axis=1)
print (df1)
dim      A      B      A      B
    value1 value1 value2 value2
id                             
1      NaN    1.2    1.0    2.0
2      2.0    NaN    NaN    NaN
3      3.0    NaN    NaN    NaN

df2 = df1['A'].combine_first(df1['B']).reset_index()
print (df2)
   id  value1  value2
0   1     1.2     1.0
1   2     2.0     NaN
2   3     3.0     NaN

与 xs 类似的解决方案对于选择 MultiIndex:

df1 = df.set_index(['id','dim']).unstack()
#alternative
#df1 = df.pivot('id','dim')
print (df1)
    value1      value2     
dim      A    B      A    B
id                         
1      NaN  1.2    1.0  2.0
2      2.0  NaN    NaN  NaN
3      3.0  NaN    NaN  NaN

df2 = df1.xs('A', axis=1, level=1).combine_first(df1.xs('B', axis=1, level=1)).reset_index()
print (df2)
   id  value1  value2
0   1     1.2     1.0
1   2     2.0     NaN
2   3     3.0     NaN