-2

0.000003 a value in excel cell converts into 3e-05 when read in python using panda pd.read_excel. Here is my program below. I m new to python trying to learn . Please suggest .

import pandas as pd
import numpy as np
import os
import glob
os.chdir('C:\\Users\\Desktop\\Files\\')
files = glob.glob("CT.xls")
for f in files:
    print(f)
    x = pd.read_excel(f).replace(np.nan, '', regex=True)
    cols = x.loc[0]
    x.drop(x.index[[0]],inplace=True)
    x.columns = cols
    #-------------------
    pipe_del =[]
    for i,j in x.iterrows():
        st = ''
        for k in j:
            st = st + (k).strip() + '|'
        st = st[0:len(st)-1]
        pipe_del.append(st)
    newf = f.split('_')[0].split(' ')[0]+'_'+f.split('_')[0].split(' ')[1]+'_'+f.split('_')[0].split(' ')[2]+'_'+f.split('_')[1].split('.')[0]+'.csv'
    with open(newf, "w") as output:
        for i in pipe_del:
            output.write("%s\n" % i)
Anym
  • 1
  • 1
  • 3
  • 1
    What exactly is the question here... ? – meissner_ Sep 06 '18 at 11:47
  • I have a value in Excel column as 0.0000003 and i want this format of decimal to be retained when i read the excel in python. But its not happening , instead the decimal is getting represented as 3e-05 (as exponential). – Anym Sep 06 '18 at 11:50
  • 1
    And why? The value is the same, it's just displayed differently. – IanS Sep 06 '18 at 11:51
  • 4
    Please be more careful about reporting your values. At first I thought you were asking why 0.000003, which is 3e-6, was being interpreted as 3e-5, but now you've used 0.0000003, which is 3e-7, so I think you're just not paying much attention to the number of zeroes. (Incidentally, mistakes like these are one reason exponential notation is often a good idea..) – DSM Sep 06 '18 at 11:52

1 Answers1

3

try

pd.options.display.float_format = '${:,.6f}'.format

and the print your result.

Be careful while changing your display settings, remember to visit the docs : https://pandas.pydata.org/pandas-docs/stable/options.html

anky
  • 74,114
  • 11
  • 41
  • 70