how to filter on date that starts on the last day of the previous month and ends on the current day-CodePudding

how to filter on date that starts on the last day of the previous month and ends on the current day

(If the last day of the previous month is Saturday or Sunday, the previous day must be assigned)

For example:

example = pd.read_excel("C:/Users/USER/Downloads/Movimentos.xls")

example = example.drop(columns=['Data Valor', 'Descrição', 'Valor'])

example.sort_values(by='Data Operação', ascending=False)

display(example)

Data Operação   Saldo
0   12/10/2022  310.36
1   12/10/2022  312.86
2   11/10/2022  315.34
3   11/10/2022  317.84
4   09/10/2022  326.44
5   30/09/2022  224.44 
... ... ...
188 05/07/2022  128.40
189 01/07/2022  8.40
190 24/06/2022  18.40
191 22/06/2022  23.40
192 27/05/2022  50.00

In this case I would like to filter from ( 5 30/09/2022 224.44 ) which it is the last day of the previous month and it is a weekday, to ( 0 12/10/2022 310.36 ) which it is the current day.

I've seen some examples where you just had to enter the date '2022-09-30' but in this case will be recurring so it needs to be something like:

today = datetime.date.today()

today.month (for the end date)

but for the start date I don't know how I'm supposed to do.

CodePudding user response：

here is one way to do it

btw, you can convert the date column to datetime as follows, to avoid converting while filtering

# optionally
df['Operação'] = pd.to_datetime(df['Operação'], dayfirst=True)

# convert (in memory) the date to ymd format
# using pd.offset, get business monthend of previous month
# finally using loc to identify the rows matching the criteria
(df.loc[(pd.to_datetime(df['Operação'], dayfirst=True) <= 
         pd.Timestamp(datetime.now()).strftime('%Y-%m-%d')) & 
        (pd.to_datetime(df['Operação'], dayfirst=True) >=
         pd.Timestamp(datetime.now() pd.offsets.BusinessMonthEnd(-1)).strftime('%Y-%m-%d'))  
       ]
)

OR to make it more comprehensible

# create today (with a typo in var name) and lmonth variables
# then use these for comparison
tday=pd.Timestamp(datetime.now()).strftime('%Y-%m-%d')
lmonth= (pd.Timestamp(datetime.now() pd.offsets.BusinessMonthEnd(-1))).strftime('%Y-%m-%d')



(df.loc[(pd.to_datetime(df['Operação'], dayfirst=True) <= tday) &
        (pd.to_datetime(df['Operação'], dayfirst=True) >= lmonth)  
       ]
)

    Data    Operação    Saldo
0   0   12/10/2022  310.36
1   1   12/10/2022  312.86
2   2   11/10/2022  315.34
3   3   11/10/2022  317.84
4   4   09/10/2022  326.44
5   5   30/09/2022  224.44