31

In the excel sheet , i have two columns with large numbers.

But when i read the excel file with read_excel() and display the dataframe,

those two columns are printed in scientific format with exponential.

How can get rid of this format?

Thanks

Output in Pandas

enter image description here

Smita Ahinave
  • 1,901
  • 7
  • 23
  • 42
Nathaniel Babalola
  • 617
  • 2
  • 6
  • 15
  • Why do you want to? You know, that internally there is no difference at all. It's just the visualization/output function, which thinks, it's a good idea to shorten it. – sascha Aug 01 '16 at 01:51
  • yeah i agree, not used to scientific notations..that's why i prefer it's original form – Nathaniel Babalola Aug 02 '16 at 14:44
  • 6
    This can actually be very problematic for other reasons. In one of our systems the IDs being noted are for keys in a database. So manually querying the database based on pandas output becomes very difficult. Also, with very long IDs with lots of zeroes like 1000000000000000000000041, the notation leaves out the 41. – John Humphreys Dec 13 '18 at 14:22

2 Answers2

32

The way scientific notation is applied is controled via pandas' display options:

pd.set_option('display.float_format', '{:.2f}'.format)
df = pd.DataFrame({'Traded Value':[67867869890077.96,78973434444543.44],
                   'Deals':[789797, 789878]})
print(df)
       Traded Value   Deals
0 67867869890077.96  789797
1 78973434444543.44  789878

If this is simply for presentational purposes, you may convert your data to strings while formatting them on a column-by-column basis:

df = pd.DataFrame({'Traded Value':[67867869890077.96,78973434444543.44],
                   'Deals':[789797, 789878]})
df

    Deals   Traded Value
0   789797  6.786787e+13
1   789878  7.897343e+13


df['Deals'] = df['Deals'].apply(lambda x: '{:d}'.format(x))
df['Traded Value'] = df['Traded Value'].apply(lambda x: '{:.2f}'.format(x))
df    

     Deals       Traded Value
0   789797  67867869890077.96
1   789878  78973434444543.44

An alternative more straightforward method would to put the following line at the top of your code that would format floats only:

pd.options.display.float_format = '{:.2f}'.format
Sergey Bushmanov
  • 23,310
  • 7
  • 53
  • 72
  • 1
    i have applied this `display.precision` but it seems to affect the decimal part, it's the integer part i want affected – Nathaniel Babalola Aug 02 '16 at 14:54
  • Can you please give an example of (i) what you want to achieve and (ii) what you want to avoid? – Sergey Bushmanov Aug 02 '16 at 15:06
  • 3
    okay, (i) I want the exact values from my excel file (ii) i want to avoid pandas representing the large numbers in the last two columns in scientific form (http://imgur.com/a/9Ls6c) – Nathaniel Babalola Aug 02 '16 at 17:02
  • I am facing same issue and I cant see anyone provided solution here. @NathanielBabalola can you please let me know if you have found any solution for this problem. – Amit Aug 17 '21 at 16:15
0

try '{:.0f}' with Sergeys, worked for me.

R3X
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jun 09 '22 at 04:33