从多个列中查找最接近的值,然后添加到Python中的新列 [英] Find nearest value from multiple columns and add to a new column in Python
问题描述
我有以下数据框:
import pandas as pd
import numpy as np
data = {
"index": [1, 2, 3, 4, 5],
"A": [11, 17, 5, 9, 10],
"B": [8, 6, 16, 17, 9],
"C": [10, 17, 12, 13, 15],
"target": [12, 13, 8, 6, 12]
}
df = pd.DataFrame.from_dict(data)
print(df)
我想在A,B和C列中找到最接近列目标的值,并将这些值放入列结果中.据我所知,我需要使用abs()和argmin()函数. 这是我期望的输出:
I would like to find nearest values for column target in column A, B and C, and put those values into column result. As far as I know, I need to use abs() and argmin() function. Here is the output I expected:
index A B C target result
0 1 11 8 10 12 11
1 2 17 6 17 13 17
2 3 5 16 12 8 5
3 4 9 17 13 6 9
4 5 10 9 15 12 10
这里是解决方案,它链接了我从stackoverflow中发现的内容,这可能会有所帮助:
Here is the solution and links what i have found from stackoverflow which may help:
(df.assign(closest=df.apply(lambda x: x.abs().argmin(), axis='columns'))
.apply(lambda x: x[x['target']], axis='columns'))
在列中标识最接近的值每个过滤器都使用熊猫 https://codereview.stackexchange.com/questions/204549/lookup-closest-熊猫中数据框的值
推荐答案
从其他列中减去目标",使用idxmin
获得具有最小差异的列,后跟一个lookup
:
Subtract "target" from the other columns, use idxmin
to get the column of the minimum difference, followed by a lookup
:
idx = df.drop(['index', 'target'], 1).sub(df.target, axis=0).abs().idxmin(1)
df['result'] = df.lookup(df.index, idx)
df
index A B C target result
0 1 11 8 10 12 11
1 2 17 6 17 13 17
2 3 5 16 12 8 5
3 4 9 17 13 6 9
4 5 10 9 15 12 10
处理字符串列和NaN的常规解决方案(以及您将目标中的NaN值替换为"v1"中的值的要求):
General solution handling string columns and NaNs (along with your requirement of replacing NaN values in target with value in "v1"):
df2 = df.select_dtypes(include=[np.number])
idx = df2.drop(['index', 'target'], 1).sub(df2.target, axis=0).abs().idxmin(1)
df['result'] = df2.lookup(df2.index, idx.fillna('v1'))
您还可以通过使用df.columns.get_indexer
获取整数索引来索引基础的NumPy数组.
You can also index into the underlying NumPy array by getting integer indices using df.columns.get_indexer
.
# idx = df[['A', 'B', 'C']].sub(df.target, axis=0).abs().idxmin(1)
idx = df.drop(['index', 'target'], 1).sub(df.target, axis=0).abs().idxmin(1)
# df['result'] = df.values[np.arange(len(df)), df.columns.get_indexer(idx)]
df['result'] = df.values[df.index, df.columns.get_indexer(idx)]
df
index A B C target result
0 1 11 8 10 12 11
1 2 17 6 17 13 17
2 3 5 16 12 8 5
3 4 9 17 13 6 9
4 5 10 9 15 12 10
这篇关于从多个列中查找最接近的值,然后添加到Python中的新列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!