1

I've been trying to understand how to work with Website APIs and how to use get methods to download data from the website.

At the moment I have a big dictionary that I'm trying to write to file.

When I output it to a file it looks like this:

 {"Tickets":[{"Project":"Test_Project", "TicketID":"1", "Title":"Title_1", "Reportedby":"User_1"},{"Project":"Test_Project", "TicketID":"2", "Title":"Title_2", "Reportedby":"User_2"}]}

How can I output this to a Excel file?

I've tried using all kinds of string formatting, like:

response_string = response_string[response_string.find("["):len(response_string)-1]
response_string = response_string[1:len(response_string)-1]
response_string = response_string.replace("},{","} , {")
response_array = response_string.split(" , ") + response_array

but I know this is not the right way as a comma inside of the text would really mess this up.

Thanks, A.

deadshot
  • 8,881
  • 4
  • 20
  • 39
Alin G.
  • 45
  • 6
  • 3
    Perhaps create a pandas dataframe and save as excel/csv? – ilyankou May 11 '20 at 06:36
  • Does this answer your question? [Export a simple Dictionary into Excel file in python](https://stackoverflow.com/questions/28555112/export-a-simple-dictionary-into-excel-file-in-python) – deadshot May 11 '20 at 06:40
  • The best thing is to use Pandas DataFrame and then save it to excel. But if you would not like to use it this may be helping you. https://pythonspot.com/save-a-dictionary-to-a-file/ – Dejene T. May 11 '20 at 06:51
  • Does this answer your question? [Python Dictionary to CSV](https://stackoverflow.com/questions/8331469/python-dictionary-to-csv) – Grismar May 11 '20 at 07:14

2 Answers2

2

If you want to save a dictionary to a csv file, the basic method is to use the csv module. The assumption is that you have a list of dictionaries, with keys as the column names and values you want to save. 

import csv

data = {"Tickets":[{"Project":"Test_Project", "TicketID":"1", "Title":"Title_1", "Reportedby":"User_1"},{"Project":"Test_Project", "TicketID":"2", "Title":"Title_2", "Reportedby":"User_2"}]}

#Write a CSV file. 
f = open('temp','w')
c = csv.DictWriter(f,data['Tickets'][0].keys())
c.writeheader()
c.writerows(data['Tickets'])
f.close()

#Read a CSV file. 
f = open('temp','r')
c = csv.DictReader(f)
data = {'Tickets':list(c)}
f.close()
Bobby Ocean
  • 3,120
  • 1
  • 8
  • 15
  • This seems like what I'm looking for but is there any way to write to .xlsx instead of .csv? – Alin G. May 11 '20 at 07:00
  • Not really, a CSV file has nothing to do with excel. It is a Comma-Separated-Values file. Excel opens this with ease. In excel, covert the file to an xlsx, by choosing save-as. The main difference is that CSV can be opened with any editor and read easily because it is stings and commas, but a xlsx file is a collection of compressed XML files used by Microsoft products. Try to open an .xlsx file with an editor like notepad or gedit. – Bobby Ocean May 11 '20 at 07:03
  • There could be some module out there that knows how to create the compressed stacks of XML files for a xlsx format. But I don't know off the top of my head. – Bobby Ocean May 11 '20 at 07:04
1

A much easier way to save dictionaries or any other files is joblib from sklearn.externals.

You can do this there in a single line.

joblib.dump('filename', dict_name)

https://scikit-learn.org/stable/modules/model_persistence.html

Abhishek Verma
  • 1,671
  • 1
  • 8
  • 12