convert dataframe to json including index column-CodePudding

This sounds an easy question but I am not getting my desire output in json file.

I am trying to convert my dataframe to json after I get result from anomaly detector.

Data frame contains following columns including index coulumn as datetime.

index column - datetime,
loss,
rolling_mean,
exponential_mean
static_threshold, 
dynamic_threshold, 
anomlay,
global anomaly,
total value

I am trying to convert these dataframe columns to json file by using to_json.But it doesn't give me my index column in json file though I used orient.

my code:

#anomalies is a dataframe which has all the above columns

result = anomalies.to_json(orient='index')
parsed = json.loads(result)
json.dumps(parsed, indent=4)
print(parsed)  
#print(result)

output

{'1637593200000': {'loss': 0.2777269163, 
                   'rolling_mean': 0.3497074445,  
                   'exp_mean': 0.2994830801, 
                   'static_threshold': 0.2707094526, 
                   'dynamic_threshold': 0.3497074445, 
                   'global_anomaly': False, 
                   'anomaly': True, 
                   'total': 0.2090592334}, 
'1637596800000': {'loss': 0.2816397219, 
                  'rolling_mean': 0.3550902968, 
                  'exp_mean': 0.3079049915, 
                  'static_threshold': 0.2707094526, 
                  'dynamic_threshold': 0.3550902968, 
                  'global_anomaly': False, 
                  'anomaly': True, 
                  'total': 0.0818815331}}

What I want is:

[{'datetime':20220105,
  'loss': 0.2777269163, 
  'rolling_mean': 0.3497074445,  
  'exp_mean': 0.2994830801, 
  'static_threshold': 0.2707094526, 
  'dynamic_threshold': 0.3497074445, 
  'global_anomaly': False, 
  'anomaly': True, 
  'total': 0.2090592334}, 
 {'datetime':20220105,
  'loss': 0.2816397219, 
  'rolling_mean': 0.3550902968, 
  'exp_mean': 0.3079049915, 
  'static_threshold': 0.2707094526, 
  'dynamic_threshold': 0.3550902968, 
  'global_anomaly': False, 
  'anomaly': True, 
  'total': 0.0818815331}]

CodePudding user response：

The pandas DataFrame has a function to convert index to column. It is .reset_index().

If you use this function with orient='records' in .to_json(), then you can solve it.

This is my solution.

result = anomalies.reset_index().to_json(orient='records', indent=4)
print(result)