Pandas 组合来自多列的信息，其中值取决于同一行中的值

Question

我正在尝试将几列中的信息压缩为一列。数据框有一个 'step'、'tx' 和一个 'rx' 列。这些后面是显示各方之间传输的累积值的列，例如“A_to_B”等。但我想将信息压缩到单个列，显示给定传输的给定步骤的适当累积值。因此，组合列中的值取决于传输中涉及的各方，即。该值取决于同一行中的其他值。下面的代码就是这样做的:

u = pd.DataFrame()
u["step"] = pd.Series([1,2,3,4,5,6])
u["tx"] = pd.Series(["A","A","A","A","A","B"])
u["rx"] = pd.Series(["B","C","B","C","C","A"])
u["A_to_B"] = pd.Series([15,15,30,30,30,30])
u["A_to_C"] = pd.Series([0,20,20,40,50,50])
u["B_to_A"] = pd.Series([0,0,0,0,0,100])

print(u)

def compact(x):
  rx = x["rx"]
  tx = x["tx"]
  x["combo"] = x[tx + "_to_" + rx]
  return x

u = u.apply(lambda x: compact(x), axis=1)

print(u)

它产生以下结果:

   step tx rx  A_to_B  A_to_C  B_to_A
0     1  A  B      15       0       0
1     2  A  C      15      20       0
2     3  A  B      30      20       0
3     4  A  C      30      40       0
4     5  A  C      30      50       0
5     6  B  A      30      50     100
   step tx rx  A_to_B  A_to_C  B_to_A  combo
0     1  A  B      15       0       0     15
1     2  A  C      15      20       0     20
2     3  A  B      30      20       0     30
3     4  A  C      30      40       0     40
4     5  A  C      30      50       0     50
5     6  B  A      30      50     100    100

问题是这在很多行上运行非常慢。是否有更高效/有效的方法来执行此操作？

Answer 1

使用 df.lookup 这里:

s=u['tx']+'_to_'+u['rx']
u['combo']=u.lookup(s.index,s)

同样的事情使用函数(如果你需要):

def compact(x):
    s=x['tx']+'_to_'+x['rx']
    return x.assign(combo=x.lookup(s.index,s))
compact(u)

   step tx rx  A_to_B  A_to_C  B_to_A  combo
0     1  A  B      15       0       0     15
1     2  A  C      15      20       0     20
2     3  A  B      30      20       0     30
3     4  A  C      30      40       0     40
4     5  A  C      30      50       0     50
5     6  B  A      30      50     100    100

表现:

u = pd.concat([u]*2000,ignore_index=True)
%%timeit
s=u['tx']+'_to_'+u['rx']
u.lookup(s.index,s)
#16.6 ms ± 754 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
%%timeit
u.assign(lookup=u['tx'].str.cat(u['rx'], sep='_to_')).apply(lambda x: x[x['lookup']],1)
#613 ms ± 79.2 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
#lookup is faster than apply