Pandas DataFrame lost index after appending row

Question

I create a DataFrame and set an index. If I append a row via append then the index is lost.

import pandas as pd history = {} history_cols = { "event_time": "E", "close": "c", "base_volume": "v", "quote_volume": "q", "total_number_of_trades": "n" } ticks = [ {'event_time': 1638470651223, 'close': '133.41000000', 'base_volume': '70094.70000000', 'quote_volume': '9415851.87690000', 'total_number_of_trades': 30917}, {'event_time': 1638470652088, 'close': '133.41000000', 'base_volume': '70094.70000000', 'quote_volume': '9415851.87690000', 'total_number_of_trades': 30917}, {'event_time': 1638470653224, 'close': '133.41000000', 'base_volume': '70094.70000000', 'quote_volume': '9415851.87690000', 'total_number_of_trades': 30917}, {'event_time': 1638470654189, 'close': '133.41000000', 'base_volume': '70094.70000000', 'quote_volume': '9415851.87690000', 'total_number_of_trades': 30917}, {'event_time': 1638470655203, 'close': '133.41000000', 'base_volume': '70094.70000000', 'quote_volume': '9415851.87690000', 'total_number_of_trades': 30917}, {'event_time': 1638470656201, 'close': '133.41000000', 'base_volume': '70094.70000000', 'quote_volume': '9415851.87690000', 'total_number_of_trades': 30917} ] history["AXSBUSD"] = pd.DataFrame(columns=history_cols.keys()) history["AXSBUSD"].set_index("event_time", inplace=True) history["AXSBUSD"]

The empty DataFrame has the index:

 close base_volume quote_volume total_number_of_trades event_time

Now I append a row with a dict ...

history["AXSBUSD"] = history["AXSBUSD"].append(ticks[0], ignore_index=True) history["AXSBUSD"]

... and this is the result:

 close base_volume quote_volume total_number_of_trades event_time 0 133.41000000 70094.70000000 9415851.87690000 30917 1.638471e+12

Has anyone an idea why the index is gone?

Any reason you doing it on row at a time? history["AXSBUSD"] = history["AXSBUSD"].append(pd.DataFrame(ticks).set_index('event_time')) use DataFrame constructor with ticks an set_index then append to dataframe in dictionary. — Scott Boston
– Scott Boston, Commented Dec 2, 2021 at 20:53
Reason is that I get every second a tick which I want to add to the DataFrame and then do some calculations like moving avarage — noskule
– noskule, Commented Dec 2, 2021 at 20:58

score 1 · Accepted Answer · 2021-12-02 21:55:35Z

Instead of making it so complicated, why not just:

history["AXSBUSD"] = pd.DataFrame(ticks).set_index('event_time')

If you need to append rows one by one, then you can do:

history["AXSBUSD"] = pd.DataFrame(columns=history_cols.keys()) history["AXSBUSD"].set_index("event_time", inplace=True) history["AXSBUSD"] = (history["AXSBUSD"] .append(pd.Series(ticks[0]) .rename(ticks[0]['event_time'], inplace=True) .drop('event_time'))) print(history["AXSBUSD"])

Output:

 close base_volume quote_volume total_number_of_trades event_time 1638470651223 133.41000000 70094.70000000 9415851.87690000 30917

The main issue with just appending a dictionary to a dataframe is, it's not clear what the new row's index should be; that's why you had to put ignore_index=True. But if you .rename a pd.Series to be appended, it will serve as the index.

But, I think it's best if you just append rows without doing all that and just set_index once you actually need to work with the dataframe:

for tick in ticks: history["AXSBUSD"] = history["AXSBUSD"].append(tick, ignore_index=True) history["AXSBUSD"].set_index('event_time', inplace=True)

the ticks list is just for testing. I will get only one tick per second which a then want to add to the DataFrame.

Boudewijn Aasman · Accepted Answer · 2021-12-02 21:32:29Z

Not sure how efficient this is compared to simply appending the dataframe, but this would work and serve your purposes:

history["AXSBUSD"] = pd.concat( [history["AXSBUSD"], pd.DataFrame([ticks[0]]).set_index("event_time")] )

I guess one benefit is that concat dont copy the dataframe but append does, see: stackoverflow.com/questions/15819050/…

Collectives™ on Stack Overflow

Pandas DataFrame lost index after appending row

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related