When loading securities quote data, the price values look like this:
Where units is the integer part, nano is the fractional part.
| close | volume | time
| {'units': 270, 'nano': 190000000} | 51642 | 2023-12-29 07:30:00+00:00
| {'units': 270, 'nano': 930000000} | 47910 | 2023-12-29 08:00:00+00:00
| {'units': 271, 'nano': 0} | 49376 | 2023-12-29 08:30:00+00:00
How can I fix the "close" column so that the data looks like this?
| close | volume | time
| 270.19 | 51642 | 2023-12-29 07:30:00+00:00
| 270.93 | 47910 | 2023-12-29 08:00:00+00:00
| 271 | 49376 | 2023-12-29 08:30:00+00:00
I tried to sum the values, but nothing came out.
def cost_money(v):
return v.units + v.nano / 1e9
>Solution :
Convert dictionaries to DataFrame by json_normalize and then create new column:
v = pd.json_normalize(df['close'])
df['close'] = v.units + v.nano / 1e9
But if there are strings repr of dictionaries first convert them:
import ast
v = pd.json_normalize(df['close'].apply(ast.literal_eval))
df['close'] = v.units + v.nano / 1e9
print (df)
close volume time
0 270.19 51642 2023-12-29 07:30:00+00:00
1 270.93 47910 2023-12-29 08:00:00+00:00
2 271.00 49376 2023-12-29 08:30:00+00:00
Youe solution:
import ast
def cost_money(v):
#if necessary convert string repr to dicts
v = ast.literal_eval(v)
return v['units'] + v['nano'] / 1e9
df['close'] = df['close'].apply(cost_money)