I have a table looking similarly to the one below. I want to be able to drop row when current value date is not the max date in the table. In this case the row where current value is not null should be dropped because the date is not the max date.

CodePudding user response：

If your DataFrame is called df, then you can keep only the max date values by using a mask:

df = df[df['date'] == df['date'].max()]

Here you are saying that you only want rows from df where the value in the column df['date'] is equal to the max value from df['date'].

If you only want this where the 'current' column is not NULL, then you can do the following:

df = df[(df['current'] == 'NULL') | (df['date'] == df['date'].max())]

CodePudding user response：

You could do:

df = df[(df['current'].isnull()) | (df['date'] == df['date'].max())]

CodePudding user response：

Not sure if I understand what you want to do, but here's my take.

Convert your dates to pandas datetime objects and replace NULL values with NaN.

import numpy as np
import pandas as pd

df = df.replace('NULL', np.nan)
df.date = pd.to_datetime(df.date)

Then you can do:

df.query('not (~current.isna() and date != date.max())')

This drops the row with current value 273.7215714 since its date is not the maximum date. If you add another row with the maximum date (2022-04-01) and any non-null current value, it should be kept.

The resulting table looks like this (row 7 from original df dropped):