CODEWITHSUNDEEP
python-3.xpandasdataframetype-conversion
I_m_Possible
I_m_Possible

Reputation: 53

convert float64 (from excel import) to str using pandas

although the same question has been asked multiple times. I dont seem to make it work. I use python 3.8 and I rean an excel file like this

df = pd.read_excel(r"filename.xlsx")

the column A is a float64 type and I see sth like this when I open the df using spyder: "1.5934e+06" So, I need to make it a 7digit string and I used:

df["columnA] = df["columnA].astype(str).str.zfill(7)

But the result is : "1593396.0"

How can I get as a result this = 1593396 ?

I tried replacing '.0' with '' and didnt't work. Instead of getting the '.0' from the end it removed any zero in the cell!

Upvotes: 1

Views: 981

Answers (2)

Wilian
Wilian

Reputation: 1257

When you enter "astype(str)" the code is converting the whole float to string, that includes the dot and zero.

What I did in the code below was:

1 - format the float so that it has 2 decimal places. (this already transforms into string).

2 - replace the ".00" of the string to "";

3 - keeps the zfill that was already in the code.

df["columnA"] = df["columnA"].map('{:.2f}'.format).replace('\.00', '', regex=True).str.zfill(7)

EDIT

Adding a hint, as Corralien said, it is possible to anticipate a step already in the reading of the file. It is best to do only in the desired columns:

df = pd.read_excel(r"filename.xlsx",converters={'columnA':str})
df['columnA'] = df['columnA'].str.zfill(7)

Upvotes: 1

Corralien
Corralien

Reputation: 120409

Don't let Pandas infer your datatype, use dtype=str as argument of pd.read_csv:

df = pd.read_excel(r"filename.xlsx", dtype=str)

Upvotes: 1

Related Questions