Python-pandas : a strange filtering/remove error

FlyUFalcon 0
Viewed 34

I've been using pandas for a while but I am having a really strange issue with simple filtering in pandas.

OS: Mac OS
IDE: VSCode
Pandas version: 1.4.2

I am fetching the latest data from crypto exchanges(via ccxt api) and append them into a dataframe.

limit = 28
timeframe = '1h'

futures_exchange = ccxt.kucoinfutures({
'apiKey' : MY_API_KEY,
'secret' : MY_API_SECRET,
'enableRateLimit': True, 
'password': MY_KUCOIN_PASS_PHRASE,
})

all_future_list = [ 'BTC/USDT:USDT', 'BTC/USD:BTC', 'ETH/USDT:USDT', 'BCH/USDT:USDT', 'BSV/USDT:USDT', ]
  
attempts = 0
while attempts<= 20:
    alldf = pd.DataFrame()
    try:
        for i in all_future_list:
            df = futures_exchange.fetchOHLCV(i, limit=limit, timeframe=timeframe)
            df = pd.DataFrame(df, columns=[['timestamp', 'open', 'high', 'low', 'close', 'volume']])
            df['ticker'] = i
            df['timestamp'] = df['timestamp'].astype('datetime64[ms]') 
            df = df[['timestamp', 'close', 'volume', 'ticker']]
            df = df.tail(1)
            alldf = pd.concat([alldf, df], ignore_index=True)
            time.sleep(0.1)
    except:
        print(traceback.format_exc())
        print(' ___  Network Error, restart fetching data _____ ')
        attempts += 1
        time.sleep(8)
        continue
    break

So far so good. Dataframe looks like...

             timestamp     close    volume         ticker     
0  2022-09-17 10:00:00  19836.00  789336.0  BTC/USDT:USDT
1  2022-09-17 10:00:00    1411.2    982840  ETH/USDT:USDT   
2  2022-09-17 10:00:00    120.95   55564.0  BCH/USDT:USDT

However, from there if I want to remove/filter a ticker from dataframe

new_df = alldf[alldf['ticker']!= 'BTC/USDT:USDT']
print(new_df)

Erroneous output:

  timestamp close volume         ticker  
0       NaT   NaN    NaN            NaN     
1       NaT   NaN    NaN  ETH/USDT:USDT    
2       NaT   NaN    NaN  BCH/USDT:USDT     
3       NaT   NaN    NaN  BSV/USDT:USDT

This should be a simple remove/filter but I dont understand why 'timestamp', 'close' and 'volume' columns are NaN

It seems if I write dataframe to a csv and then read it then I can get what I want.

alldf.to_csv('alldf.csv')
alldf = pd.read_csv('alldf.csv',index_col=0)

new_df = alldf[alldf['ticker']!= 'BTC/USDT:USDT']
print(new_df)

Output:

             timestamp     close    volume         ticker     
1  2022-09-17 10:00:00    1411.2    982840  ETH/USDT:USDT
0  2022-09-17 10:00:00    120.95   55564.0  BCH/USDT:USDT    
2  2022-09-17 11:00:00     51.95    3628.0  BSV/USDT:USDT

However, I dont want to write dataframe to csv and read it again just to filter out some tickers.

Can anyone help me ? Not sure whats going on here.

ccxt csv filter pandas python
1 Answers

I guess this happens because that this piece of code

        df = df[['timestamp', 'close', 'volume', 'ticker']]
        df = df.tail(1)

subsets existing dataframe without actually copying the data. Therefore, target dataframe would get reference to original data, and when its changed - it gets broken.

Try this:

        df = df[['timestamp', 'close', 'volume', 'ticker']]
        df = df.tail(1).copy()

BTW, AFAIK, it's more efficient to keep new records in a dictionary and convert it to a dataframe at the end, rather then merging them one by one in a loop.

kol0
Related