[FIXED] How to turn multiple rows of dictionaries from a file into a dataframe

Issue

I have a script that I use to fire orders from a csv file, to an exchange using a for loop.

data = pd.read_csv('orderparameters.csv')
df = pd.DataFrame(data)

for i in range(len(df)):
    order = Client.new_order(...

   ...)
    file = open('orderData.txt', 'a')
    original_stdout = sys.stdout
    with file as f:
        sys.stdout = f
        print(order)
        file.close()
        sys.stdout = original_stdout

I put the response from the exchange in a txt file like this...

I want to turn the multiple responses into 1 single dataframe. I would hope it would look something like...

(I did that manually). I tried;

data = pd.read_csv('orderData.txt', header=None)
dfData = pd.DataFrame(data)
print(dfData)

but I got;

I have also tried

data = pd.read_csv('orderData.txt', header=None)
organised = data.apply(pd.Series)
print(organised)

but I got the same output. I can print order['symbol'] within the loop etc. I'm not certain whether I should be populating this dataframe within the loop, or by capturing and writing the response and processing it afterwards. Appreciate your advice.

Solution

It looks like you are getting json strings back, you could read json objects into dictionaries and then create a dataframe from that. Perhaps try something like this (no longer needs a file)

data = pd.read_csv('orderparameters.csv')
df = pd.DataFrame(data)
response_data = []

for i in range(len(df)):
    order_json = Client.new_order(...

   ...)
   response_data.append(eval(order_json))

response_dataframe = pd.DataFrame(response_data)

Answered By - Ben Borchard