pandas - How to filter one condition and extract the data based on the filtered condition in python?

Question

I am bit stuck with the code to extract the condition that I want.

What I want to do is that I want to filter the largest value of Tag AB0150 and based on that number date then I want to filter the data with that number.

Here is the example of my data. I have 3months of data and about 31 different tags.

ID           TIME              Machine_NO     TAG      VALUE
99959   2020-11-13 19:34:00       409301     AB0150    63.59
99959   2020-12-15 17:55:00       409302     AB0151    0.00
99959   2020-11-05 01:25:00       409303     AB0152    499.65
99959   2021-01-01 00:05:00       409301     AB0153    80.00
99959   2020-11-13 19:34:00       409301     AB0150    50.59
99959   2020-11-13 19:34:00       409301     AB0150    53.59
99959   2020-12-13 19:59:00       409301     AB0150    12.5
99959   2021-01-11 15:22:00       409301     AB0150    21.3
99959   2020-12-15 17:55:00       409302     AB0151    4.69
99959   2020-11-21 05:16:00       409303     AB0152    2133
99959   2020-11-13 19:34:00       409301     AB0151    700
99959   2020-11-13 19:34:00       409302     AB0152    35.5
99959   2020-11-13 19:34:00       409303     AB0153    2159

Below is the code so far.

df.sort_values(by=['TAG','VALUE'], ascending=False).drop_duplicates(subset=['TAG'],keep='first')
df_check=df[df['TIME']=='2020-11-13 19:34:00']

I want some automated code, with the code I've got I need to change the date if I change the date range. I want to filter the largest tag and select date and extract the data with that date at once. Is there any good way to do this?

Here is the desired output.

 ID           TIME              Machine_NO     TAG      VALUE
99959   2020-11-13 19:34:00       409301     AB0150     63.59
99959   2020-11-13 19:34:00       409301     AB0151     700
99959   2020-11-13 19:34:00       409302     AB0152     35.5
99959   2020-11-13 19:34:00       409303     AB0153     2159

Thank you

question from:https://stackoverflow.com/questions/65947038/how-to-filter-one-condition-and-extract-the-data-based-on-the-filtered-condition

Answer 1

You're pretty close to what you wanted. Store that resulting dataframe from the sort_values step as it's own dataframe and extract the TIME from the first row (index 0) and TIME column (2nd column; index 1) using .iat(). Then filter by that TIME value:

df_time = df.sort_values(by=['TAG','VALUE'], ascending=False).drop_duplicates(subset=['TAG'], keep='first')
MAX_TIME = df_time.iat[0, 1]
# '2020-11-13 19:34:00'

In case TIME is not necessarily the 2nd column, use this to get the TIME from the first row:

MAX_TIME = df_time.iat[0, df_time.columns.get_loc('TIME')]

And then filter the original df with MAX_TIME:

df_check = df[df['TIME'] == MAX_TIME]

# Result:
       ID                 TIME  Machine_NO     TAG    VALUE
0   99959  2020-11-13 19:34:00      409301  AB0150    63.59
4   99959  2020-11-13 19:34:00      409301  AB0150    50.59
5   99959  2020-11-13 19:34:00      409301  AB0150    53.59
10  99959  2020-11-13 19:34:00      409301  AB0151   700.00
11  99959  2020-11-13 19:34:00      409302  AB0152    35.50
12  99959  2020-11-13 19:34:00      409303  AB0153  2159.00