1

I want to merge two dataframes, however, one column contains nan, and after merging, the integer type values are recorded by scientific notation. Actual, I just want to get their original values. Input:

import pandas as pd 
import numpy as np 
left=pd.DataFrame({'key':['one','two','three'],'other':[1,2,3]})

right=pd.DataFrame({'id':[600608457536718400,np.nan,96436390593326400],'key':['one','two','three']})

total=pd.merge(left,right,on=['key'],how='left')
print(left)
print(right)
print(total)

Output:

     key  other
0    one      1
1    two      2
2  three      3
             id    key
0  6.006085e+17    one
1           NaN    two
2  9.643639e+16  three
     key  other            id
0    one      1  6.006085e+17
1    two      2           NaN
2  three      3  9.643639e+16

Expected:

     key  other            id
0    one      1  600608457536718400
1    two      2           NaN
2  three      3  96436390593326400

I try to fillna the column id and then convert the type of the column, but failed.

What I try:

right['id'].fillna(-1,inplace=True)
right['id']=right['id'].astype('int64')
total=pd.merge(left,right,on=['key'],how='left')

Output:

     key  other                  id
0    one      1  600608457536718336
1    two      2                  -1
2  three      3   96436390593326400

Hopefully for help! Thanks!

rosefun
  • 1,797
  • 1
  • 21
  • 33
  • Refer to this : https://stackoverflow.com/questions/21137150/format-suppress-scientific-notation-from-python-pandas-aggregation-results – uttejh Jul 18 '18 at 12:57

1 Answers1

1

Can you try using this one? Perhaps it might work. Basically, while creating dataframe, I changed the type to str. 

import pandas as pd 
import numpy as np 
left=pd.DataFrame({'key':['one','two','three'],'other':[1,2,3]})

right=pd.DataFrame({'id':[str(600608457536718400),np.nan,str(96436390593326400)],'key':['one','two','three']})

total=pd.merge(left,right,on=['key'],how='left')
print(total)

This gives following output

     key  other                  id
0    one      1  600608457536718400
1    two      2                 NaN
2  three      3   96436390593326400