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

Question

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.

Forgot the Code for merge I tried using this: combined_data = pd.merge(panda_df, df_test, left_index = True, right_index = True) — Nikolas Weichert
– Nikolas Weichert, Commented Sep 21, 2022 at 13:55

Khaled Koubaa · Accepted Answer · 2022-09-22 13:26:56Z

1

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)

edited Sep 22, 2022 at 13:26

answered Sep 21, 2022 at 14:47

Khaled Koubaa

5476 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

22 Comments

Nikolas Weichert Over a year ago

I tried your solution. It works partially. The output to the csv is as following: 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16 2022-09-22 07:31:00,145.197,145.238,145.197,145.237,,,,,,,,,,,, ,,,,,145.197,145.238,145.197,145.237,,,,,,,, ,,,,,,,,,145.28,145.281,145.26,145.274,,,, ,,,,,,,,,,,,,145.272,145.278,145.257,145.274 So basically it now saves the prices/data which is nice. However now the date time index is missing for the following prices.

Nikolas Weichert Over a year ago

Also when loaded into a new DataFrame it yields following result 0 1 2 3 4 5 6 7 8 9 10 11 12 0 2022-09-22 07:41:00 145.243 145.252 145.231 145.238 NaN NaN NaN NaN NaN NaN NaN NaN 1 NaN NaN NaN NaN NaN 145.243 145.252 145.231 145.238 NaN NaN NaN NaN 2 NaN NaN NaN NaN NaN NaN NaN NaN NaN 145.253 145.284 145.25 145.262 As you can see the first prices are in the rows 1 -4 while the next prices are in row 5-8 with the next prices in 9 -12

Nikolas Weichert Over a year ago

When I set Ignore Index to false the output is as follows 'DateTime Open High Low Close Open.1 High.1 Low.1 Close.1 Open.1.1 High.1.1 Low.1.1 Close.1.1 Open.1.1.1 High.1.1.1 Low.1.1.1 Close.1.1.1 0 2022-09-22 07:45:00 145.3 145.337 145.291 145.333 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 1 NaN NaN NaN NaN NaN 145.3 145.337 145.291 145.337 NaN NaN NaN NaN NaN NaN NaN NaN 2 NaN NaN NaN NaN NaN NaN NaN NaN NaN 145.315 145.35 145.311 145.324 NaN NaN NaN NaN 3 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 145.348 145.379 145.342 145.352'

Nikolas Weichert Over a year ago

So basically right now it combines the Dataframes by adding new columns when it should just add new rows

Nikolas Weichert Over a year ago

Okay so I am one step further: Using axis = 0 in combined_data gives following output DateTime Open High Low Close 2022-09-22 07:59:00 145.551 145.676 145.546 145.674 NaN 145.551 145.676 145.546 145.674 NaN 145.668 145.742 145.618 145.727 So this now makes the concat function add rows instead of columns However now the Datetime Index of the following rows is NaN

|

Nikolas Weichert · Accepted Answer · 2022-09-22 14:11:31Z

0

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)

answered Sep 22, 2022 at 14:11

Nikolas Weichert

337 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

22 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

22 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related