Pandas DataFrame lost index after appending row-CodePudding

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?

CodePudding user response：

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)

CodePudding user response：

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