Float64 converted Int64 values are roundedoff due to None

[arunkumaralagesan]

Research

• I have asked my usage related question on StackOverflow.

• I have searched the [pandas] tag on StackOverflow for similar questions.

Link to question on StackOverflow

https://stackoverflow.com/questions/53327973/pandas-converting-null-int64-to-float64

Question about pandas

Hi,
I created a df with BigInt's and a single None value. Post conversion when i try to access the entries, the bigint numbers are overflowed and i could not get back the original value as it is. Even after typecasting, using Decimal package etc.

Code:

import pandas as pd
rounded_or_scientific_df = pd.DataFrame({
    'col1': [6760925268837314560, 6947311761343549440, 7103057685633208320, 6844033375485878447, 6844033375485878272, 
             6844033375485878550, 6944033375485878550, None],
})

expected_df = pd.DataFrame({
    'col1': [6760925268837314560, 6947311761343549440, 7103057685633208320, 6844033375485878447, 6844033375485878272, 
             6844033375485878550, 6944033375485878550],
})

The difference is the insertion of None.

Please refer the google collab playground.
Thanks in advance!

[simonjayhawkins]

The precision loss is when the array is constructed. None is interpreted as missing and using the default inference, NaN is used as the missing value indicator and a float64 NumPy array is created to back the Series. Use Nullable arrays instead.

I think the capitalization in the issue title is maybe confusing and that I have misunderstood the problem? dtype: float64 is the default, dtype: Float64 and dtype: Int64 are Nullable types.

rounded_or_scientific_df = pd.DataFrame({
    'col1': [6760925268837314560, 6947311761343549440, 7103057685633208320, 6844033375485878447, 6844033375485878272, 
             6844033375485878550, 6944033375485878550, None],
})
rounded_or_scientific_df.col1
# 0    6.760925e+18
# 1    6.947312e+18
# 2    7.103058e+18
# 3    6.844033e+18
# 4    6.844033e+18
# 5    6.844033e+18
# 6    6.944033e+18
# 7             NaN
# Name: col1, dtype: float64
rounded_or_scientific_df = pd.DataFrame({
    'col1': [6760925268837314560, 6947311761343549440, 7103057685633208320, 6844033375485878447, 6844033375485878272, 
             6844033375485878550, 6944033375485878550, None],
}, dtype="Int64")
rounded_or_scientific_df.col1
# 0    6760925268837314560
# 1    6947311761343549440
# 2    7103057685633208320
# 3    6844033375485878447
# 4    6844033375485878272
# 5    6844033375485878550
# 6    6944033375485878550
# 7                   <NA>
# Name: col1, dtype: Int64

[arunkumaralagesan]

The precision loss is when the array is constructed. None is interpreted as missing and using the default inference, NaN is used as the missing value indicator and a float64 NumPy array is created to back the Series. Use Nullable arrays instead.

I think the capitalization in the issue title is maybe confusing and that I have misunderstood the problem? dtype: float64 is the default, dtype: Float64 and dtype: Int64 are Nullable types.

rounded_or_scientific_df = pd.DataFrame({
    'col1': [6760925268837314560, 6947311761343549440, 7103057685633208320, 6844033375485878447, 6844033375485878272, 
             6844033375485878550, 6944033375485878550, None],
})
rounded_or_scientific_df.col1
# 0    6.760925e+18
# 1    6.947312e+18
# 2    7.103058e+18
# 3    6.844033e+18
# 4    6.844033e+18
# 5    6.844033e+18
# 6    6.944033e+18
# 7             NaN
# Name: col1, dtype: float64
rounded_or_scientific_df = pd.DataFrame({
    'col1': [6760925268837314560, 6947311761343549440, 7103057685633208320, 6844033375485878447, 6844033375485878272, 
             6844033375485878550, 6944033375485878550, None],
}, dtype="Int64")
rounded_or_scientific_df.col1
# 0    6760925268837314560
# 1    6947311761343549440
# 2    7103057685633208320
# 3    6844033375485878447
# 4    6844033375485878272
# 5    6844033375485878550
# 6    6944033375485878550
# 7                   <NA>
# Name: col1, dtype: Int64

Hi Thanks for the insights. I understand passing force dtype works. But my bad that i did not provide example with all the conditions. In my use case, the df will contain many columns string, float, int, bigint. I just want to preserve the bigints as string as it is.

rounded_or_scientific_df = pd.DataFrame({
    'col1': [6760925268837314560, 6947311761343549440, 6844033375485878272, 
             6844033375485878550, 6944033375485878550, None],
   'col2': ["test1", "test2", "test3", "test4", "test5", "test6"],
   'col3': [1.1, 2.2, 3.3, 4.4, 5.5, 6.6]
})

But i want the "col1" values to be preserved either as int64 without rounding-off or scientific; or else better stringified.
How the schema would look like in this case?
Thanks again

[simonjayhawkins]

But i want the "col1" values to be preserved either as int64 without rounding-off or scientific; or else better stringified.

yes, only a single dtype is allowed in the DataFrame constructor.

pass a Series or array instead for the columns that you want to control the dtype for and the others will still use the default inference.

rounded_or_scientific_df = pd.DataFrame({
    'col1': pd.array([6760925268837314560, 6947311761343549440, 6844033375485878272, 
             6844033375485878550, 6944033375485878550, None], dtype=str),
   'col2': ["test1", "test2", "test3", "test4", "test5", "test6"],
   'col3': [1.1, 2.2, 3.3, 4.4, 5.5, 6.6]
})
print(rounded_or_scientific_df)
#                   col1   col2  col3
# 0  6760925268837314560  test1   1.1
# 1  6947311761343549440  test2   2.2
# 2  6844033375485878272  test3   3.3
# 3  6844033375485878550  test4   4.4
# 4  6944033375485878550  test5   5.5
# 5                 None  test6   6.6
rounded_or_scientific_df.dtypes
# col1     object
# col2     object
# col3    float64
# dtype: object

[rhshadrach]

Looks to me like @simonjayhawkins's last code sample satisfies the request here. Closing for now - @arunkumaralagesan if you think there is still an issue in pandas comment here as we can reopen.

[simonjayhawkins]

Closing for now - @arunkumaralagesan if you think there is still an issue in pandas comment here as we can reopen.

or in #56678 as other than the performance discussion (for which I don't see evidence to back up the claim) this is moreless a duplicate of the start of that discussion.

[arunkumaralagesan]

But i want the "col1" values to be preserved either as int64 without rounding-off or scientific; or else better stringified.

yes, only a single dtype is allowed in the DataFrame constructor.

pass a Series or array instead for the columns that you want to control the dtype for and the others will still use the default inference.

rounded_or_scientific_df = pd.DataFrame({
    'col1': pd.array([6760925268837314560, 6947311761343549440, 6844033375485878272, 
             6844033375485878550, 6944033375485878550, None], dtype=str),
   'col2': ["test1", "test2", "test3", "test4", "test5", "test6"],
   'col3': [1.1, 2.2, 3.3, 4.4, 5.5, 6.6]
})
print(rounded_or_scientific_df)
#                   col1   col2  col3
# 0  6760925268837314560  test1   1.1
# 1  6947311761343549440  test2   2.2
# 2  6844033375485878272  test3   3.3
# 3  6844033375485878550  test4   4.4
# 4  6944033375485878550  test5   5.5
# 5                 None  test6   6.6
rounded_or_scientific_df.dtypes
# col1     object
# col2     object
# col3    float64
# dtype: object

Thanks for the suggestion, adding the bigInt cols with explicit datatype as Int64 solves the use case.