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, 2023 in Others by Kithuzzz
• 38,010 points
522 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, 2023 by narikkadan
• 63,700 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,230 points
570 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,440 points
665 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 4,180 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, 2023 in Others by narikkadan
• 63,700 points
341 views
0 votes
1 answer

How to divide data in excel into 4 columns whose sum is almost equal to 1/4 of the sum of all values/

5049 is the sum of all numbers, ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,700 points
505 views
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