Accessing data with iloc or other method for more then one items using loop in it

samphel
Hello to all fellow members,

so what i'm doing in my project is through API i'm accessing stock price of 10 scripts and i'm sending these tick data to mysql, and from mysql i'm retreiving the data back to calculate or to use resample the stock price in OHLC format, well following is the code for using the resample function:
def data_from_sql(): # fetch all query
try:
db = pymysql.connect(host, user, password, database)
cursor = db.cursor()

sql_query_2 = "SELECT * FROM tick_data.ticks WHERE date >= NOW() - INTERVAL 5 MINUTE"

cursor.execute(sql_query_2)

records = cursor.fetchall()
cursor.close()
print("Total rows are: ", len(records))
df = pd.DataFrame(records, columns=['token', 'last_price', 'time']).set_index('time')
ohlc = df.groupby('token').resample('5min')['last_price'].ohlc()

excel_details(ohlc) # send the data to other function for updating my excel sheet.

except pymysql.Error as error:
print("Failed to read data from table", error)

data_from_sql()

while True:

data_from_sql() # for infinite loop
Ok now the output of the above code is:

enter image description here


Now, what i need is for each token number i need "close" price of the previous timeframe, i.e 5 minute before the current timeframe, for eg for token number "5633" i need "close" price at 2020-03-12 14:15:00 not of 2020-03-12 14:20:00, since "2020-03-12 14:20:00" candle or time is not closed and would like to update my excel sheet for the same.

So for above result i'm coding as:

new_close = ohlc.iloc[-2]['close']
print(new_close)
And , for this i'm getting the "close_price of only my last token no "3861249":

# output is:

292.05

But i need close_price of all the token number , so please help me out on how to get the desired result.

And also if possible can anyone tell me how to improve the perfomance of my code. :)
Sign In or Register to comment.