How to save Python output result in csv

Question

I have below code where i am connecting to JIRA rest api using Python which is working fine. Now i want to save this response into csv file but dont know how to do it. I am quiet new in Python code.

import requests
import json
import base64

# Base encode email and api token
cred =  "Basic " + base64.b64encode(b'XXXX:XXXX').decode("utf-8")
# Set header parameters
headers = {
   "Accept": "application/json",
   "Content-Type": "application/json",
   "Authorization" : cred
}

# Enter your project key here
projectKey = "TRT"

# Update your site url
url = "https://jira-test./rest/api/latest/search?jql=project%20%3D%20" + projectKey

# Send request and get response
response = requests.request(
   "GET",
   url,
   headers=headers
)

# Decode Json string to Python
json_data = json.loads(response.text)

# Display issues
for item in json_data["issues"]:
    print(item["id"] + "\t" + item["key"] + "\t" +
        item["fields"]["issuetype"]["name"] + "\t" +
        item["fields"]["created"]+ "\t" +
        item["fields"]["creator"]["displayName"] + "\t" +
        item["fields"]["status"]["name"] + "\t" +
        item["fields"]["summary"] + "\t"
        )

Below is how the output looks like:

330479  OO-27  Ad-hoc  2021-10-14T09:29:41.000+0200   TST Backlog Testing the Report
330480  OO-28  Ad-hoc  2021-10-14T09:29:41.000+0200   TST Backlog Testing the Report
330481  OO-29  Ad-hoc  2021-10-14T09:29:41.000+0200   TST Backlog Testing the Report

first create list with all rows and later use module cvs or pandas to save it. — furas
– furas, Commented Oct 25, 2021 at 12:56
if you display it on screen then you can redirect outout in console using python script.py > output.csv and it will send to file all printed text . — furas
– furas, Commented Oct 25, 2021 at 12:58
i tried with second approach but everything is exported in one column...can you please tell how to create separate columns and export data in this.. — Marcus
– Marcus, Commented Oct 25, 2021 at 13:30
if you display with \t as separator then you have to load CSV also with \t as separator. ie. pd.read_csv(..., sep='\t'). (normal CSV uses comma as separator - and this is why it has name Comma Separated Values) — furas
– furas, Commented Oct 25, 2021 at 14:37

Tõnis Piip · Accepted Answer · 2021-10-26 12:53:31Z

2

You could try converting the dict/json to a pandas DataFrame and then use DataFrame.to_csv()

import pandas as pd

df = pd.DataFrame.from_dict(json_data)

# r"..." mean raw string, which basically means the string as is written, no need to escape backslashes or other special character Python might interpret differently

custom_path = r'C:\Users\username\Desktop\'

# rf"..." is raw string with formatting (text inside curly brackets {} is interpreted as Python variables)

df.to_csv(rf"{custom_path}name_of_your_file_custom.csv")

# saving to whichever folder the python code is run from is simply giving the file a name

df.to_csv("name_of_your_file_current_directory.csv")

Or if the dict isn't in the desired format yet, you could manually create a csv file and append lines to it:

# here you should also be able to give a full path as such

# custom_path = r"C:\Users\username\Desktop\"
# with open(rf"{custom_path}file.csv") as file_to_append:

with open("file.csv", "a") as file_to_append_to:
    file_to_append_to.write("id,key,issuetype,...") #first line is the columns, such as "id,name,address,whatever"
    for item in json_data:
        item_line = f'{item["id"]},{item["key"]},{item["fields"]["issuetype"]["name"]}...'
        file_to_append_to.write(item_line)

edited Oct 26, 2021 at 12:53

answered Oct 25, 2021 at 12:55

Tõnis Piip

4923 silver badges9 bronze badges

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

4 Comments

Marcus Over a year ago

can you please explain in the first case as the variable dict_var is not defined it will throw an error...in second case for line second do i have to mention headers in csv file ? And if i want to create csv in specific location ?

Marcus Over a year ago

in second solution also variable json_file not defined ...can you please update the answer accordingly with respect to question ? thanks

Marcus Over a year ago

i am getting error item_line = f'{item["id"]},{item["key"]},{item["fields"]["issuetype"]["name"]}' TypeError: string indices must be integers

Tõnis Piip Over a year ago

@Andrew I edited the code part to fit the data given in the question. I don't know enough to help with the TypeError problem though, sorry.

westandskif · Accepted Answer · 2021-10-25 17:05:22Z

Have a look at convtools library, it is lightweight and it contains a lot of data processing primitives.

from convtools import conversion as c
from convtools.contrib.tables import Table

# please, provide the input data next time :)
input_data = {
    "issues": [
        {
            "id": 330479,
            "key": "OO-27",
            "fields": {
                "issuetype": {
                    "name": "Ad-hoc",
                },
                "created": "2021-10-14T09:29:41.000+0200",
                "creator": {"displayName": "TST"},
                "status": {"name": "Backlog"},
                "summary": "Testing the Report",
            },
        },
    ]
}

# define the schema
schema = {
    "id": c.item("id"),
    "key": c.item("key"),
    "name": c.item("fields", "issuetype", "name"),
    "created": c.item("fields", "created"),
    "creator": c.item("fields", "creator", "displayName"),
    "status": c.item("fields", "status", "name"),
    "summary": c.item("fields", "summary"),
}

# here we process every issue, results in iterable of tuples
converter = c.item("issues").iter(tuple(schema.values())).gen_converter()

# using schema keys as column names and processed rows as data
Table.from_rows(converter(input_data), header=list(schema)).into_csv(
    "output.csv", dialect=Table.csv_dialect(delimiter="\t")
)

dasfacc · Accepted Answer · 2021-10-25 12:59:05Z

0

import pandas as pd
issues = json_data["issues"]
data = {"id": [item["id"] for item in issues], 
        "key": [item["key"] for item in issues],
....
}
df = pd.DataFrame(data=data)
df.to_csv("tabseparated.csv", sep="\t")

answered Oct 25, 2021 at 12:59

dasfacc

1742 silver badges9 bronze badges

2 Comments

Marcus Over a year ago

in one column it has exported data in csv...how to create separate columns ?

Christopher Hamkins Over a year ago

Posting code in an answer is important, but adding a few lines of text to explain what you did and why it solves the problem is a big help too.

Collectives™ on Stack Overflow

How to save Python output result in csv

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related