Merge converting timestamp to scientific notation and losing precision

user_15967967 0
Viewed 103

Original timestamp dtype int64

ts = datetime.fromtimestamp(1627741304932/1000)
print(ts)
2021-07-31 17:21:44.932000

After merging dataframes the timestamp loses/gains +-5 minutes and dtype turns to float64

ts = datetime.fromtimestamp(1.627741e+12/1000)
print(ts)
2021-07-31 17:16:40

Is there a way to avoid this kind of convertion or at least the precision loss?

except from dropping a trillion+ and returning it after merging?

UPDATE

I've created an exact example of my issue:

Example

df1 = pd.DataFrame({'col1': ['ts1', 'ts2', 'ts3', 'ts4'], 
                    'col2': [1627741304932, 1627741304931, 1627741304930, 1627741304929]})
df2 = pd.DataFrame({'col1': ['ts1', 'ts2', 'ts3', 'ts5'], 
                    'col2': [1627741305932, 1627741304931, 1627741304930, 1627741304920]})

x = df1.merge(df2, on='col1', how='outer', suffixes=('_prev', '_new'))

print(x)
print(x.dtypes)

Output

It's happening because of the NaN value that are added to the to the dataframe during the merge

  col1     col2_prev      col2_new
0  ts1  1.627741e+12  1.627741e+12
1  ts2  1.627741e+12  1.627741e+12
2  ts3  1.627741e+12  1.627741e+12
3  ts4  1.627741e+12           NaN
4  ts5           NaN  1.627741e+12

col1          object
col2_prev    float64
col2_new     float64
dtype: object

How can I get around this?

pandas dataframe merge python-3.x timestamp
1 Answers

So it seems that the problem boils down to pandas converting the timestamps from int to float. This is because the 'int64' data type does not support NaN values.

To overcome this, we can use Nullable integer data types:

e.g:

df1 = pd.DataFrame({'col1': ['ts1', 'ts2', 'ts3', 'ts4'], 
                    'col2': [1627741304932, 1627741304931, 1627741304930, 1627741304929]})
df2 = pd.DataFrame({'col1': ['ts1', 'ts2', 'ts3', 'ts5'], 
                    'col2': [1627741305932, 1627741304931, 1627741304930, 1627741304920]})
# allow NaN values (notice the capital I)
df1['col2'] =df1['col2'].astype('Int64')
df2['col2'] =df2['col2'].astype('Int64')
x = df1.merge(df2, on='col1', how='outer', suffixes=('_prev', '_new'))
print(x)
print(x.dtypes)

Output:

  col1      col2_prev       col2_new
0  ts1  1627741304932  1627741305932
1  ts2  1627741304931  1627741304931
2  ts3  1627741304930  1627741304930
3  ts4  1627741304929           <NA>
4  ts5           <NA>  1627741304920
col1         object
col2_prev     Int64
col2_new      Int64
dtype: object
user_41459410
Related