在 Pandas DataFrame 中转换列值的最有效方法
问题描述
我有一个 pd.DataFrame,看起来像:
I have a a pd.DataFrame that looks like:
我想对值创建一个截止值以将它们推入二进制数字,在这种情况下我的截止值是 0.85
.我希望生成的数据框看起来像:
I want to create a cutoff on the values to push them into binary digits, my cutoff in this case is 0.85
. I want the resulting dataframe to look like:
我为此编写的脚本很容易理解,但对于大型数据集,它效率低下.我确信 Pandas 有一些方法可以处理这些类型的转换.
The script I wrote to do this is easy to understand but for large datasets it is inefficient. I'm sure Pandas has some way of taking care of these types of transformations.
有人知道使用阈值将浮点数列转换为整数列的有效方法吗?
我做这种事情的方式极其天真:
My extremely naive way of doing such a thing:
DF_test = pd.DataFrame(np.array([list("abcde"),list("pqrst"),[0.12,0.23,0.93,0.86,0.33]]).T,columns=["c1","c2","value"])
DF_want = pd.DataFrame(np.array([list("abcde"),list("pqrst"),[0,0,1,1,0]]).T,columns=["c1","c2","value"])
threshold = 0.85
#Empty dataframe to append rows
DF_naive = pd.DataFrame()
for i in range(DF_test.shape[0]):
#Get first 2 columns
first2cols = list(DF_test.ix[i][:-1])
#Check if value is greater than threshold
binary_value = [int((bool(float(DF_test.ix[i][-1]) > threshold)))]
#Create series object
SR_row = pd.Series( first2cols + binary_value,name=i)
#Add to empty dataframe container
DF_naive = DF_naive.append(SR_row)
#Relabel columns
DF_naive.columns = DF_test.columns
DF_naive.head()
#the sample DF_want
解决方案
您可以使用 np.where
根据布尔条件设置所需的值:
You can use np.where
to set your desired value based on a boolean condition:
In [18]:
DF_test['value'] = np.where(DF_test['value'] > threshold, 1,0)
DF_test
Out[18]:
c1 c2 value
0 a p 0
1 b q 0
2 c r 1
3 d s 1
4 e t 0
请注意,由于您的数据是一个异构 np 数组,因此值"列包含字符串而不是浮点数:
Note that because your data is a heterogenous np array the 'value' column contains strings rather than floats:
In [58]:
DF_test.iloc[0]['value']
Out[58]:
'0.12'
因此,您需要先将 dtype
转换为 float
:DF_test['value'] = DF_test['value'].astype(float)
So you'll need to convert the dtype
to float
first: DF_test['value'] = DF_test['value'].astype(float)
您可以比较时间:
In [16]:
%timeit np.where(DF_test['value'] > threshold, 1,0)
1000 loops, best of 3: 297 µs per loop
In [17]:
%%timeit
DF_naive = pd.DataFrame()
for i in range(DF_test.shape[0]):
#Get first 2 columns
first2cols = list(DF_test.ix[i][:-1])
#Check if value is greater than threshold
binary_value = [int((bool(float(DF_test.ix[i][-1]) > threshold)))]
#Create series object
SR_row = pd.Series( first2cols + binary_value,name=i)
#Add to empty dataframe container
DF_naive = DF_naive.append(SR_row)
10 loops, best of 3: 39.3 ms per loop
np.where
版本快了 100 倍以上,诚然您的代码做了很多不必要的事情,但您明白了
the np.where
version is over 100x faster, admittedly your code is doing a lot of unnecessary stuff but you get the point
相关文章