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.

Solution 1

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)

Solution 2

use pandas to convert json file to excel file.

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

Comments

[Admin]

Thank you Abinash, that answers well my question.