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... stackoverflow.com/questions/3294889/… — OneCricketeer
– OneCricketeer, Commented Dec 23, 2016 at 16:39
Do you really need a dictionary? What's wrong with a regular list? — OneCricketeer
– OneCricketeer, Commented Dec 23, 2016 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
– SizzyNini, Commented Dec 23, 2016 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
– OneCricketeer, Commented Dec 23, 2016 at 16:45
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
– OneCricketeer, Commented Dec 23, 2016 at 17:13

OneCricketeer · Accepted Answer · 2016-12-23 17:29:16Z

1

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.

answered Dec 23, 2016 at 17:29

OneCricketeer

193k20 gold badges146 silver badges276 bronze badges

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

2 Comments

SizzyNini Over a year ago

I'll try this and let you know how it goes! Thanks for your help.

OneCricketeer Over a year ago

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.

Parfait · Accepted Answer · 2016-12-23 18:44:06Z

0

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)

answered Dec 23, 2016 at 18:44

Parfait

108k19 gold badges103 silver badges138 bronze badges

Collectives™ on Stack Overflow

How to extract API data in a CSV format in Python

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related