Pandas reindex to higher resolution

Question

I have a pandas dataframe with index 3 to 15 with 0.5 steps and want to reindex it to 0.1 steps. I tried this code and it doesn't work

# create data and set index and print for verification
df = pd.DataFrame({'A':np.arange(3,5,0.5),'B':np.arange(3,5,0.5)})
df.set_index('A', inplace = True)
df.reindex(np.arange(3,5,0.1)).head(15)

The above code outputs this:

A	B
3.0	3.0
3.1	NaN
3.2	NaN
3.3	NaN
3.4	NaN
3.5	NaN * expected output in this position to be 3.5 since it exists in the original df
3.6	NaN
3.7	NaN
3.8	NaN

Strangely the problem is fixed when reindexing from 0 instead of 3 as it's shown in the code below:

df = pd.DataFrame({'A':np.arange(3,5,0.5),'B':np.arange(3,5,0.5)})
df.set_index('A', inplace = True)
print(df.head())
df.reindex(np.arange(0,5,0.1)).head(60)

The output now correctly shows

A	B
0.0	NaN
...	...
3.0	3.0
3.1	NaN
3.2	NaN
3.3	NaN
3.4	NaN
3.5	3.5
3.6	NaN
3.7	NaN
3.8	NaN

I'm running python 3.8.5 on Windows 10.

Pandas version is 1.4.07

Numpy version is 1.22.1

Does anyone know why this happens? If it's a known or new bug? If the bug has been fixed in a newer version of python, pandas or numpy?

Thanks

Answer 1

Good question.

The answer is because np.arange(3,5,0.1) creates a value of 3.5 that is not exactly 3.5. It is 3.5000000000000004. But np.arange(0,5,0.1) does create a 3.5 that is exactly 3.5. Plus, np.arange(3,5,0.5) also generates a 3.5 that is exactly 3.5.

pd.Index(np.arange(3,5,0.1)) 

Float64Index([               3.0,                3.1,                3.2,
              3.3000000000000003, 3.4000000000000004, 3.5000000000000004,
              3.6000000000000005, 3.7000000000000006, 3.8000000000000007,
               3.900000000000001,  4.000000000000001,  4.100000000000001,
               4.200000000000001,  4.300000000000001,  4.400000000000001,
               4.500000000000002,  4.600000000000001,  4.700000000000001,
               4.800000000000002,  4.900000000000002],
             dtype='float64')

and

pd.Index(np.arange(0,5,0.1))

Float64Index([                0.0,                 0.1,                 0.2,
              0.30000000000000004,                 0.4,                 0.5,
               0.6000000000000001,  0.7000000000000001,                 0.8,
                              0.9,                 1.0,                 1.1,
               1.2000000000000002,                 1.3,  1.4000000000000001,
                              1.5,                 1.6,  1.7000000000000002,
                              1.8,  1.9000000000000001,                 2.0,
                              2.1,                 2.2,  2.3000000000000003,
               2.4000000000000004,                 2.5,                 2.6,
                              2.7,  2.8000000000000003,  2.9000000000000004,
                              3.0,                 3.1,                 3.2,
               3.3000000000000003,  3.4000000000000004,                 3.5,
                              3.6,                 3.7,  3.8000000000000003,
               3.9000000000000004,                 4.0,  4.1000000000000005,
                              4.2,                 4.3,                 4.4,
                              4.5,  4.6000000000000005,                 4.7,
                4.800000000000001,                 4.9],
             dtype='float64')

and

pd.Index(np.arange(3,5,0.5))

Float64Index([3.0, 3.5, 4.0, 4.5], dtype='float64')

This is definitely related to Numpy:

np.arange(3,5,0.1)[5]

3.5000000000000004

and

np.arange(3,5,0.1)[5] == 3.5

False

This situation is documented in the Numpy arange doc:

https://numpy.org/doc/stable/reference/generated/numpy.arange.html

The length of the output might not be numerically stable.

Another stability issue is due to the internal implementation of numpy.arange. The actual step value used to populate the array is dtype(start + step) - dtype(start) and not step. Precision loss can occur here, due to casting or due to using floating points when start is much larger than step. This can lead to unexpected behaviour.

It looks like np.linspace might be able to help you out here:

pd.Index(np.linspace(3,5,num=21))

Float64Index([3.0, 3.1, 3.2, 3.3, 3.4, 3.5, 3.6, 3.7, 3.8, 3.9, 4.0, 4.1, 4.2,
              4.3, 4.4, 4.5, 4.6, 4.7, 4.8, 4.9, 5.0],
             dtype='float64')