I have a dataframe that looks like this:
| Unit | Charges |
|----------|---------------------------------|
| DW01-100 | trash(15); pest(2) |
| DW01-101 | trash(15); pest(3) |
| DW01-102 | garage(150); trash(15); pest(3) |
| DW01-103 | pest(3); trash(15) |
| DW01-104 | trash(15); pest(3) |
| DW11-407 | trash(15); pest(3); carport(35) |
| DW11-408 | garage(200); trash(15); pest(3) |
| DW11-409 | trash(15); pest(3) |
| DW11-410 | carport(35); trash(15); pest(3) |
| DW11-411 | NaN |
I want to take all the charges, which are the numbers in parentheses, then sum them and store them in a column. So far I am using a regex findall
df['Charges'] = df['Charges'].str.findall(r"\((.+?)\)")
to extract all the numbers in parentheses. Now I have a list of numbers stored as text in each cell.
I'm getting stuck stuck on the next step, which is to convert each number string into a float and sum the list of floats for each cell in the column. The desired output should look like this:
| Unit | Charges | Summed Charges |
|---|---|---|
| DW01-100 | trash(15); pest(2) | 17 |
| DW01-101 | trash(15); pest(3) | 18 |
| DW01-102 | garage(150); trash(15); pest(3) | 168 |
So far I have tried apply like this:
def charge_sum(charge_list):
return sum([float(i) for i in charge_list])
df['Summed Charges'] = df['Charges'].apply(charge_sum)
This returns TypeError: 'float' object is not iterable.
What am I doing wrong here? I thought apply passes the cell as an argument to the charge_sum function, so the input should be a single list of strings, and then the list comprehension should convert each str to float and the return the sum. When I put added this to the function for debugging:
print(charge_list) it seems to print the entire column
['15', '2']
['15', '3']
['150', '15', '3']
['3', '15']
['15', '3']
['3', '15']
['15']
['15', '3']
['15', '3', '-101.75', '150']
['15', '3', '-88.4']
['15', '3', '-88']
['15', '3', '-89']
['3', '15']
['15', '3', '150']
['15', '2']
['15', '3']
nan
instead of just a printing the first row ['15', '2'] before erroring out. Why is the whole column being passed to the apply function at once, instead of a single cell?
In addition, how do I avoid applying the function if the cell contains nan? I suppose I could just fillna(0), but is there a better way?