How to format CSV data downloaded using requests library in an intuitive way?

Question

I have this code I am using to extract data from a site:

import csv
import requests

url = 'https://covid-19.dataflowkit.com/v1'
response = requests.get(url)        

with open('covid.csv', 'w') as f:
    writer = csv.writer(f)
    for line in response.iter_lines():
        writer.writerows(line.decode('utf-8').split(','))

I am able to get data out in a CSV file, but the format is wrong and confusing.

How do I format the output in a meaningful way in the CSV file?

Or how can I insert this result/data into a table in SQL server?

The reponse is a JSON. You can't just `.split(',')` that and get anything meaningful. — nog642, Jul 12 '20 at 21:31
@mkrieger1 in a csv format that makes sense, right now the data is all over the place split in cells. Also if there is a way i could ingest this data directly into sql sever database that would be ideal. Thank you for taking your time. Again i am very new and trying to navigate learning this. Additional resources would be helpful also. — user3537234, Jul 12 '20 at 21:35
You will need to write some code to clean up the data and format it according to your requirements. — mkrieger1, Jul 12 '20 at 21:36
@mkrieger1 thank you for the information, are there some resources in mind? — user3537234, Jul 12 '20 at 21:43

bigbounty · Accepted Answer · 2020-07-12T21:39:10.987

The response is a json. I would say the response itself is put in an appropriate manner.

import requests
import pandas as pd

url = 'https://covid-19.dataflowkit.com/v1'
response = requests.get(url)  
df = pd.DataFrame(response.json())
df.to_csv("data.csv", index=False)

How does the csv look like?

    Active Cases_text           Country_text       Last Update New Cases_text New Deaths_text Total Cases_text Total Deaths_text Total Recovered_text
0           4,871,695                  World  2020-07-12 20:16       +175,247          +3,530       13,008,752           570,564            7,566,493
1           1,757,520                    USA  2020-07-12 20:16        +52,144            +331        3,407,790           137,733            1,512,537
2             579,069                 Brazil  2020-07-12 19:16        +23,869            +608        1,864,681            72,100            1,213,512
3             301,850                  India  2020-07-12 19:16        +29,108            +500          879,466            23,187              554,429
4             214,766                 Russia  2020-07-12 20:16         +6,615            +130          727,162            11,335              501,061
..                ...                    ...               ...            ...             ...              ...               ...                  ...
212                 0  Caribbean Netherlands               NaN                                               7                                      7
213                 0              St. Barth               NaN                                               6                                      6
214                 0               Anguilla               NaN                                               3                                      3
215                 1  Saint Pierre Miquelon               NaN                                               2                                      1

If you want to pull out meaning from the data, then I would suggest to analyse the data on the pandas dataframe

If you want to analyse the data in a database, then you use this answer - https://stackoverflow.com/a/25662997/6849682 for sql server

How to format CSV data downloaded using requests library in an intuitive way?

1 Answers1