CODEWITHSUNDEEP
pythonpandascsvappend
Noob74
Noob74

Reputation: 65

Appending and or writing to a CSV from pulled JSON data

The code written below is meant to pull the JSON data from the url given.( i've removed the key). Then i've formatted it and changed it to CSV. When this program is ran and printed, i see the first row that is fetched, but the subsequent rows that i mean to append are added to the first row (df) only, and not compiled one on the next. Is there a simple way this can be accomplished? So that after each time.sleep() period is done, the row is added to the previous sleep period and not to the original df.

df = pd.read_json('https://forex.1forge.com/1.0.3/quotes?pairs=EURUSD,EURJPY,GBPUSD,USDCAD,&api_key=KEY')
df = df.pivot_table('price', 'timestamp', 'symbol')
df.to_csv('datapull.csv')
df = pd.read_csv('datapull.csv', index_col='timestamp')

x = range(6)
for n in x:

  df2 = pd.read_json('https://forex.1forge.com/1.0.3/quotes?pairs=EURUSD,EURJPY,GBPUSD,USDCAD,&api_key=KEY')
  df2 = df2.pivot_table('price', 'timestamp', 'symbol')
  df2.to_csv('datapull2.csv')
  df2 = pd.read_csv('datapull2.csv', index_col='timestamp')
  df3 = df.append(df2)
  time.sleep(1)
  print(df3)

A sample of the input JSON input data is:

[{"symbol":"EURUSD","bid":1.13913,"ask":1.13913,"price":1.13913,"timestamp":1541703878},{"symbol":"EURJPY","bid":129.75,"ask":129.753,"price":129.7515,"timestamp":1541703878},{"symbol":"GBPUSD","bid":1.30907,"ask":1.30908,"price":1.30907,"timestamp":1541703878},{"symbol":"USDCAD","bid":1.31059,"ask":1.31059,"price":1.31059,"timestamp":1541703878}]

Below is the output of that code above.

                       EURJPY   EURUSD   GBPUSD   USDCAD
timestamp                                               
2018-11-08 00:06:06  129.8615  1.14359  1.31276  1.31167
2018-11-08 00:06:06  129.8610  1.14359  1.31276  1.31167
                       EURJPY   EURUSD   GBPUSD   USDCAD
timestamp                                               
2018-11-08 00:06:06  129.8615  1.14359  1.31276  1.31167
2018-11-08 00:06:08  129.8605  1.14359  1.31277  1.31163
                       EURJPY   EURUSD   GBPUSD   USDCAD
timestamp                                               
2018-11-08 00:06:06  129.8615  1.14359  1.31276  1.31167
2018-11-08 00:06:09  129.8660  1.14362  1.31278  1.31156
                       EURJPY   EURUSD   GBPUSD   USDCAD
timestamp                                               
2018-11-08 00:06:06  129.8615  1.14359  1.31276  1.31167
2018-11-08 00:06:10  129.8725  1.14363  1.31289  1.31155
                       EURJPY   EURUSD   GBPUSD   USDCAD
timestamp                                               
2018-11-08 00:06:06  129.8615  1.14359  1.31276  1.31167
2018-11-08 00:06:12  129.8750  1.14363  1.31289  1.31155
                       EURJPY   EURUSD   GBPUSD   USDCAD
timestamp                                               
2018-11-08 00:06:06  129.8615  1.14359  1.31276  1.31167
2018-11-08 00:06:13  129.8735  1.14363  1.31289  1.31155

Upvotes: 3

Views: 2599

Answers (1)

Nicolás Ozimica
Nicolás Ozimica

Reputation: 9748

You're accumulating data in a wrong way, because you're appending on each round the first row (df) to the row just fetched (df2).

The result of that appending on each round (df3) gets overwritten and lost at the end of each round.

What about this:

df = pd.read_json('https://forex.1forge.com/1.0.3/quotes?pairs=EURUSD,EURJPY,GBPUSD,USDCAD,&api_key=KEY')
df = df.pivot_table('price', 'timestamp', 'symbol')
df.to_csv('datapull.csv')
df = pd.read_csv('datapull.csv', index_col='timestamp')

x = range(6)
for n in x:

  df2 = pd.read_json('https://forex.1forge.com/1.0.3/quotes?pairs=EURUSD,EURJPY,GBPUSD,USDCAD,&api_key=KEY')
  df2 = df2.pivot_table('price', 'timestamp', 'symbol')
  df2.to_csv('datapull2.csv')
  df2 = pd.read_csv('datapull2.csv', index_col='timestamp')
  df = df.append(df2)
  time.sleep(1)
  print(df)

i.e. just defining df as the appending of itself and the new row just fetched (df2).

Upvotes: 1

Related Questions