Nsryjdtyk

I have an text file, where each line I have cleansed up to be of a json format. I can read each line, clean them, and convert them into a panda dataframe.

My problem is that I want to add/combine them all into one dataframe, but there are more than 200k lines.

I am reading each line in as 'd' = '{"test1":"test2","data":{"key":{"isin":"test3"},"creationTimeStamp":1541491884194,"signal":0,"hPreds":[0,0,0,0],"bidPrice":6.413000,"preferredBidSize":1,"offerPrice":6.415000,"preferredOfferSize":1,"averageTradeSize":1029,"averageTradePrice":0.065252,"changedValues":true,"test4":10,"snapshot":false}}'

Assume I am able to convert each line into a panda... is there a way to append each line into the panda dataframe, such that it is very fast. Right now, with >200k lines, it takes hours to append... reading the file itself takes less than 5 min...

file ='fileName.txt'

with open(file) as f:

    content = f.readlines()

content = [x.strip() for x in content]



data = pd.DataFrame()

count = 0

for line in content:

    line = line.replace('{"string1','')

    z = line.splitlines()

    z[0] = z[0][:-1]

    z = pd.read_json('[%s]' % ','.join(z))



    data = data.append(z)

You may check with Series

pd.Series(d)

Out[154]: 

averageTradePrice              0.065

averageTradeSize                 109

bidPrice                        6.13

changedValues                   True

creationTimeStamp           15414994

Preds                   [0, 0, 0, 0]

key                  {'epic': 'XXX'}

dataLevel                         10

offerPrice                     3.333

dtype: object

Preds and key's value are list and dict , that is why when you pass it to DataFrame it flag as :

ValueError: arrays must all be same length

Since you mention json

from pandas.io.json import json_normalize

json_normalize(d)

Out[157]: 

          Preds  averageTradePrice     ...      key.epic  offerPrice

0  [0, 0, 0, 0]              0.065     ...           XXX       3.333

[1 rows x 9 columns]