How to avoid scientific notation when exporting mixed-type dataframe to CSV in pandas?

Other Questions Notion
1

I’m working with a pandas DataFrame that contains both floating-point numbers and strings. When exporting the DataFrame using the to_csv function, larger numeric values get converted into scientific notation. For example, a value like 1234567890123.456 becomes 1.234568e+12 in the file.

I’ve attempted to use the float_format parameter, but it fails due to the presence of string columns. Below is a sample code illustrating my scenario:

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'text': ['apple', 'banana', 'cherry'],
    'big_numbers': np.random.rand(3) * 1e14
})

df.to_csv('output.csv')

How can I export this DataFrame so that the numeric values retain their full precision without converting into scientific notation? Ideally, the output should look similar to:

text,big_numbers
apple,12345678901234.56
banana,78901234567890.12
cherry,34567890123456.78
2

I ran into a similar issue a while back. One solution that worked for me was to convert the DataFrame to a string format with the desired numerical precision before writing to CSV. This method avoids the limitations of the float_format parameter when mixed data types are present.

For example, you could do something like this:

import pandas as pd
import numpy as np

# Create the DataFrame
df = pd.DataFrame({
    'text': ['apple', 'banana', 'cherry'],
    'big_numbers': np.random.rand(3) * 1e14
})

# Convert DataFrame to a formatted string
df_string = df.to_string(index=False, header=True, float_format='%.2f')

# Write the string to a CSV file, replacing spaces with commas
with open('output.csv', 'w') as f:
    f.write(df_string.replace(' ', ','))

This approach lets you set the precision explicitly while keeping the overall structure intact, although it may require some tweaks depending on your data and desired output. It’s a workaround that proved effective in my experience.

3

I’ve encountered this issue before, and a reliable solution I’ve found is to use the float_format parameter in combination with a custom formatter function. Here’s an approach that has worked well for me:

def format_float(x):
    if isinstance(x, float):
        return f'{x:.2f}'
    return x

df.to_csv('output.csv', float_format=format_float)

This method allows you to maintain full precision for large numbers while handling mixed data types. The custom formatter function checks if each value is a float and formats it accordingly, leaving non-float values unchanged. You can adjust the precision by modifying the ‘.2f’ part to suit your needs.

I’ve found this approach to be both elegant and effective, preserving the DataFrame’s structure without resorting to more complex workarounds.

4

hey, i had this problem too. try using pandas’ styler. it’s pretty cool. u can do something like:

df.style.format({‘big_numbers’: ‘{:.2f}’.format}).to_csv(‘output.csv’)

this keeps ur big numbers as they are, no scientifc notation. worked for me, hope it helps!