Could not convert string to float Python - Pandas DataFrames

Question

Hi I am getting this error but everything seems ok.

import matplotlib.pyplot as mpl
import pandas as pd

#Uploading data to Python Pandas Dataframe

db_fondos = pd.read_excel('/Users/jonathanprieto/Documents/GitHub/GoogleMaps/data/- Matriz TDF Python.xlsm',
                       sheet_name="DB Fondos")
ts_flujos = pd.read_excel('/Users/jonathanprieto/Documents/GitHub/GoogleMaps/data/- Matriz TDF Python.xlsm',
                       sheet_name="TS Flujos")
ts_ind_fin = pd.read_excel('/Users/jonathanprieto/Documents/GitHub/GoogleMaps/data/- Matriz TDF Python.xlsm',
                       sheet_name="TS Ind. Fin.")
ts_market = pd.read_excel('/Users/jonathanprieto/Documents/GitHub/GoogleMaps/data/- Matriz TDF Python.xlsm',
                       sheet_name="TS Market data")
db_posiciones = pd.read_excel('/Users/jonathanprieto/Documents/GitHub/GoogleMaps/data/- Matriz TDF Python.xlsm',
                       sheet_name="DB Posiciones")
print('Carga completada')


#NaN = np.nan
#db_posiciones["Id. Pos."] = NaN
#db_posiciones.head()

print(db_posiciones)

diccionario=db_fondos.set_index("Fondo")["Id. Fondo"].to_dict()

for index, row in db_posiciones.iterrows():
    ipos = row["Fondo"]
    print(ipos)
    if ipos in diccionario:
        idpos=diccionario[ipos]
        twofirst=row["Fondo"][:2]
        twofirst = twofirst[0:2]
        print(idpos+"-"+twofirst)
        db_posiciones["Id. Pos."].values[index] = str(idpos)+"-"+str(twofirst)
        print(index)
print(db_posiciones)

Could anyone told me why I am getting this error:

File "/Users/jonathanprieto/Documents/GitHub/GoogleMaps/test.py", line 36, in <module>
db_posiciones["Id. Pos."].values[index] = str(idpos) + "-" + str(twofirst)

ValueError: could not convert string to float: '91_AGSACB_08-65'

Well, what kinds of values are stored in the db_posiciones["Id. Pos."].values column? Is '91_AGSACB_08-65' that kind of value? Are you surprised that ` str(idpos)+"-"+str(twofirst) ` produces that kind of value? Are you expecting the conversion to happen automatically? If so, what should the value be in this case? — Karl Knechtel
– Karl Knechtel, Commented May 25, 2020 at 2:17
In db_posiciones["Id. Pos."].values[index], the is NaN value because it an empty cell, the value 91_AGSACB_08-65 is exactly why I want to put in the cell. — Jonathan Prieto
– Jonathan Prieto, Commented May 25, 2020 at 2:20
Okay, what kind of thing is NaN (hint: what does the error message say couldn't be done)? What kind of thing is 91_AGSACB_08-65 (hint: what does the error message say it was)? — Karl Knechtel
– Karl Knechtel, Commented May 25, 2020 at 2:26
db_posiciones["Id. Pos."].values[index] is the wrong way to try to slice your data. Just do db_posiciones["Id. Pos."].loc['91_AGSACB_08'] or .iloc[]. Please read the pandas Getting Started doc about how to slice properly with .loc[] / .iloc[] asnd string/ integer row-labels. — smci
– smci, Commented May 25, 2020 at 2:40

smci · Accepted Answer · 2020-06-22 05:45:33Z

3

You're getting this because your series is string, but it contains some NAs, which actually get represented in pandas as nan, which is a float value (that's how pd.read_csv() will handle it). That's why pandas gives a strange warning claiming the string series is a float:

Solution: first, fill any NA values in your string column with empty-string:

df[column].fillna('', inplace=True)

Notes:

make sure to use fillna(..., inplace=True) so you don't need to assign the result back to df['column'] to prevent it getting thrown away.
for doc, see pd.Series.fillna(..., inplace=True)
you can fill several Series (/columns) in your dataframe at once, with df.fillna rather than df[column].fillna(..., inplace=True) on each column. See pd.DataFrame.fillna()

edited Jun 22, 2020 at 5:45

answered May 25, 2020 at 2:29

smci

34.2k21 gold badges118 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jonathan Prieto Over a year ago

I just tried with this: db_posiciones["Id. Pos."].fillna(" ") and I have the same problem: File "/Users/jonathanprieto/Documents/GitHub/GoogleMaps/test.py", line 36, in <module> db_posiciones["Id. Pos."].values[index] = str(idpos)+"-"+str(twofirst) ValueError: could not convert string to float: '91_AGSACB_08-65'

smci Over a year ago

@JonathanPrieto: make sure to use fillna(..., inplace=True) so you don't need to assign the result back to df['column'] to prevent it getting thrown away.

smci Over a year ago

JonathanPrieto: db_posiciones["Id. Pos."].values[index] is the wrong way to try to slice your data. Just do db_posiciones["Id. Pos."].loc['91_AGSACB_08'] or .iloc[]. Please read the pandas Getting Started doc about how to slice properly.

smci Over a year ago

As a general tip, you should never need to use df[column].values in pandas, unless you're doing something unusual like converting to matrices for numpy/scipy. So if you find yourself feeling the urge to use .values, go check the 10 minutes to pandas doc first, there is almost surely a standard pandas method or two on DataFrame or Series that you can use instead.

Darkknight · Accepted Answer · 2020-05-25 02:18:17Z

0

df[column] = df[column].apply(lambda x: float(x.split()[0].replace(',', '')))

answered May 25, 2020 at 2:18

Darkknight

1,87613 silver badges23 bronze badges

3 Comments

Jonathan Prieto Over a year ago

Could you explain me this ?

Jonathan Prieto Over a year ago

I am getting the following error: File "/Users/jonathanprieto/Documents/GitHub/GoogleMaps/test.py", line 25, in <lambda> db_posiciones["Id. Pos."] = db_posiciones["Id. Pos."].apply(lambda x: float(x.split()[0].replace(',', ''))) AttributeError: 'float' object has no attribute 'split'

smci Over a year ago

You can just directly do df[column].fillna('') to fill NAs with pd.Series.fillna() , you don't need this.

Collectives™ on Stack Overflow

Could not convert string to float Python - Pandas DataFrames

2 Answers 2

Solution: first, fill any NA values in your string column with empty-string:

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Solution: first, fill any NA values in your string column with empty-string:

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related