could not convert string to float: '7,751.30'

Question

I get the TWSE price from Taiwan Stock Exchange.

df = pd.read_csv(r'C:\Stock\TWSE.csv',encoding='Big5')
df.head()
    日期    開盤指數   最高指數   最低指數   收盤指數
0 96/02/01 7,751.30  7,757.63   7,679.78  7,701.54
1 96/02/02 7,754.16  7,801.63   7,751.53  7,777.03
2 96/02/05 7,786.77  7,823.94   7,772.05  7,783.12
3 96/02/06 7,816.30  7,875.75   7,802.94  7,875.75
4 96/02/07 7,894.77  7,894.77   7,850.06  7,850.06

df.loc[0][2]
'7,757.63'

type(df.loc[0][2])
str

I want to convert the str type to float type for the purpose of plotting. But, I can not convert them. For example:

float(df.loc[0][2])
ValueError: could not convert string to float: '7,757.63'

You can't do it with the comma in the string. Remove comma, then it should convert fine. — August Williams, Dec 20 '17 at 14:19
`df = pd.read_csv(r'C:\Stock\TWSE.csv',encoding='Big5', thousands=',')`. The read functions have a `thousands` argument you should exploit for this. — Abdou, Dec 20 '17 at 14:28
@Abdou Ah, I knew about this, but missed it. Why not post an answer? — cs95, Dec 20 '17 at 14:34

score 2 · Answer 1 · answered Dec 20 '17 at 14:38

pd.read_csv, much like almost every other pd.read_* function, has a thousands parameter you can set to ',' to make sure that you're importing those values as floats.

The following is an illustration:

import io
import pandas as pd


txt = '日期    開盤指數   最高指數   最低指數   收盤指數\n0 96/02/01 7,751.30  7,757.63   7,679.78  7,701.54\n1 96/02/02 7,754.16  7,801.63   7,751.53  7,777.03\n2 96/02/05 7,786.77  7,823.94   7,772.05  7,783.12\n3 96/02/06 7,816.30  7,875.75   7,802.94  7,875.75\n4 96/02/07 7,894.77  7,894.77   7,850.06  7,850.06'

with io.StringIO(txt) as f:
    df = pd.read_table(f, encoding='utf8', header=0, thousands=',', sep='\s+')

print(df)

Yields:

   日期     開盤指數     最高指數  最低指數  收盤指數
0  96/02/01  7751.30  7757.63  7679.78  7701.54
1  96/02/02  7754.16  7801.63  7751.53  7777.03
2  96/02/05  7786.77  7823.94  7772.05  7783.12
3  96/02/06  7816.30  7875.75  7802.94  7875.75
4  96/02/07  7894.77  7894.77  7850.06  7850.06

I hope this proves helpful.

Thank you! it works. And, I think it is really efficient. – 廖家揚 Dec 20 '17 at 14:44 — 廖家揚, Dec 20 '17 at 14:44

score 0 · Answer 2 · edited Dec 20 '17 at 19:36

0

float(df.loc[0][2].replace(',',''))

edited Dec 20 '17 at 19:36

cs95

379,657
97
704
746

answered Dec 20 '17 at 14:21

Shiva

467
2
4
12

Thank you, Shiva. It works! I want to convert all number data to float type. Is there any fast solution? – 廖家揚 Dec 20 '17 at 14:30
np, if possible make it as correct answer, it might helpfull for some one :) – Shiva Dec 20 '17 at 14:31
This is a pretty poor answer, and it doesn't solve the problem. – cs95 Dec 20 '17 at 14:32
1

put this code in forloop.. – Shiva Dec 20 '17 at 14:36
That's the worst possible solution for a pandas question... using a loop. – cs95 Dec 20 '17 at 14:38
@cᴏʟᴅsᴘᴇᴇᴅ thanks for the comments.. am not into pandas.. i have given my input. – Shiva Dec 20 '17 at 14:40
OK, I use for loop to complete it. It works. But I will use 'thousands' in read_csv function. Thank you!!! – 廖家揚 Dec 20 '17 at 14:46

could not convert string to float: '7,751.30'

2 Answers2