How to create a dataframe from a nested JSONField in Django?

Florent 0
Viewed 21

My Django model has a DateTimeField and a JSONField fields I would like to create a timeseries dataframe with.

The package django-pandas has a method to_timeseries to do that but my problem is that it puts all the JSONField field into a column. How can I flatten this column into multiindexed columns ?

models.py

class Indicator(models.Model):
    dt = models.DateTimeField(null=True)
    metrics = models.JSONField(default=dict)

JSONField dictionary:

{'housing': {'1d_percent': 73.62755998, '2d_percent': 3e-08},
 'fund-flower': {'ratio': 0.01981295},
 'mpi': {'mpi': -0.6527736158660562}}

Converting queryset to a timeseries dataframe :

>> qs = Indicator.objects.all()
>> df = qs.to_timeseries(index='dt', fieldnames='metrics').sort_index().dropna()
>> df

                                                                    metrics
dt                                                                          
2018-01-01 00:00:00+00:00  {'mpi': {'mpi': -0.01679772442974948}, 'fund-f...
2018-01-02 00:00:00+00:00  {'mpi': {'mpi': 1.1785319016689795}, 'fund-flo...
2018-01-03 00:00:00+00:00  {'mpi': {'mpi': 1.047678402830424}, 'fund-flow...
2018-01-04 00:00:00+00:00  {'mpi': {'mpi': 1.111703887319459}, 'fund-flow...
2018-01-05 00:00:00+00:00  {'mpi': {'mpi': 2.3908629334035343}, 'fund-flo...
                                                                      ...
2022-09-17 00:00:00+00:00  {'mpi': {'mpi': -1.0434999082318062}, 'fund-fl...
2022-09-18 00:00:00+00:00  {'mpi': {'mpi': -0.9680468633746766}, 'fund-fl...
2022-09-19 00:00:00+00:00  {'mpi': {'mpi': -0.9287818619840235}, 'fund-fl...
2022-09-20 00:00:00+00:00  {'mpi': {'mpi': -0.8487296227267782}, 'fund-fl...

This is the desired output:

                            mpi            fund-flower               housing
dt                          mpi            ratio                     1d_percent    2d_percent                                                                        
2018-01-01 00:00:00+00:00   value          value                     value         value
2018-01-02 00:00:00+00:00   value          value                     value         value  
2018-01-03 00:00:00+00:00   value          value                     value         value  
2018-01-04 00:00:00+00:00   value          value                     value         value  
2018-01-05 00:00:00+00:00   value          value                     value         value  
                                                                      ...
2022-09-17 00:00:00+00:00   value          value                     value         value  
2022-09-18 00:00:00+00:00   value          value                     value         value  
2022-09-19 00:00:00+00:00   value          value                     value         value  
2022-09-20 00:00:00+00:00   value          value                     value         value

I have tried to normalize data with json_normalize as mentioned here but it throws an error:

>> pd.json_normalize(df, record_path =['metrics'])
TypeError: string indices must be integers
pandas django python
1 Answers

I finally found the answer to my own question.

df = pd.DataFrame()
for obj in qs:
    tmp = pd.DataFrame.from_dict(obj.metrics, orient="columns").stack().to_frame().T
    df = pd.concat([df, m])

df.index = qs.values_list('dt')
Florent0
Related