tim
Reputation: 10186
Python/Pandas: Bug with element-wise division resulting in NaN?
BACKGROUND & WHAT THE DATA LOOKS LIKE:
I have a DataFrame df with several columns. For this matter I'm only slicing out the columns of interest and save them in x:
df is a huge dataframe, where I slice data from like this:
In [29]: x = df[['date', 'amount', 'price']][:25]
Just as an info what x looks like, see this:
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)
WHAT I WANT:
Now I want a new column that contains the price per single item. This is:
- When amount == 1 -> It's the same value as the
pricein that row - When amount > 1 -> It's
price/amountof that row
I tried it using boolean indexing:
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
This works for small datasets x.
The output is as follows:
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
PROBLEM:
When I'm slicing out a bigger range of df, with this full code:
x = df[['date', 'amount', 'price']][:100]
x['price1'] = x['price']
rows = x['amount'] > 1
x['price1'][rows] = x['price'][rows] / x['amount'][rows]
then I'm getting NaN for some divisions:
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]
strange enough, some divisions work (marked with <<).
Any ideas what could be going on wrongly?
Thanks
MORE INSIGHT INTO A POSSIBLE "BUG"?
I tried a little bit more and when I'm converting the new price1-column to float64 before the division, it seems to work. For me, this seems like a bug. I can even convert this back to int64 after division and the results are fine with it. I don't know why it works on small slices (i.e. when I do x = df[...][:25]) correctly!?
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
gives:
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]
Upvotes: 2
Views: 3109
Answers (1)
EdChum
Reputation: 394159
You are doing chained assignment which you shouldn't do as it sometimes doesn't work which is what you are observing: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
moreover you should always use .loc if possible, if we compare the performance between using a mask and without a mask we can see that for a 25000 row dataframe based on your sample data it is faster without the mask:
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
Your original code:
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
so you see that dividing on the whole dataframe is faster than selecting the first 100 rows, and then masking and then dividing
Upvotes: 2
Related Questions
- Getting NaN for dividing each row value by row sum
- Dividing One Column By Another Returns NAN
- Dividing two dataframes gives NaN
- Question: Dividing values from two different Pandas Dataframes selected based on column values returns NaN
- Weird behavior when checking for NaNs in a pandas dataframe
- Pandas divide returns dataframe with all NaNs
- Divide columns in a DataFrame by a Series (result is only NaNs?)
- Pandas dataframe division returning 'inf' when dividing by non-zero
- Weird Error When Dividing two numbers in Pandas DataFrame
- Pandas divide creates extra columns and NaN