This is the solution, if someone out there needs.  The code will transform the splunk api invalid json to pandas dataframe into rows and columns.       import pandas as pd import requests import json import urllib3 from base64 import b64encode import urllib.parse from csv import reader urllib3.disable_warnings() data=[] output1 = pd.DataFrame() def fetch_data_using_userid(userid): url = "https://localhost:8000/services/search/jobs/export" payload = { 'search': f'search earliest = -40h index=* user_id="{userid}" | stats count by user user_ip ', 'output_mode':'json' } safe_payload = urllib.parse.urlencode(payload) userAndPass = b64encode(b"admin:pass").decode("ascii") headers = { 'Authorization': 'Basic %s' % userAndPass, 'Content-Type': 'application/json; charset=UTF-8' } response = requests.request("POST", url, headers=headers, data=safe_payload, verify=False) return response.text # open file in read mode with open('/Users/Downloads/user_data.csv', 'r') as read_obj: # pass the file object to reader() to get the reader object csv_reader = reader(read_obj) header = next(csv_reader) if header is not None: # Iterate over each row in the csv using reader object for row in csv_reader: # row variable is a list that represents a row in csv print(row) response_text = fetch_data_using_userid(row[0]) data = response_text with open('data.txt', 'w') as wf: wf.write(data) with open('data.txt') as rf: for entry in rf: LDict = json.loads(entry) output1 = output1.append(LDict,ignore_index=True) print(response_text) # result1 = pd.json_normalize(output1['result'])         thanks @kamlesh_vaghela for all the help. ... View more