How to efficiently filter and extract data from a pandas dataframe into an array

Question

I have a dataframe df_orders such as this:

    Symbol    action    orderid   qty    status   price
    AAPL      buy       1232131   100    open     110.41
    MSFT      sell      8120472   200    open     83.42
    MSFT      sell      8120473   500    open     81.12

I want to get orderid, qty, status and price for each row where 'Symbol' == 'MSFT' and 'Action' == 'sell' and put them into an array. Desired output is

[['MSFT','sell',8120472,200,'open',83.42],['MSFT','sell',8120473,200,'open',81.12]]

I wrote the following code a while ago that works, but it is not efficient. I need to run this piece of code at least 50 million times per day and a very millisecond counts.


myarray = []
dfsells = df_orders[df_orders['Symbol'] == symbol]
if len(dfsells.index) > 0:
    dfsells = dfsells[dfsells['action'] == 'Sell']
    dfsells = dfsells.reset_index(drop=True)

    if len(dfsells.index) > 0:
        for j in range(0,len(dfsells.index)):
                orderid = dfsells.loc[j,'orderid']
                sellqty = dfsells.loc[j,'qty']
                status = dfsells.loc[j,'status']
                price = round(float(dfsells.loc[j,'price']),2)
                myarray.append([symbol,'sell',orderid,sellqty,status,price])

What is the quickest way to get this array?

you can just write df.loc[(df.Symbol=='MSFT' | df.Action=='sell, [list of features you like].values — anishtain4
– anishtain4, Commented Nov 16, 2020 at 20:32

mujjiga · Accepted Answer · 2020-11-16 20:39:55Z

1

df[(df['Symbol'] == symbol) & (df['action'] == 'Sell')].values.tolist()

Test case (in notebook):

n = 10000000
df = pd.DataFrame(
    {
        'Symbol': np.random.randint(0, 10, n),        
        'action': np.random.randint(0, 10, n),  
        'orderid': np.random.randint(0, 10, n),  
        'qty': np.random.randint(0, 10, n),  
        'status': np.random.randint(0, 10, n),  
        'price': np.random.randint(0, 10, n),  
    }
)

%%time
len(df[(df['Symbol'] == 3) & (df['action'] == 4)].values.tolist())

Output:

Wall time: 68.6 ms
99781

It took 68.6ms so pretty fast I guess.

answered Nov 16, 2020 at 20:39

mujjiga

17.1k2 gold badges37 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to efficiently filter and extract data from a pandas dataframe into an array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related