Converting Json to Excel or CSV in Python

Question

I habe a JSon Document, which i would like to convert to Excel or CSV. My JSON File looks like this:

df = pd.DataFrame(columns=[Store,Address,User,Rating], dtype='unicode')

With following data:

 Store  |   Address  |  User   | Rating  
|:----------------------------------------:|
 Store X| Adresse X  | User 1  | 3
        |            | User 2  | 5
        |            | User 3  | 2
 Store Y| Adresse Y  | User 1  | 2
        |            | User 2  | 1
        |            | User 3  | 4
        |            | User 4  | 5

I tried following Code to convert the Json Doc to Excel:

jsonDoc = pd.read_json(df.to_json())
ExcelDoc = jsonDoc.to_excel("C:\Users\output.xlsx")

But i get following output:

 Store  |   Address  |  User                    | Rating  
|:-------------------------------------------------------:|
 Store X| Adresse X  | User 1,User2,User3       | 3,5,2
 Store Y| Adresse Y  | User 1,User2,User3,User4 | 2,1,4,5

But I would like to have my excel file like this:

 Store  |   Address  |  User   | Rating  
|:----------------------------------------:|
 Store X| Adresse X  | User 1  | 3
 Store X| Adresse X  | User 2  | 5
 Store X| Adresse X  | User 3  | 2
 Store Y| Adresse Y  | User 1  | 2
 Store Y| Adresse Y  | User 2  | 1
 Store Y| Adresse Y  | User 3  | 4
 Store Y| Adresse Y  | User 4  | 5

Can somebody help me? how I can implement this? Is there a library that can handle it?

Tyler C. · Accepted Answer · 2018-02-07 03:20:30Z

I have only been doing this for a month, but I have mainly had to work with and learn .json and .xlsx, since that is mostly what I need python for.

The main one I use and prefer is Tablib. I use Openpyxl when I need more features working on excel.

http://docs.python-tablib.org/en/master/api/

data = tablib.Dataset(headers=('Store', 'Address', 'User', 'Rating'))

There a few ways to get the data into tablib, I prefer the below method, as it will also work with Databooks(multiple datasets).

import tablib

import_filename = 'jsondatafile.json'
data.json = open(import_filename, 'r').read()

To convert it to xlsx or csv:

print(data.xlsx)
print(data.csv)

To write it to a file:

data_export = data.export('xlsx')
with open('C:/file.xlsx', 'wb') as f:  # open the xlsx file
    f.write(data_export)  # write the dataset to the xlsx file
f.close()

It is quite amazing what Tablib can do, check out their API or getting Quickstart guides.

Collectives™ on Stack Overflow

Converting Json to Excel or CSV in Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related