How to prevent exponential notation in CSV export with pandas DataFrame containing mixed data types

[Gizmo_Funny]

I’m working with a pandas DataFrame that has columns with different data types - some are float64 and others contain string values. When I export this DataFrame using to_csv(), large numbers get converted to exponential format automatically.

For instance, a number like 2847392847392.847392 becomes 2.847393e+12 in the output CSV file. I want to keep the full number format without scientific notation.

I tried using the float_format parameter but it fails because my DataFrame also contains text columns. Here’s what I’m dealing with:

import pandas as pd
import numpy as np

data = pd.DataFrame({'labels': ['x','y','z'], 
                     'amounts': np.random.rand(3)*50000000000000})

data.to_csv('output.csv')

# This produces exponential notation:
# ,labels,amounts
# 0,x,4.58392847e+13
# 1,y,1.29384756e+13
# 2,z,3.84756293e+13

# When I try float_format:
data.to_csv('output.csv', float_format='{:.0f}'.format)
# Error: ValueError: Unknown format code 'f' for object of type 'str'

I need the output to show full numbers like:

  labels              amounts
0      x 45839284756392.000000
1      y 12938475629384.000000
2      z 38475629384756.000000

What’s the best way to disable scientific notation for mixed-type DataFrames?

[Claire29]

had this exact problem last week! quick fix: set pandas options before exporting - pd.set_option('display.float_format', '{:.6f}'.format) then run your to_csv() call. works perfectly with mixed dtypes and won’t break string columns like mine did.

[ExcitedGamer85]

I encountered a similar issue recently while working with a mixed-type DataFrame in pandas. To avoid the automatic conversion of large floating-point numbers into exponential notation during CSV export, I found that converting the numeric columns to strings with formatted outputs was the most effective solution. Here’s a snippet that worked for me:

    data_copy = data.copy()
    data_copy['amounts'] = data_copy['amounts'].apply(lambda x: f'{x:.6f}')
    data_copy.to_csv('output.csv')

Alternatively, you can set pandas display options temporarily:

    with pd.option_context('display.float_format', '{:.6f}'.format):
        data.to_csv('output.csv')

Both methods will preserve the integrity of your string columns while ensuring that numbers are printed in standard notation.

[livbrown]

The float_format parameter in to_csv() works with mixed data types, but you need string format instead of the .format() method. I hit this same issue when exporting financial data with transaction IDs and amounts.

Try this:

data.to_csv('output.csv', float_format='%.6f')

The key is using old-style string formatting with '%.6f' instead of the newer .format() method. Pandas applies the formatting only to float columns and leaves string columns alone.

If you need more control over precision for different columns, modify the DataFrame before export by targeting specific columns:

data_modified = data.copy()
data_modified['amounts'] = data_modified['amounts'].map(lambda x: f'{x:.6f}')
data_modified.to_csv('output.csv')

This converts the numeric column to string format with your desired precision while preserving other columns.