Myujth

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?

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)))

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

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