How to Load Text Data into Data Frame from Response Object

Question

I am attempting to convert a curl request into a get-request to pull some data for work and transfer it to a local folder with a parameterized file name. One issue is that the data is only in text format and will not convert to JSON, even after trying multiple methods. Per the response, the data type is "text/tsv; charset=utf-8."

The next issue is that I cannot load the data into a data frame, partially because I am new to Python and do not understand the various methods for doing so, and partially because the formatting makes it more difficult to find an applicable solution. However, I was able to at least break the text into lists by using the splitlines() method. Unfortunately, though, I still cannot load the lists into a data frame. As of the last run, the error message is: "Error: cannot concatenate object of type '<class '_csv.reader'>'; only Series and DataFrame objs are valid."

import requests
import datetime
import petl
import csv
import pandas as pd
import sys
from requests.auth import HTTPBasicAuth
from curlParameters import *

def calculate_year():
    current_year = datetime.datetime.now().year
    return str(current_year)

def file_name():
    name = "CallDetail"
    year = calculate_year()
    file_type = ".csv"
    return name + year + file_type

try:
    response = requests.get(url, params=parameters, auth=HTTPBasicAuth(username, password))
except Exception as e:
    print("Error:" + str(e))
    sys.exit()

if response.status_code == 200:
    raw_data = response.text
    parsed_data = csv.reader(raw_data.splitlines(), delimiter='\t')
    table = pd.DataFrame(columns=[
                    'contact_id',
                    'master_contact_id',  
                    'Contact_Code',
                    'media_name',
                    'contact_name',
                    'ani_dialnum',
                    'skill_no',
                    'skill_name', 
                    'campaign_no', 
                    'campaign_name', 
                    'agent_no', 
                    'agent_name', 
                    'team_no', 
                    'team_name', 
                    'disposition_code',    
                    'sla', 
                    'start_date', 
                    'start_time', 
                    'PreQueue', 
                    'InQueue', 
                    'Agent_Time', 
                    'PostQueue', 
                    'Total_Time', 
                    'Abandon_Time', 
                    'Routing_Time', 
                    'abandon', 
                    'callback_time', 
                    'Logged', 
                    'Hold_Time'])
    try:
        for row in table:
            table.append(parsed_data)
    except Exception as e:
        print("Error:" + str(e))
        sys.exit()

petl.tocsv(table=table, source=local_source+file_name(), encoding='utf-8', write_header=True)

Answer 1

So, you're trying to append your parsed_data, which is the variable for iterating through your CSV data. I would actually recommend reading the data from the response first, then load it all into the dataframe. This would require a slight restructuring of the code. Something like this:

parsed_data = [row for row in csv.reader(raw_data.splitlines(), delimiter='\t')]
table = pd.DataFrame(parsed_data, columns=your_long_column_list)