I'm getting this JSON string:
[{'open': 13.739999771118164, 'high': 13.739999771118164, 'low': 13.380000114440918, 'close': 13.460000038146973, 'time': {'day': 6, 'month': 6, 'year': 2022}}, {'open': 13.260000228881836, 'high': 13.770000457763672, 'low': 13.1899995803833, 'close': 13.739999771118164, 'time': {'day': 7, 'month': 6, 'year': 2022}}, {'open': 13.630000114440918, 'high': 13.850000381469727, 'low': 13.4399995803833, 'close': 13.529999732971191, 'time': {'day': 8, 'month': 6, 'year': 2022}}, {'open': 13.510000228881836, 'high': 13.59000015258789, 'low': 13.279999732971191, 'close': 13.279999732971191, 'time': {'day': 9, 'month': 6, 'year': 2022}}, {'open': 13, 'high': 13.210000038146973, 'low': 12.630000114440918, 'close': 12.75, 'time': {'day': 10, 'month': 6, 'year': 2022}}, {'open': 12.300000190734863, 'high': 12.380000114440918, 'low':
11.739999771118164, 'close': 11.8100004196167, 'time': {'day': 13, 'month': 6, 'year': 2022}}, {'open': 11.989999771118164, 'high': 12.420000076293945, 'low': 11.90999984741211, 'close': 12.199999809265137, 'time': {'day': 14, 'month': 6, 'year': 2022}}, {'open': 12.220000267028809, 'high': 12.420000076293945, 'low': 12, 'close': 12.270000457763672, 'time': {'day': 15, 'month': 6, 'year': 2022}}, {'open': 11.800000190734863, 'high': 11.90999984741211, 'low': 11.119999885559082, 'close': 11.25, 'time': {'day': 16, 'month': 6, 'year': 2022}}, {'open': 11.239999771118164, 'high': 11.4399995803833, 'low': 10.899999618530273, 'close': 11.229999542236328, 'time': {'day': 17, 'month': 6, 'year': 2022}}, {'open': 11.550000190734863, 'high': 11.65999984741211, 'low': 11.350000381469727, 'close': 11.460000038146973, 'time': {'day': 21, 'month': 6, 'year': 2022}}, {'open': 11.260000228881836, 'high': 11.680000305175781, 'low': 11.210000038146973, 'close': 11.479999542236328, 'time': {'day': 22, 'month': 6, 'year': 2022}}, {'open': 11.5, 'high': 11.579999923706055, 'low': 11.25, 'close': 11.5600004196167, 'time': {'day': 23, 'month': 6, 'year': 2022}}, {'open': 11.649999618530273, 'high': 12.119999885559082, 'low': 11.569999694824219, 'close': 12.010000228881836, 'time': {'day': 24, 'month': 6, 'year': 2022}}, {'open': 12.119999885559082, 'high': 12.180000305175781, 'low': 11.899999618530273, 'close': 12.039999961853027, 'time': {'day': 27, 'month': 6, 'year': 2022}}, {'open': 12.140000343322754, 'high': 12.4399995803833, 'low': 11.800000190734863, 'close': 11.8100004196167, 'time': {'day': 28, 'month': 6, 'year': 2022}}, {'open': 11.739999771118164, 'high': 11.760000228881836, 'low': 11.380000114440918, 'close': 11.520000457763672, 'time': {'day': 29, 'month': 6, 'year': 2022}}, {'open': 11.239999771118164, 'high': 11.359999656677246, 'low': 10.930000305175781, 'close': 11.130000114440918, 'time': {'day': 30, 'month': 6, 'year': 2022}}, {'open': 11.100000381469727, 'high': 11.5, 'low': 10.989999771118164, 'close': 11.319999694824219, 'time': {'day': 1, 'month': 7, 'year': 2022}}]
I'm converting it to pandas dataframe the following way:
candleData = pd.DataFrame(json.loads(request.GET.get('candleData')), columns=['open','high','low','close','time'])
Getting this results:
open high low close time
0 13.74 13.74 13.38 13.46 {'day': 6, 'month': 6, 'year': 2022}
1 13.26 13.77 13.19 13.74 {'day': 7, 'month': 6, 'year': 2022}
2 13.63 13.85 13.44 13.53 {'day': 8, 'month': 6, 'year': 2022}
3 13.51 13.59 13.28 13.28 {'day': 9, 'month': 6, 'year': 2022}
4 13.00 13.21 12.63 12.75 {'day': 10, 'month': 6, 'year': 2022}
5 12.30 12.38 11.74 11.81 {'day': 13, 'month': 6, 'year': 2022}
6 11.99 12.42 11.91 12.20 {'day': 14, 'month': 6, 'year': 2022}
...
Now, what I want to do and I don't know how is making the column time out of its nested list making it a real date like this:
open high low close time
0 13.74 13.74 13.38 13.46 6-6-2022
1 13.26 13.77 13.19 13.74 7-6-2022
2 13.63 13.85 13.44 13.53 8-6-2022
3 13.51 13.59 13.28 13.28 9-6-2022
4 13.00 13.21 12.63 12.75 10-6-2022
5 12.30 12.38 11.74 11.81 13-6-2022
6 11.99 12.42 11.91 12.20 14-6-2022
...
CodePudding user response：
I see two options (see second part for the format).
Convert the time Series to DataFrame with pandas.json_normalize, then to datetime with pandas.to_datetime:
candleData['time'] = pd.to_datetime(pd.json_normalize(candleData['time']))
Or, apply to convert to pandas.Timestamp, expanding the dictionary as parameters:
candleData['time'] = candleData['time'].apply(lambda x: pd.Timestamp(**x))
NB. in both cases it is important to have day / month / years as dictionary keys!
output:
open high low close time
0 13.74 13.74 13.38 13.46 2022-06-06
1 13.26 13.77 13.19 13.74 2022-06-07
2 13.63 13.85 13.44 13.53 2022-06-08
3 13.51 13.59 13.28 13.28 2022-06-09
4 13.00 13.21 12.63 12.75 2022-06-10
5 12.30 12.38 11.74 11.81 2022-06-13
6 11.99 12.42 11.91 12.20 2022-06-14
7 12.22 12.42 12.00 12.27 2022-06-15
8 11.80 11.91 11.12 11.25 2022-06-16
9 11.24 11.44 10.90 11.23 2022-06-17
10 11.55 11.66 11.35 11.46 2022-06-21
11 11.26 11.68 11.21 11.48 2022-06-22
12 11.50 11.58 11.25 11.56 2022-06-23
13 11.65 12.12 11.57 12.01 2022-06-24
14 12.12 12.18 11.90 12.04 2022-06-27
15 12.14 12.44 11.80 11.81 2022-06-28
16 11.74 11.76 11.38 11.52 2022-06-29
17 11.24 11.36 10.93 11.13 2022-06-30
18 11.10 11.50 10.99 11.32 2022-07-01
d-m-YYYY string format:
Add .dt.strftime('%-d-%-m-%Y') to you command:
candleData['time'] = (pd.to_datetime(pd.json_normalize(candleData['time']))
.dt.strftime('%-d-%-m-%Y')
)
output:
open high low close time
0 13.74 13.74 13.38 13.46 6-6-2022
1 13.26 13.77 13.19 13.74 7-6-2022
2 13.63 13.85 13.44 13.53 8-6-2022
3 13.51 13.59 13.28 13.28 9-6-2022
4 13.00 13.21 12.63 12.75 10-6-2022
5 12.30 12.38 11.74 11.81 13-6-2022
6 11.99 12.42 11.91 12.20 14-6-2022
7 12.22 12.42 12.00 12.27 15-6-2022
8 11.80 11.91 11.12 11.25 16-6-2022
9 11.24 11.44 10.90 11.23 17-6-2022
10 11.55 11.66 11.35 11.46 21-6-2022
11 11.26 11.68 11.21 11.48 22-6-2022
12 11.50 11.58 11.25 11.56 23-6-2022
13 11.65 12.12 11.57 12.01 24-6-2022
14 12.12 12.18 11.90 12.04 27-6-2022
15 12.14 12.44 11.80 11.81 28-6-2022
16 11.74 11.76 11.38 11.52 29-6-2022
17 11.24 11.36 10.93 11.13 30-6-2022
18 11.10 11.50 10.99 11.32 1-7-2022
CodePudding user response：
You can try something like this :
candleData.time.apply(lambda x: f"{x['day']}-{x['month']}-{x['year']}")