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.

quincybatten

Iterating through pandas dataFrame objects is generally slow. Iteration beats the whole purpose of using DataFrame. It is an anti-pattern and is something you should only do when you have exhausted every other option. It is better look for a List Comprehensions , vectorized solution or DataFrame.apply() method for loop through DataFrame.

List comprehensions example

result = [(x, y,z) for x, y,z in zip(df['column1'], df['column2'],df['column3'])]