String to Float in DataFrame's Column

Question

The 5th column of my DataFrame is a list of floats. I want to replace the list with the maximum value from the list. How could I do so?

I'm trying this but I get an error:

import pandas as pd
import numpy as np

colNames = ['unixTime', 'sampleAmount','Time','samplingRate', 'Data']

data = pd.read_csv("project_fan.csv",  sep = ';', error_bad_lines = False, names = colNames) 
print(data.head())
data['Data'] = [float(x) for x in data.Data.values]
data['Data'] = [np.array(x).mean()for x in data.Data.values]

Traceback (most recent call last):
  File "new.py", line 9, in <module>
    data['Data'] = [float(x) for x in data.Data.values]
ValueError: could not convert string to float: [1618.6294555664062, 1619.0826416015625, 1620.0897216796875, 1620.0393676757812, 1620.0393676757812, 1620.240783691406, 1620.391845703125, 1620.0897216796875, 1619.435119628906, 1620.4925537109373, 16

Also tried to use astype(float).mean but doesn't work.

Sample DataFrame:

       unixTime  sampleAmount  Time  samplingRate   Data
0  1.556891e+09         16384   340  48188.235294  [1618.6294555664062,1619.0826416015625,1620.489622]
1  1.556891e+09         16384   341  48046.920821  [1619.78759765625,1619.0826416015625,1620.49754]

Please add a minimum dataset to reproduce your problem. Thanks — Alfonso, Jan 17 '20 at 14:48
@anky_91 doesn't work properly. It compiles but then gives NaN as the value for all rows of that column. — x89, Jan 17 '20 at 14:52
please provide a code to construct the dataframe along with a clear expected output. Thanks — anky, Jan 17 '20 at 14:57
@x89 [JFYI](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) — Danila Ganchar, Jan 17 '20 at 15:00
I suspect you created a `df` with list values in a column and saved to a `csv`. Look at the text file. Those lists are probably quoted. They then load as strings, not as lists. So while you can put objects like lists in a `pandas` column, you loose a lot of the `pandas` functionality. — hpaulj, Jan 17 '20 at 20:37

score 3 · Accepted Answer · answered Jan 17 '20 at 16:13

From your error message, it's clear that the data in your "Data" column is stored as a string containing what looks like a Python representation of a list of floats. Which is natural, considering that is coming from a CSV file, which can't otherwise represent a list of numbers in a single column.

You can check that with type(data.Data[0]), which I expect will tell you str.

Since it looks like a Python representation of a list of floats, one good way is to use Python's module to evaluate a Python literal, which you can do with the ast.literal_eval() function. That function is able to interpret Python basic types (integers, floats, strings, lists, tuples, dicts) and it's a safe way to parse contents coming from an external source such as a CSV file.

So you can convert it to an actual list of floats with:

import ast
data['Data'] = data.Data.transform(ast.literal_eval)

Another approach is to claim that this column contains JSON-encoded data and parse it as JSON instead. It turns out in this case, for a list of floats, both the Python and the JSON representation are equivalent, so either method should work. (It's possible the JSON decoding will be faster, JSON is generally simpler than the general Python literal syntax.)

To decode it as JSON (alternative to the above):

import json
data['Data'] = data.Data.transform(json.loads)

At this point (after either Python or JSON conversion), you can use functions such as np.mean on the result, since it's just a list of floats and no longer a string:

data['Data'] = data.Data.apply(np.mean)

String to Float in DataFrame's Column

1 Answers1