Home > Blockchain >  How to remove nan values and merge lower rows with upper rows in a live dataframe?
How to remove nan values and merge lower rows with upper rows in a live dataframe?

Time:11-04

I'm trying to automate a trade strategy. I'm using Angelbroking stock market API to create live dataframe.

The API I'm using fetches only open high low close values excluding date. So I mixed a python time program with API to get both current datetime and OHLC values. Below is the code I'm working on-

while (True):
#below code fetches current date and time 
IST = pytz.timezone('Asia/Kolkata')
datetime_ist = datetime.now(IST)
Datetime=datetime_ist.strftime('%Y/%m/%d %H:%M:%S')

#API to fetch last traded price(LTP)of a stock
exchange = "NSE"
tradingsymbol = "SBIN-EQ"
symboltoken = 3045
a=SmartApi.ltpData("NSE", "SBIN-EQ", "3045")

#creating datetime and LTP DataFrames
df = pd.DataFrame([a['data']])
df1= pd.DataFrame([Datetime])
df1.columns = ['Datetime']

#joining both DataFrames
df2 = pd.concat([df1, df])

After joining both DataFrames df and df1 I'm getting Nan values in both rows like in the below image-

After joining DataFrames.jpg

So, I tried grouping the data to remove Nan values.

#Grouping the data
df3=df2.groupby('Datetime').max().reset_index()
display(df3)

Now, grouping datetime only outputs datetime and shows Nan values in OHLC columns and when I group OHLC columns I'm getting Nan in datetime column like below

Datetime grouping.jpg

Being a beginner in python I'm unable to understand what am I missing or doing wrong.

CodePudding user response:

It seems like you are trying to attach the datetime column of the same length to the OHLC dataframe. axis=0 is for rows while axis=1 is for columns. By default, pandas uses axis=0, so you need to specify your preferred change to axis=1.

Try changing your concat to this:

df2 = pd.concat([df1, df], axis=1)

In a scenario where you get InvalidIndexError:

pd.concat([df1.reset_index(), df], axis=1) 

An alternative would be to simply use join():

df.join(df1)

Word of advice: It's good practice to reset the index before using join() or concat() if you are trying to produce a dataframe from 2 different dataframes.

  • Related