How to add a row to a DataFrame (or CSV) in Pandas with DateTime Index

Nikolas Weichert 0
Viewed 40

I am getting data every minute through an API call. Now I want to add this data to a CSV file. The data should update to the CSV after it gets new data.

I am using this code:

num_points = 1

df_setup = ig_service.fetch_historical_prices_by_epic_and_num_points(epic, resolution, num_points)
df_setup_prices_ask = df_setup['prices']['ask'] 
panda_df = pd.DataFrame(df_setup_prices_ask)
time.sleep(60)

while True:
    stream_close_price = ig_service.fetch_historical_prices_by_epic_and_num_points(epic, resolution, num_points)
    df_last_close = stream_close_price['prices']['ask'] 
    
    df_test = pd.DataFrame(df_last_close)
    
    combined_data = pd.concat([panda_df,df_test], axis=1)
    
    combined_data.to_csv('data.csv')
    
    
    print(df_last_close)
    
    time.sleep(60)

However I can't figure out how to get this working. If I use the code above I get following output (only the "newest" data gets saved to the CSV):

DateTime,Open,High,Low,Close,Open,High,Low,Close
2022-09-21 14:34:00,143.992,143.995,143.99,143.992,,,,
2022-09-21 14:36:00,,,,,143.977,143.978,143.975,143.978

Now when I use .join using this code:

combined_data = panda_df.join(df_test)

which give this error:

ValueError: columns overlap but no suffix specified: Index(['Open', 'High', 'Low', 'Close'], dtype='object')

Now I of course tried combined_data = panda_df.join(df_test, on = 'DateTime') However this throws the key error for DateTime

Next up I tried using merge with this code which does not work because it only returns an empty CSV:

DateTime,Open_x,High_x,Low_x,Close_x,Open_y,High_y,Low_y,Close_y

Also I know that there is probably an easier way than doing a call to create a df to use as a base to join but I haven't figured that out.

pandas python
2 Answers

try

#...
panda_df = pd.DataFrame(df_setup_prices_ask)
panda_df = panda_df.reset_index(names='DateTime') #add this <<---
panda_df = pd.read_csv('data.csv', index=False) #don't forget index=False otherwise will not work

while True:
    prevous_df = pd.read_csv('data.csv')

    stream_close_price = ig_service.fetch_historical_prices_by_epic_and_num_points(epic, resolution, num_points)
    df_last_close = stream_close_price['prices']['ask'] 
    
    df_test = pd.DataFrame(df_last_close)
    df_test = df_test.reset_index(names='DateTime')  #add this <<---

    combined_data = pd.concat([previous_df, df_test], axis=1, ignore_index=True) 
    
    combined_data.to_csv('data.csv', index=False) #don't forget index=False otherwise will not work
    
    
    print(df_last_close)
    
    time.sleep(60)
khaled koubaa0

Like my last comment explained I found a workaround where I just save the csv at the end of while function instead of saving and loading the first dataframe:

stream_close_price = ig_service.fetch_historical_prices_by_epic_and_num_points(epic, resolution, num_points)
df_last_close = stream_close_price['prices']['ask'] 

df = pd.DataFrame(df_last_close)

time.sleep(60) 

while True:

stream_close_price = ig_service.fetch_historical_prices_by_epic_and_num_points(epic, resolution, num_points)
df_last_close = stream_close_price['prices']['ask'] 

df_2 = pd.DataFrame(df_last_close)
    
    df = pd.concat([df, df2], axis=0)
    
    print(df)
    
    df.to_csv('data.csv')
    
    time.sleep(60)
Nikolas Weichert0
Related