How to extract API data in a CSV format in Python

Question

I am pulling data from an API endpoint. My response looks like this:

b'col1;col2;col3;a1;a2;a3\r\nb1;b2;b3\r\n'

I want to format the response to look like this:

col1  col2  col3
 a1    a2    a3
 b1    b2    b3

To complicate things further, this is the response for one input in my list. I need this to loop through and repeat through multiple inputs.

This is what I've tried so far:

dict = {}
dict[1] = "input1"
dict[2] = "input2"
base_url = "ex.com/?key=abc&input={}"
df = pandas.read_csv('out.csv', Header = None) 

for input in dict:
    url1=base_url.format(input)
    response1 = requests.get(url1)

Now I know the for loop is incomplete - not sure which function to use to append the data and make it in the right format. I'm new to Python - even if you direct me to the right function to research, that would help.

`for input in dict`... http://stackoverflow.com/questions/3294889/iterating-over-dictionaries-using-for-loops-in-python — OneCricketeer, Dec 23 '16 at 16:39
Do you really need a dictionary? What's wrong with a regular list? — OneCricketeer, Dec 23 '16 at 16:39
Well I don't care if it's a dictionary or a list. It was just an example to show that it has to iterate through multiple inputs. More than the iteration piece, I want to figure out how to format the api response in the tabular setup I've portrayed above. — SizzyNini, Dec 23 '16 at 16:42
It doesn't appear you've tried to do anything with the response. What is `out.csv`? What are you planning to with `df`? Try looking into the `split()` string function — OneCricketeer, Dec 23 '16 at 16:45
out.csv is a file with headers (of my response data) that I created. I was hoping to use some kind of append function in the for loop that would append the response to the out.csv file. — SizzyNini, Dec 23 '16 at 16:56
How are you suppose to separate `col1;col2;col3;a1;a2;a3`? Are you guaranteed there are three columns? Or should there be a `\r\n` in there? — OneCricketeer, Dec 23 '16 at 17:13

score 1 · Answer 1 · answered Dec 23 '16 at 17:29

I think there should be a line break within col1;col2;col3;a1;a2;a3

But if not, here is a possible solution

s = b'col1;col2;col3;a1;a2;a3\r\nb1;b2;b3\r\n'.decode('utf-8')

table = []
cols = 3
for i, row in enumerate(s.split()):
  data = row.split(';')

  # If first line, or data is longer than specified columns
  if i == 0 or len(data) > cols:
    # Try to take column sized chunks
    table.append('\t'.join(data[:cols]))
    table.append('\t'.join(data[cols:]))
  else:
    table.append('\t'.join(data))

print('\n'.join(table))

Output

col1    col2    col3
a1      a2      a3
b1      b2      b3

As far as looping over the dictionary goes, you only want the values, I assume, so just loop over a list. I'm not familiar enough with pandas, but you can keep appending rows to this table list within a loop and you can worry about getting a dataframe or outputting to a file later.

I'll try this and let you know how it goes! Thanks for your help. — SizzyNini, Dec 23 '16 at 18:02
Welcome. I tried to handle the case of adding an `a4`, for example, but if you are guaranteed 3 columns, then this should be fine. — OneCricketeer, Dec 23 '16 at 18:07

score 0 · Answer 2 · answered Dec 23 '16 at 18:44

Consider StringIO to read text content to data frame with read_table, specifying semicolon delimiter. Below assumes response's line breaks fall between columns including headers: b'col1;col2;col3\r\na1;a2;a3\r\nb1;b2;b3\r\n'. Also, each data frame is appended to list and concatenated together at end for final dataframe:

from io import StringIO
import pandas as pd 

inputList = ['input1', 'input2']
base_url = "ex.com/?key=abc&input={}"
dfList = []    

for input in inputList: 
    url1 = base_url.format(input) 
    response1 = requests.get(url1)
    temp = pd.read_table(StringIO(response1.decode('utf-8')), sep=';')
    dfList.append(temp)

finaldf = pd.concat(dfList)

How to extract API data in a CSV format in Python

2 Answers2