I have a multi-index df that stores all my stocks execution orders.
I need to compute the counts of each existing open orders and working orders of each type of combination type, ie strategy type.
there could be multiple rows of a specific combination type. this helps with automation follow up activities such as amend stoploss, close all, execution, and etc.
i have problem selecting the specific data and tabulating the counts, cause there are many variations and layers so i want to use a more pythonic df / multiindex friendly way to process it to prevent overheads. from the left to right, the row indexers are sequential.
ie there will a set of orders in AAA, BUY, 0, 0, 0, oo, 0, 0 and also in AAA, BUY, 1, 1, 0, oo, 0, 0 for example. so i want to count the total counts of each type of combination.
the more important combination are in the first 6 indexers. ie asset direction acc_no str_no type_asset o_type
i know i can use loop to find all combination one by one and add up the counts, but worried of overheads and time performance.
i tried this: but this gives me the error but assume this even works, this only checks 1 row and 1 combination
orders_df.index.values[0][0:5].isin([("AAA", "BUY", 0, 0, 0)]).any()
AttributeError: 'tuple' object has no attribute 'isin'
tried this
orders_df.index.isin([("AAA", "BUY", 0, 0, 0)]).any()
ValueError: Length of names must match number of levels in MultiIndex.
this:
orders_df.index.values.isin([("AAA", "BUY", 0, 0, 0)]).any()
ValueError: Length of names must match number of levels in MultiIndex.
i think i may have to use iterrows(), lambda or comprehension but im new to python and not strong in this...
would truly greatly appreciate if someone can provide me with ideas on how to filter/scan and search for all existing combinations and their counts or perhaps len.
Thank you!
the df looks like this. there are more columns for follow up processing and computation but i cannot squeeze in all. i have no problems with columns but facing issues on the rows indexers.
size level
asset direction acc_no stratg type_asset o_type seq sub_type ref Id code
AAA BUY 0 0 0 oo 0 0 sdw jag qwe 5.0 12937.0
1 oo 0 1 fdc dfr qwe 0.5 12937.3
1 2 fdc gb qwe 0.5 12938.6
1 0 oo 0 0 fgf gd qwe 5.0 12937.8
1 oo 0 1 sse sfg qwe 0.5 12937.8
1 2 dcv rsf qwe 0.5 12937.8
3 0 oo 0 0 asd adgs qwe 5.0 12934.1
1 oo 0 1 qwe sdfsr qwe 0.5 12934.6
1 2 ikj agsrgs qwe 0.5 12934.0
5 0 oo 0 0 bfg dfd qwe 5.0 12934.1
dfg asfg qwe 5.0 12932.0
fdf asfdgs qwe 5.0 12933.6
hdt afsd qwe 5.0 12928.2
1 oo 0 1 dse sdfsf qwe 0.5 12931.7
fbg afdsf qwe 0.5 12927.7
fdd qrd qwe 0.5 12933.5
hnb dfas qwe 0.5 12934.8
1 2 dfg asfaf qwe 0.5 12927.1
sdf qwe 0.5 12935.0
ews dfsd qwe 0.5 12931.5
fgd fjfgh qwe 0.5 12934.0
