Why the data type in last columns is str instead of float

0 votes

All the data fomr column B to column K are numbers stored as text in excel file.
enter image description here

I have uploaded the excel file in dropbox as a sample to test with.
sample data text

Download it and save in /tmp/tsm.xlsx.

tsm.xlsx for testing

After reading it into a dataframe, I discover that the last column K's data type is str, whereas columns B through J's data types are all numbers:

import pandas as pd
sexcel = '/tmp/tsm.xlsx'
df = pd.read_excel(sexcel,sheet_name='ratios_annual')
row_num = len(df)
for id in range(row_num):
    print('the data type in last column--K is',type(df.iloc[id,-1]))
    print('the data type in  column--J is',type(df.iloc[id,-2]))

the data type in last column--K is <class 'str'>
the data type in  column--J is <class 'numpy.float64'>
the data type in last column--K is <class 'str'>
the data type in  column--J is <class 'numpy.float64'>

When viewing the file in Excel, it is clear that all of the numbers in columns B through K are text-based. Why does it read into a data frame with different types?
Please examine the sample data that you can download.

Feb 13 in Others by Kithuzzz
• 34,760 points
51 views

1 answer to this question.

0 votes

Have you attempted to float-ify that column explicitly? Pandas attempts to infer data types correctly by default, but if the data is imperfect, it may make a mistake (e.g. column has a character somewhere, etc.). You might find the problem by changing that column's type to float.

df["2012"] = df["2012"].astype(float)
answered Feb 13 by narikkadan
• 59,740 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Where is the documentation to refer for coinbase api integration of Etherium coin currency in php?

Hey there! Please refer to the following ...READ MORE

answered Jan 25, 2019 in Others by Omkar
• 69,190 points
357 views
0 votes
1 answer

What is a future data type in Flutter?

Hi@akhtar, Future is a type that ‘comes from the ...READ MORE

answered Aug 11, 2020 in Others by MD
• 95,460 points
453 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 3,458 views
0 votes
1 answer
0 votes
1 answer

Excel: Is it possible to reorder the data in 2 columns to match up if they have a certain number of characters / a string in common?

Try this: =LET(files,A1:A4, URLs,B1:B4, f,BYROW(files,LAMBDA(r,TEX ...READ MORE

answered Jan 21 in Others by narikkadan
• 59,740 points
75 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP