Pandas Dataframe interpreting columns as float instead of String

user_10645913 0
Viewed 13687

I want to import a csv file into a pandas dataframe. There is a column with IDs, which consist of only numbers, but not every row has an ID.

   ID      xyz
0  12345     4.56
1           45.60
2  54231   987.00

I want to read this column as String, but even if I specifiy it with

df=pd.read_csv(filename,dtype={'ID': str})

I get 

   ID         xyz
0  '12345.0'    4.56
1   NaN        45.60
2  '54231.0'  987.00

Is there an easy way get the ID as a string without decimal like '12345'without having to edit the Strings after importing the table?

pandas dataframe python-3.x python types
3 Answers

A solution could be this, but after you have imported the df:

df = pd.read_csv(filename)
df['ID'] = df['ID'].astype(int).astype(str)

Or since there are NaN with:

df['ID'] = df['ID'].apply(lambda x: x if pd.isnull(x) else str(int(x)))
user_51789050

Possible solution if missing values are not in numeric columns - ad parameter keep_default_na=False for not convert empty values to strings, but it NOT convert to NaNs in all data, not always in first column, check also docs:

import pandas as pd

temp=u"""ID;xyz
0;12345;4.56
1;;45.60
2;54231;987.00"""
#after testing replace 'pd.compat.StringIO(temp)' to 'filename.csv'
df = pd.read_csv(pd.compat.StringIO(temp), sep=";", dtype={'ID': str}, keep_default_na=False)
    print (df)
      ID     xyz
0  12345    4.56
1          45.60
2  54231  987.00

EDIT:

For me in pandas 0.23.4 working your solution perfectly, so it means bug in lower pandas versions:

import pandas as pd

temp=u"""ID;xyz
0;12345;4.56
1;;45.60
2;54231;987.00"""
#after testing replace 'pd.compat.StringIO(temp)' to 'filename.csv'
df = pd.read_csv(pd.compat.StringIO(temp), sep=";", dtype={'ID': str})
print (df)
      ID     xyz
0  12345    4.56
1    NaN   45.60
2  54231  987.00
user_29010020

Specify float format when writing to csv

Since your underlying problem is output format when exporting data, no manipulation is required. Just use:

df.to_csv('file.csv', float_format='%.0f')

Since you want only specific columns to have this formatting you can use to_string:

def format_int(x):
    return f'{x:.0f}' if x==x else ''

with open('file.csv', 'w') as fout:
    fout.write(df.to_string(formatters={'ID': format_int}))

Keep numeric data numeric

There is a column with IDs, which consist of only numbers

If your column only includes numbers, don't convert to strings! Your desire to convert to strings seems an XY problem. Numeric identifiers should stay numeric.

Float NaN prompts upcasting

Your issue is NaN values can't coexist with integers in a numeric series. Since NaN is a float, Pandas forces upcasting. This is natural, because the object dtype alternative is inefficient and not recommended.

If viable, you can use a sentinel value, e.g. -1 to indicate nulls:

df['ID'] = pd.to_numeric(df['ID'], errors='coerce').fillna(-1).astype(int)

print(df)

      ID     xyz
0  12345    4.56
1     -1   45.60
2  54231  987.00
user_92095460
Related