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, Dec 02 '21 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, Dec 02 '21 at 20:58

score 1 · Answer 1 · 2021-12-02T21:55:35.517

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. — noskule, Dec 02 '21 at 21:00

score 0 · Accepted Answer · answered Dec 02 '21 at 21:32

0

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")]
)

answered Dec 02 '21 at 21:32

Boudewijn Aasman

1,236
1
13
20

I guess one benefit is that concat dont copy the dataframe but append does, see: https://stackoverflow.com/questions/15819050/pandas-dataframe-concat-vs-append – noskule Dec 02 '21 at 22:36

Pandas DataFrame lost index after appending row

2 Answers2