Python Pandas:DataFrame作为查找表 [英] Python Pandas: DataFrame as a Lookup Table
问题描述
这是一个预处理的DataFrame,其列表示特定列的频率和成功值。例如:列 A
与 FREQ_A
和 SUCCESS_A
相关联
This is a preprocessed DataFrame, with columns representing frequency and success values for specific column. For example: Column A
is associated with FREQ_A
and SUCCESS_A
respectively.
A B Gold FREQ_A SUCCESS_A FREQ_B SUCCESS_B
0 1 B 0 1 0.00 1 0.00
1 2 A 1 1 0.01 1 0.01
我有另一个DataFrame,如下所示:
I have another DataFrame, like the following:
A B
0 1 A
1 2 B
现在我想添加关联的频率和成功列( FREQ _ *
和 SUCCESS _ *
, *:{A,B}
),从预处理的DataFrame中查找值。一个重要的观察结果是,预处理的DataFrame具有相同的(非频率/成功)列集,但没有完整的键集。 (请参见 2
, A:3
和 B:C
不在预处理框架中)
Now I want to add the associated frequency and success columns (FREQ_*
and SUCCESS_*
, * : {A,B}
), looking up the values from the preprocessed DataFrame. An important observation is that the preprocessed DataFrame has an identical set of (non freq/success) columns, but not a complete set of keys. (See row 2
, A:3
and B:C
are not located in the preprocessed frame)
例如:
数据框的第一行包含值 A = 1,B = A
,因此:
The first row in the dataframe, has values A = 1, B = A
, so:
FREQ_A
将采用 FREQ_A
的原始数据框的值,其中 A == 1
FREQ_A
will take the value of the original dataframe of FREQ_A
where A == 1
和
FREQ_B
将采用原始数据帧的值 FREQ_B
其中 B == A
FREQ_B
will take the value of the original dataframe of FREQ_B
where B == A
理想的输出
A B FREQ_A SUCCESS_A FREQ_B SUCCESS_B
0 1 A 1 0.00 1 0.01
1 2 B 1 0.01 1 0.00
测试用例
A B
0 1 A
1 2 B
2 1 C
3 4 A
推荐答案
df1 = pd.DataFrame({
'A': [1, 2],
'B': ['B', 'A'],
'FREQ_A': [1, 1],
'FREQ_B': [1, 1],
'Gold': [0, 1],
'SUCCESS_A': [0.0, 0.01],
'SUCCESS_B': [0.0, 0.01]})
df2 = pd.DataFrame({'A': [1, 2], 'B': ['A', 'B']})
result = (df2
.merge(df1[['A', 'FREQ_A', 'SUCCESS_A']], on='A')
.merge(df1[['B', 'FREQ_B', 'SUCCESS_B']], on='B'))
>>> result
A B FREQ_A SUCCESS_A FREQ_B SUCCESS_B
0 1 A 1 0.00 1 0.01
1 2 B 1 0.01 1 0.00
编辑
对于任意数据框:
result = pd.concat(
[df2, pd.concat([df2[[col]].merge(
df1[[col, 'FREQ_' + str(col), 'SUCCESS_' + str(col)]],
on=col, how='left').iloc[:, 1:]
for col in df2], axis=1)],
axis=1)
这篇关于Python Pandas:DataFrame作为查找表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!