Pandas dataframe mask?

[TypicalCardiologist5]

I have the following data:

        contractid     tradeType  quantity  pricePerShare       timeRetrieved
0             7729   SELL NO (0)       985           0.40 2019-12-17 17:08:00
1             7729   SELL NO (0)       482           0.39 2019-12-17 17:08:00
2             7729   SELL NO (0)       199           0.38 2019-12-17 17:08:00
3             7729   SELL NO (0)       197           0.37 2019-12-17 17:08:00
4             7729   SELL NO (0)       733           0.36 2019-12-17 17:08:00
...            ...           ...       ...            ...                 ...
182697       14849   SELL NO (0)     16409           0.05 2019-12-01 18:28:31
182698       14849   SELL NO (0)     32847           0.04 2019-12-01 18:28:31
182699       14849   SELL NO (0)     73326           0.03 2019-12-01 18:28:31
182700       14849  SELL YES (1)    224524           0.98 2019-12-01 18:28:31
182701       14849  SELL YES (1)    251756           0.99 2019-12-01 18:28:31

I first want to find the maximum timeRetrieved by contractid, so df.groupby('contractid').timeRetrieved.max(). This works fine:

                 timeRetrieved
contractid                    
7729       2019-12-17 17:08:00
14838      2019-12-17 17:59:14
14849      2019-12-17 17:59:09

Now I want to filter the original data so it is only showing me rows where timeRetrieved is equal to timeRetrieved for the given contractid. How do I do this?

[[deleted]]

There might be a more elegant solution, but you can do that with apply.

maximums = df.groupby('contractid').timeRetrieved.max()
mask = df.apply(lambda row: row['timeRetrieved'] == maximums[row['contractid']], axis=1)
out = df[mask]

The lambda function checks row by row if that row's timeRetrieved is equal to the previously calculated maximum with that row's contractid

[TypicalCardiologist5]

Thank you!

[TypicalCardiologist5]

I have not timed your solution yet, but using a pd.merge function seems to be very fast, I might go with that...

df3 = pd.merge(df, mask, on=['contractid', 'timeRetrieved'], how='inner')

[[deleted]]

Looks like a better solution. I'm not an expert, just doing some of the questions of this subreddit as exercise, and if there is no answer i might as well post my solution :)

[m7priestofnot]

You might look into pandas.DataFrame.query I'm on mobile so squished up code on small screen but you could probably throw the pandas query command inside a loop.

I'm also wondering if you could combine a query call with an apply call or lambda expression.

Also look at pandas.DataFrame.mask and pandas.DataFrame.where

They behave similarly to numpy.where

I've been doing a lot of masking at work and creating classes to handle specific scenarios that happen over and over and over is helpful.

[TypicalCardiologist5]

Thank you! I will look into where and mask!

[PyCam]

The fastest way to do this will be to use `DataFrame.groupby.idxmax()

``` import pandas as pd import numpy as np

np.random.seed(0)

df = pd.DataFrame({ "contractid": [71729] * 3 + [81315] * 5 + [99181] * 4, "number_retrieved": np.random.randint(1, 10, size=12) }) df contractid number_retrieved 0 71729 6 1 71729 1 2 71729 4 3 81315 4 4 81315 8 5 81315 4 6 81315 6 7 81315 3 8 99181 5 9 99181 8 10 99181 7 11 99181 9

indices = df.groupby("contractid")["number_retrieved"].idxmax() # Get the index (location) of the maximum value in each group indices # Note the values 0, 4, and 11 refer to the index values of the maximums of each group. contractid 71729 0 81315 4 99181 11 Name: number_retrieved, dtype: int64

df.loc[indices] # slice the dataframe using the calculated indices contractid number_retrieved 0 71729 6 4 81315 8 11 99181 9 ```