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'

• Karl Knechtel: 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?
• Jonathan Prieto: 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.
• Karl Knechtel: 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)?
• smci: 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.

Answer 1

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()

• Jonathan Prieto: 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: @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: 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: 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.

Answer 2

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

• Jonathan Prieto: Could you explain me this ?
• Jonathan Prieto: 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: You can just directly do df[column].fillna('') to fill NAs with pd.Series.fillna() , you don't need this.