背景和数据概况:
我有一个 DataFrame df有几列。对于这件事,我只是切出感兴趣的列并将它们保存在 x 中。 :
df是一个巨大的数据框,我从中切片数据如下:
In [29]: x = df[['date', 'amount', 'price']][:25]
就像信息一样 x看起来像,看这个:
In [30]: x
Out[28]:
date amount price
0 2000-11-01 3 57
1 2000-11-01 2 48
2 2000-11-01 1 135
3 2000-11-01 1 24
4 2000-11-01 2 170
5 2000-11-01 1 46
6 2000-11-01 1 28
7 2000-11-01 1 55
8 2000-11-01 1 90
9 2000-11-01 1 20
10 2000-11-01 1 109
11 2000-11-01 1 25
12 2000-11-01 1 129
13 2000-11-01 1 19
14 2000-11-01 1 19
15 2000-11-01 1 168
16 2000-11-01 1 19
17 2000-11-01 1 29
18 2000-11-01 2 48
19 2000-11-01 1 29
20 2000-11-01 1 98
21 2000-11-01 2 58
22 2000-11-01 1 24
23 2000-11-01 2 56
24 2000-11-01 1 86
In [31]: x.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 25 entries, 0 to 24
Data columns (total 3 columns):
date 25 non-null datetime64[ns]
amount 25 non-null int64
price 25 non-null int64
dtypes: datetime64[ns](1), int64(2)
我想要什么:
现在我想要一个包含每件商品价格的新列。这是:
- 当 amount == 1 -> 它与
price的值相同在那一排 - 当数量 > 1 -> 是
price/amount那一排的
我尝试使用 bool 索引:
In [32]: x['price1'] = x['price'] # make a full copy of the column
In [33]: rows = x['amount'] > 1
In [34]: x['price1'][rows] = x['price1'][rows] / x['amount'][rows] # change rows where amount>1
这适用于小型 数据集 x . 输出结果如下:
In [54]: x
Out[54]:
date amount price price1
0 2000-11-01 3 57 19
1 2000-11-01 2 48 24
2 2000-11-01 1 135 135
3 2000-11-01 1 24 24
4 2000-11-01 2 170 85
5 2000-11-01 1 46 46
6 2000-11-01 1 28 28
7 2000-11-01 1 55 55
8 2000-11-01 1 90 90
9 2000-11-01 1 20 20
10 2000-11-01 1 109 109
11 2000-11-01 1 25 25
12 2000-11-01 1 129 129
13 2000-11-01 1 19 19
14 2000-11-01 1 19 19
15 2000-11-01 1 168 168
16 2000-11-01 1 19 19
17 2000-11-01 1 29 29
18 2000-11-01 2 48 24
19 2000-11-01 1 29 29
20 2000-11-01 1 98 98
21 2000-11-01 2 58 29
22 2000-11-01 1 24 24
23 2000-11-01 2 56 28
24 2000-11-01 1 86 86
问题:
当我切出更大范围的 df 时,用这个完整的代码:
x = df[['date', 'amount', 'price']][:100]
x['price1'] = x['price']
rows = x['amount'] > 1
x['price1'][rows] = x['price'][rows] / x['amount'][rows]
然后我得到一些部门的 NaN:
In [113]: x
Out[113]:
date amount price price1
0 2000-11-01 3 57 19 <<
1 2000-11-01 2 48 24 <<
2 2000-11-01 1 135 135
3 2000-11-01 1 24 24
4 2000-11-01 2 170 NaN
5 2000-11-01 1 46 46
6 2000-11-01 1 28 28
7 2000-11-01 1 55 55
8 2000-11-01 1 90 90
9 2000-11-01 1 20 20
10 2000-11-01 1 109 109
11 2000-11-01 1 25 25
12 2000-11-01 1 129 129
13 2000-11-01 1 19 19
14 2000-11-01 1 19 19
15 2000-11-01 1 168 168
16 2000-11-01 1 19 19
17 2000-11-01 1 29 29
18 2000-11-01 2 48 NaN
19 2000-11-01 1 29 29
20 2000-11-01 1 98 98
21 2000-11-01 2 58 85 <<
22 2000-11-01 1 24 24
23 2000-11-01 2 56 NaN
24 2000-11-01 1 86 86
25 2000-11-01 1 145 145
26 2000-11-01 1 29 29
27 2000-11-01 12 434 NaN
28 2000-11-01 1 46 46
29 2000-11-01 1 52 52
.. ... ... ... ...
70 2000-11-01 1 38 38
71 2000-11-01 1 80 80
72 2000-11-01 1 79 79
73 2000-11-01 2 140 24 <<
74 2000-11-01 1 38 38
75 2000-11-01 1 40 40
76 2000-11-01 3 78 NaN
77 2000-11-01 2 104 NaN
78 2000-11-01 2 130 29 <<
79 2000-11-01 1 96 96
80 2000-11-01 1 42 42
81 2000-11-01 1 109 109
82 2000-11-01 1 89 89
83 2000-11-01 1 26 26
84 2000-11-01 1 49 49
85 2000-11-01 1 135 135
86 2000-11-01 1 38 38
87 2000-11-01 1 29 29
88 2000-11-01 2 46 NaN
89 2000-11-01 1 89 89
90 2000-11-01 1 25 25
91 2000-11-01 2 118 28 <<
92 2000-11-01 1 85 85
93 2000-11-01 1 52 52
94 2000-11-01 1 42 42
95 2000-11-01 2 84 NaN
96 2000-11-01 1 18 18
97 2000-11-01 1 28 28
98 2000-11-01 1 85 85
99 2000-11-01 1 102 102
[100 rows x 4 columns]
很奇怪,有些部门工作(用 << 标记)。 任何想法可能会发生什么错误? 谢谢
对可能的“错误”有更多了解?
当我转换新的 price1 时,我尝试了更多-列到 float64在除法之前,它似乎起作用了。对我来说,这似乎是一个错误。我什至可以将其转换回 int64划分后,结果很好。我不知道为什么它在小切片上(即当我 do x = df[...][:25] 时)正确工作!?
x = df[['date', 'amount', 'price']][:100]
x['price1'] = x['price'].astype(float64)
rows = x['amount'] > 1
x['price1'][rows] = (x['price1'][rows] / x['amount'][rows]).astype(int64)
x
给出:
In [146]: x = df[['date', 'amount', 'price']][:100]
In [147]: x['price1'] = x['price'].astype(float64)
In [148]: rows = x['amount'] > 1
In [149]: x['price1'][rows] = (x['price1'][rows] / x['amount'][rows]).astype(int64)
In [150]: x
Out[150]:
date amount price price1
0 2000-11-01 3 57 19
1 2000-11-01 2 48 24
2 2000-11-01 1 135 135
3 2000-11-01 1 24 24
4 2000-11-01 2 170 85
5 2000-11-01 1 46 46
6 2000-11-01 1 28 28
7 2000-11-01 1 55 55
8 2000-11-01 1 90 90
9 2000-11-01 1 20 20
10 2000-11-01 1 109 109
11 2000-11-01 1 25 25
12 2000-11-01 1 129 129
13 2000-11-01 1 19 19
14 2000-11-01 1 19 19
15 2000-11-01 1 168 168
16 2000-11-01 1 19 19
17 2000-11-01 1 29 29
18 2000-11-01 2 48 24
19 2000-11-01 1 29 29
20 2000-11-01 1 98 98
21 2000-11-01 2 58 29
22 2000-11-01 1 24 24
23 2000-11-01 2 56 28
24 2000-11-01 1 86 86
25 2000-11-01 1 145 145
26 2000-11-01 1 29 29
27 2000-11-01 12 434 36
28 2000-11-01 1 46 46
29 2000-11-01 1 52 52
.. ... ... ... ...
70 2000-11-01 1 38 38
71 2000-11-01 1 80 80
72 2000-11-01 1 79 79
73 2000-11-01 2 140 70
74 2000-11-01 1 38 38
75 2000-11-01 1 40 40
76 2000-11-01 3 78 26
77 2000-11-01 2 104 52
78 2000-11-01 2 130 65
79 2000-11-01 1 96 96
80 2000-11-01 1 42 42
81 2000-11-01 1 109 109
82 2000-11-01 1 89 89
83 2000-11-01 1 26 26
84 2000-11-01 1 49 49
85 2000-11-01 1 135 135
86 2000-11-01 1 38 38
87 2000-11-01 1 29 29
88 2000-11-01 2 46 23
89 2000-11-01 1 89 89
90 2000-11-01 1 25 25
91 2000-11-01 2 118 59
92 2000-11-01 1 85 85
93 2000-11-01 1 52 52
94 2000-11-01 1 42 42
95 2000-11-01 2 84 42
96 2000-11-01 1 18 18
97 2000-11-01 1 28 28
98 2000-11-01 1 85 85
99 2000-11-01 1 102 102
[100 rows x 4 columns]
请您参考如下方法:
你正在做你不应该做的链式赋值,因为它有时不起作用,这就是你所观察到的:http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
此外,如果可能,您应该始终使用 .loc,如果我们比较使用掩码和不使用掩码的性能,我们可以看到对于基于样本数据的 25000 行数据帧,它更快没有面具:
In [17]:
%%timeit
x = df[['date', 'amount', 'price']][:100]
x['price1'] = x['price']
rows = x['amount'] > 1
x.loc[rows,'price1']= x['price'] / x['amount']
100 loops, best of 3: 2.54 ms per loop
In [19]:
%timeit x.loc[rows,'price1']= x['price'] / x['amount']
1000 loops, best of 3: 950 µs per loop
您的原始代码:
In [23]:
%%timeit
x = df[['date', 'amount', 'price']][:100]
x['price1'] = x['price'].astype(float64)
rows = x['amount'] > 1
x['price1'][rows] = (x['price1'][rows] / x['amount'][rows]).astype(int64)
100 loops, best of 3: 2.48 ms per loop
所以你看到在整个数据帧上划分比选择前 100 行,然后屏蔽然后划分更快
