Extarct data from json array in python for Excel

Question

{
  "result" : [{
      "conf" : 1.000000,
      "end" : 0.300000,
      "start" : 0.000000,
      "word" : "bright"
    }, {
      "conf" : 1.000000,
      "end" : 0.720000,
      "start" : 0.330000,
      "word" : "bright"
    }, {
      "conf" : 1.000000,
      "end" : 1.950000,
      "start" : 1.710000,
      "word" : "bright"
    }],
  "text" : "bright bright bright"
}

I have this JSON array with me. I need to extract all the details from the "result" in the table format. For example,

   conf       start      end        word
1.000000    0.000000   0.300000    bright
1.000000    0.330000   0.720000    bright
1.000000    1.710000   1.950000    bright

How can I extract these values from the "result" part and append the details in excel?

bertdida · Accepted Answer · 2020-08-20 07:25:00Z

2

Using csv built-in module.

import csv

json = {
  "result" : [{
      "conf" : 1.000000,
      "end" : 0.300000,
      "start" : 0.000000,
      "word" : "bright"
    }, {
      "conf" : 1.000000,
      "end" : 0.720000,
      "start" : 0.330000,
      "word" : "bright"
    }, {
      "conf" : 1.000000,
      "end" : 1.950000,
      "start" : 1.710000,
      "word" : "bright"
    }],
  "text" : "bright bright bright"
}

header = json['result'][0].keys()
with open('results.csv', 'w', newline='') as file_:
    dict_writer = csv.DictWriter(file_, fieldnames=header)
    dict_writer.writeheader()
    dict_writer.writerows(json['result'])

answered Aug 20, 2020 at 7:25

bertdida

5,3282 gold badges19 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

oreopot · Accepted Answer · 2020-08-20 07:24:16Z

1

import pandas as pd

json_val = {
  "result" : [{
      "conf" : 1.000000,
      "end" : 0.300000,
      "start" : 0.000000,
      "word" : "bright"
    }, {
      "conf" : 1.000000,
      "end" : 0.720000,
      "start" : 0.330000,
      "word" : "bright"
    }, {
      "conf" : 1.000000,
      "end" : 1.950000,
      "start" : 1.710000,
      "word" : "bright"
    }],
  "text" : "bright bright bright"
}
pd.read_json(json_val['result'], orient='index').to_csv('someName.csv')

answered Aug 20, 2020 at 7:24

oreopot

3,4592 gold badges23 silver badges31 bronze badges

1 Comment

ml_user0993 Over a year ago

I am getting an error here "TypeError: string indices must be integers". How should I solve this?

albeksdurf · Accepted Answer · 2020-08-20 09:06:56Z

0

I would very much recommend pandas, will not take a lot of lines.

Based on your example, this is achievable with:

import pandas as pd
pd.read_json(json['result'], orient='index').to_excel('output.xlsx')

See documentation on how to create a dataset from a dictionary and how to export to Excel.

edited Aug 20, 2020 at 9:06

answered Aug 20, 2020 at 7:20

albeksdurf

1268 bronze badges

Collectives™ on Stack Overflow

Extarct data from json array in python for Excel

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related