Python - How to add delimiter and remove line breaks in CSV output?

Question

I am doing this for the first time and so far have setup a simple script to fetch 2 columns of data from an API
The data comes through and I can see it with print command
Now I am trying to write it to CSV and setup the code below which creates the file but I can't figure out how to:

1. Remove the blank lines in between each data row
2. Add delimiters to the data which I want to be " "
3. If a value such as IP is blank then just show " "

I searched and tried all sorts of examples but just getting errors

My code snippet which writes the CSV successfully is

import requests
import csv
import json

# Make an API call and store response
url = 'https://api-url-goes-here.com'
filename = "test.csv"

headers = {
    'accept': 'application/json',
}

r = requests.get(url, headers=headers, auth=('User','PWD'))
print(f"Status code: {r.status_code}")

#Store API response in a variable
response_dict = r.json()

#Open a File for Writing
f = csv.writer(open(filename, "w", encoding='utf8'))

# Write CSV Header
f.writerow(["Computer_Name", "IP_Addresses"])

for computer in response_dict["advanced_computer_search"]["computers"]:
    f.writerow([computer["Computer_Name"],computer["IP_Addresses"]])

CSV output I get looks like this:

Computer_Name,IP_Addresses

HYDM002543514,

HYDM002543513,10.93.96.144 - AirPort - en1

HYDM002544581,192.168.1.8 - AirPort - en1 / 10.93.224.177 - GlobalProtect - gpd0

HYDM002544580,10.93.80.101 - Ethernet - en0

HYDM002543515,192.168.0.6 - AirPort - en0 / 10.91.224.58 - GlobalProtect - gpd0

CHAM002369458,10.209.5.3 - Ethernet - en0

CHAM002370188,192.168.0.148 - AirPort - en0 / 10.125.91.23 - GlobalProtect - gpd0

MacBook-Pro,

I tried adding

csv.writer(f, delimiter =' ',quotechar =',',quoting=csv.QUOTE_MINIMAL)

after the f = csv.writer line but that creates an error:
TypeError: argument 1 must have a "write" method

I am sure its something simple but just can't find the correct solution to implement in the code I have. Any help is appreciated.

Also, does the file get closed automatically? Some examples suggest to use something like f.close() but that causes errors. Do I need it? The file seems to get created fine as-is.

Answer 1

I suggest you use pandas package to write .csv file, which is a most used package for data analysis.

For your problem:

import requests
import csv
import json
import pandas

# Make an API call and store response
url = 'https://api-url-goes-here.com'
filename = "test.csv"

headers = {
    'accept': 'application/json',
}

r = requests.get(url, headers=headers, auth=('User','PWD'))
print(f"Status code: {r.status_code}")

#Store API response in a variable
response_dict = r.json()

#collect data to build pandas.DataFrame
data = []
for computer in response_dict["advanced_computer_search"]["computers"]:
    # filter blank line
    if computer["Computer_Name"] or computer["IP_Addresses"]:
        data.append({"Computer_Name":computer["Computer_Name"],"IP_Addresses":computer["IP_Addresses"]})

pandas.DataFrame(data=data).to_csv(filename, index=False)

if you want use " " to separate value, you can set sep=" " in the last line output the .csv file. However, I recommend to use , as delimiters due to it's a common standard. Also much more configs could be set for DataFrame.to_csv() method, you can check the official docs. https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_csv.html

As you said in comment, pandas is not a standard python package. You can simply open a file and write lines to that file, with the lines you build manually. For example:

import requests
import csv
import json

# Make an API call and store response
url = 'https://api-url-goes-here.com'
filename = "test.csv"

headers = {
    'accept': 'application/json',
}

r = requests.get(url, headers=headers, auth=('User','PWD'))
print(f"Status code: {r.status_code}")

#Store API response in a variable
response_dict = r.json()

r = requests.get(url, headers=headers, auth=('User','PWD'))
print(f"Status code: {r.status_code}")

#Store API response in a variable
response_dict = r.json()

#Open a File for Writing
f = csv.writer(open(filename, "w", encoding='utf8'))

with open(filename, mode='w') as f:
    # Write CSV Header
    f.write("Computer_Name,"+"IP_Addresses"+"\n")
    for computer in response_dict["advanced_computer_search"]["computers"]:
        # filter blank line
        if computer["Computer_Name"] or computer["IP_Addresses"]:
            f.write("\""+computer["Computer_Name"]+"\","+"\""+computer["IP_Addresses"]+"\"\n")

Note that " around value was build by appending \". \n to change new line after each loop.