how to get rid of pandas converting large numbers in excel sheet to exponential?

Question

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 I get rid of this format?

Thanks

Output in Pandas

Rhombic

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. – Superfuse

yeah i agree, not used to scientific notations..that's why i prefer it's original form – Rhombic

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. – Hatchery

Answer 1

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

Vala

i have applied this display.precision but it seems to affect the decimal part, it's the integer part i want affected – Rhombic

Can you please give an example of (i) what you want to achieve and (ii) what you want to avoid? – Vala

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 (imgur.com/a/9Ls6c) – Rhombic

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. – Berner

Answer 2

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

Dekker

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. – Estremadura