Mapping a json file to excel with python

Question

I would like to take a json format file and map it into an xls file. i.e. in the input file

{
  "results": [
    {
      "promo_video": "https:\/\/www.youtube.com\/embed\/Tztev0Q-CN8?ps=play&vq=large&rel=0&autohide=1&showinfo=0&autoplay=1",
      "iap": true,
      "downloads": "10,000,000"
    }
  ]
}

to be presented in an excel file: column headers: promo video| iap | downloads and the respective values of each column

Would be helpful to know a recommended way and syntax example.

Thank you.

What is the specific difficulty that you're facing?

yole
– yole

2015-03-22 16:20:49 +00:00
Commented Mar 22, 2015 at 16:20 — yole
– yole, Commented Mar 22, 2015 at 16:20

Abinash Panda · Accepted Answer · 2015-03-22 17:29:10Z

8

You can use pandas module along with an excel writer engine module such as xlwt or xlsxwriter for mapping json to XLS file. For example

If your json is

[
   {"key_1":"foo1","key_2":"bar1","key_3":"foobar1"},
   {"key_1":"foo2","key_2":"bar2","key_3":"foobar2"}
]

To convert this json into XLS file

import pandas as pd
json_text = """
[
   {"key_1":"foo1","key_2":"bar1","key_3":"foobar1"},
   {"key_1":"foo2","key_2":"bar2","key_3":"foobar2"}
]
"""
df = pd.read_json(json_text)
df.to_excel('output.xls', index=False)

answered Mar 22, 2015 at 17:29

Abinash Panda

4642 silver badges7 bronze badges

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

1 Comment

Christian Over a year ago

Thank you Abinash, that answers well my question.

Dipankar Nalui · Accepted Answer · 2019-03-01 08:55:03Z

1

use pandas to convert json file to excel file.

import pandas
pandas.read_json("input.json").to_excel("output.xlsx")

answered Mar 1, 2019 at 8:55

Dipankar Nalui

1,2617 gold badges20 silver badges35 bronze badges

Collectives™ on Stack Overflow

Mapping a json file to excel with python

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related