How to transform an API to csv

Q&A Non-DQ Courses
#1

Hi there,

I’m trying to transform this file this API https://bioportal.salud.gov.pr/api/administration/reports/minimal-info-unique-tests into a csv/excel table. I think the original file is a dictionary. This is what I did but I’m stuck:

import requests
import csv
import pandas as pd

result = requests.get(‘https://bioportal.salud.gov.pr/api/administration/reports/minimal-info-unique-tests’)
result.status_code
200

I don’t know what to do next :frowning:

#2

hey @santamariaots

Welcome to DQ Community!

You can refer to this post for detailed workflow here

Please note the above post has an extra dictionary key-value pair as a parent dictionary.

I took help from this post and tried for the first 5 rows of the API given. I was able to create a “.csv” file however there’s a line space after every entry :confused: :thinking:

import requests
import csv
import pandas as pd
import json       # added code

result = requests.get('https://bioportal.salud.gov.pr/api/administration/reports/minimal-info-unique-tests')
print(result.status_code)

# copied from the post
data_file = open('test_results.csv', 'w')  
csv_writer = csv.writer(data_file)   

header = test_result.keys() 
csv_writer.writerow(header) 

for test_result in result.json()[:5]:
    csv_writer.writerow(test_result.values()) 
  
data_file.close() 

# code ends here
1 Like
#3

Hey! Thank you so much Rucha. The code worked perfectly but as you said generates a blank row between entry. Is there a way to eliminate it with Python code? I’m doing it with excel (the csv converted file) but it takes forever due to magnitude of the file.
And thank you again for the tip :slightly_smiling_face:

1 Like
#4

hi @santamariaots

This is a surprise coz I re-tried the code again today, and gave me an error here:
header = test_result.keys() I must have jumbled up this code. Apologies.

About the empty row in “.csv” file, I tried this and it seems to work.

data_file = open('test_results.csv', 'w', newline = '')

There is no space between the quotes. The quotes are one after the other. Let me know if this works.

1 Like
#5

It works! Thank you Rucha!! :grinning: