Search code examples
pandaspandasql

Number format column with pandas.DataFrame.to_csv()?

I've a process than reads the information from a Microsoft SQL database using:

df = psql.read_sql(sql, con=connection)
print(df)

This function is used in many processes so variable sql doesn't have the same columns (variable structure).

Then I get, for example, the following data:

STORE EMAIL_CONTACT VALUE
10    a@mail.com    2.2100
23    b@mail.com    0.7990

Everything is fine to this point.

When extracting to csv using:

file = r"Test.csv"
df.to_csv(file, sep=";", index=False, quoting=csv.QUOTE_ALL)

The output is the following:

"STORE";"EMAIL_CONTACT";"VALUE"
"10.0";"a@mail.com";"2.2100"
"23.0";"b@mail.com";"0.7990"

The column STORE now has ".0"...

Is there a way to configure the function "to_csv" to output exactly (values) as shown in print? Thanks in advance.

"STORE";"EMAIL_CONTACT";"VALUE"
"10";"a@mail.com";"2.2100"
"23";"b@mail.com";"0.7990"

Solved: The problem was with the decimal option:

df.to_csv(file, sep=";", index=False, quoting=csv.QUOTE_ALL, decimal=",")

"STORE";"EMAIL_CONTACT";"VALUE"
"10";"a@mail.com";"2.2100"
"23";"b@mail.com";"0.7990"

Thanks everyone for the support!

Solution

  • STORE is probably a float, check it out with

    print df.STORE.dtype

    if so, do:

    df.STORE = df.STORE.astype(int)

    then:

    df.to_csv("Test.csv", sep=";", index=False)

    output:

    STORE;EMAIL_CONTACT;VALUE
1;a@mail.com;2.2100
2;b@mail.com;0.7990

    EDIT: For tabulation use:

    df.to_csv("Test.csv", sep="\t", index=False)

    this will output a csv with this format:

    STORE    EMAIL_CONTACT    VALUE
1    a@mail.com    2.2100
2    b@mail.com    0.7990