Making a spreadsheet out of JSON data using DataFrame from pandas

Question

There's a few posts like this already, but after following them I still encounter some issues.

trade = client.get_my_trades(symbol=ticker)  # fetching all trades made on the ticker IOSTBTC
print(trade)
json_trade = json.dumps(trade, indent=4)  # converting and indenting for easier readability.
print(json_trade+"\n")
json_normalised = json_normalize(trade)  # normalising with pandas for spreadsheet use
print("Normalised JSON\n", json_normalised)

json_normalised = DataFrame(pandas.read_json("logs.xlsx"))
json_normalised_str = str(json_normalised)
logs = open("logs.xlsx", "w")  # creating file to write to
logs.write(json_normalised_str)  # writing data to file, oldest first

This code runs fine, with no erros. However, when I check the logs.xlsx, all the data is in a single column, with spaces in between where they should be separated by columns.

For example, here's some of the JSON data:

[{'id': 3084149, 'orderId': 7071890, 'price': '0.00000312', 'qty': '400.00000000', 'commission': '0.00041327', 'commissionAsset': 'BNB', 'time': 1522223234240, 'isBuyer': True, 'isMaker': True, 'isBestMatch': True}, {'id': 3084468, 'orderId': 7073272, 'price': '0.00000314', 'qty': '400.00000000', 'commission': '0.00041694', 'commissionAsset': 'BNB', 'time': 1522223910252, 'isBuyer': False, 'isMaker': True, 'isBestMatch': True}]

What I want is for 'id' and 'orderId' and 'price' (et cetera) to have it's own column. With the above data I would have two rows of information. But instead, this is what I recieve when I use this data:

What can I do?

DeepSpace · Accepted Answer · 2018-04-03 09:38:19Z

2

You can't use open("logs.xlsx", "w") to create proper xlsx files since it will write raw text (or bytes if using wb) to the file. xlsx are more complex than that.

Instead, simply use pandas.DataFrame.from_dict:

data = [{'id': 3084149, 'orderId': 7071890, 'price': '0.00000312', 'qty': '400.00000000', 'commission': '0.00041327', 'commissionAsset': 'BNB', 'time': 1522223234240, 'isBuyer': True, 'isMaker': True, 'isBestMatch': True}, {'id': 3084468, 'orderId': 7073272, 'price': '0.00000314', 'qty': '400.00000000', 'commission': '0.00041694', 'commissionAsset': 'BNB', 'time': 1522223910252, 'isBuyer': False, 'isMaker': True, 'isBestMatch': True}]

df = pd.DataFrame.from_dict(data)
print(df)

#         commission commissionAsset       id  isBestMatch  isBuyer  isMaker \
#      0  0.00041327             BNB  3084149         True     True     True   
#      1  0.00041694             BNB  3084468         True    False     True   

#       orderId       price           qty           time  
#    0  7071890  0.00000312  400.00000000  1522223234240  
#    1  7073272  0.00000314  400.00000000  1522223910252

Then exporting to a spreadsheet is as trivial as calling to_excel:

df.to_excel('logs.xlsx')

And if you don't like the indexes to be exported to the spreadsheet you can use index=False: df.to_excel('logs.xlsx', index=False). See the docs for more info.

edited Apr 3, 2018 at 9:38

answered Apr 3, 2018 at 9:30

DeepSpace

82.1k12 gold badges119 silver badges166 bronze badges

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

3 Comments

deckador Over a year ago

The first part works great, but I can't seem to write to the excel doc using df.to_excel. I get this error: ModuleNotFoundError: No module named 'openpyxl'.

DeepSpace Over a year ago

@deckador You will either need to install openpyxl using pip or try to use a different engine ie df.to_excel('logs.xlsx', engine='xlsxwriter')

deckador Over a year ago

installing openpyxl worked! Thank you so much. The spreadsheet looks like yours now. Again, cheers.

Collectives™ on Stack Overflow

Making a spreadsheet out of JSON data using DataFrame from pandas

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related