Extract last month data and insert into a new column

Dash Python
1

Hi,

How to extract the last month data based on the ‘today’ date and insert the new data into a new column?

Current data:

DRR_Date Product Sale mtd lastmthdate
01/01/2022 Product 1 100 100 01/12/2021
01/01/2022 Product 2 150 150 01/12/2021
01/01/2022 Product 3 200 200 01/12/2021
01/01/2022 Product 4 100 100 01/12/2021
01/01/2022 Product 5 200 200 01/12/2021
02/01/2022 Product 1 100 200 02/12/2021
02/01/2022 Product 2 150 300 02/12/2021
02/01/2022 Product 3 200 400 02/12/2021
02/01/2022 Product 4 100 200 02/12/2021
02/01/2022 Product 5 200 400 02/12/2021
03/01/2022 Product 1 100 300 03/12/2021
03/01/2022 Product 2 150 450 03/01/2021
03/01/2022 Product 3 200 600 03/01/2021
03/01/2022 Product 4 100 300 03/01/2021
03/01/2022 Product 5 200 600 03/01/2021
01/02/2022 Product 1 1000 1000 01/01/2022
01/02/2022 Product 2 500 500 01/01/2022
01/02/2022 Product 3 2000 2000 01/01/2022
01/02/2022 Product 4 250 250 01/01/2022
01/02/2022 Product 5 300 300 01/01/2022
02/02/2022 Product 1 1000 2000 02/01/2022
02/02/2022 Product 2 500 1000 02/01/2022
02/02/2022 Product 3 2000 4000 02/01/2022
02/02/2022 Product 4 250 500 02/01/2022
02/02/2022 Product 5 300 600 02/01/2022
03/02/2022 Product 1 1000 3000 03/01/2022
03/02/2022 Product 2 500 1500 03/01/2022
03/02/2022 Product 3 2000 6000 03/01/2022
03/02/2022 Product 4 250 750 03/01/2022
03/02/2022 Product 5 300 900 03/01/2022 
df= pd.read_csv("Result1.csv")

df['mtd']=df.groupby(['month','Product'])['Sale'].cumsum()

current_mtd_start = df['date'] - pd.tseries.offsets.MonthBegin(1)
current_mtd_end = df['date']
last_mtd_start = current_mtd_start - pd.DateOffset(months=1)
last_mtd_end = current_mtd_end - pd.DateOffset(months=1)

last_mtd_end = pd.to_datetime(last_mtd_end, format='%Y-%m-%d')
df['lastmthdate'] =  last_mtd_end

app.layout =
    dbc.Row([
        dbc.Col([
            html.P("Table:",
                style={"textDecoration":"underline"}),
        
                                
        dash_table.DataTable(id='table', filter_action = "custom",
                               columns=[
                                   {'name': 'Today', "id": 'Date'},
                                   {'name': 'Product', "id": 'Product'},
                                   {'name': 'Sale', "id": 'Sale'},
                                   {'name': 'Current MTD', "id": 'mtd'},
                                   {'name': 'Last MTD', 'id': ''},
                                      
                                       ],
                               sort_action= 'native', 
                               sort_mode="multi",
                              data=df.to_dict('records')

                             
                              ),

Expected output:

DRR_Date Product Sale mtd lastmthdate lastmthdata
01/01/2022 Product 1 100 100 01/12/2021 0
01/01/2022 Product 2 150 150 01/12/2021 0
01/01/2022 Product 3 200 200 01/12/2021 0
01/01/2022 Product 4 100 100 01/12/2021 0
01/01/2022 Product 5 200 200 01/12/2021 0
02/01/2022 Product 1 100 200 02/12/2021 0
02/01/2022 Product 2 150 300 02/12/2021 0
02/01/2022 Product 3 200 400 02/12/2021 0
02/01/2022 Product 4 100 200 02/12/2021 0
02/01/2022 Product 5 200 400 02/12/2021 0
03/01/2022 Product 1 100 300 03/12/2021 0
03/01/2022 Product 2 150 450 03/01/2021 0
03/01/2022 Product 3 200 600 03/01/2021 0
03/01/2022 Product 4 100 300 03/01/2021 0
03/01/2022 Product 5 200 600 03/01/2021 0
01/02/2022 Product 1 1000 1000 01/01/2022 100
01/02/2022 Product 2 500 500 01/01/2022 150
01/02/2022 Product 3 2000 2000 01/01/2022 200
01/02/2022 Product 4 250 250 01/01/2022 100
01/02/2022 Product 5 300 300 01/01/2022 200
02/02/2022 Product 1 1000 2000 02/01/2022 200
02/02/2022 Product 2 500 1000 02/01/2022 300
02/02/2022 Product 3 2000 4000 02/01/2022 400
02/02/2022 Product 4 250 500 02/01/2022 200
02/02/2022 Product 5 300 600 02/01/2022 400
03/02/2022 Product 1 1000 3000 03/01/2022 300
03/02/2022 Product 2 500 1500 03/01/2022 450
03/02/2022 Product 3 2000 6000 03/01/2022 600
03/02/2022 Product 4 250 750 03/01/2022 300
03/02/2022 Product 5 300 900 03/01/2022 600
2

hi @beginof
I think you will find more luck searching for an answer on stackoverflow or pandas forums because this is more of a pandas questions. Once you figure out how to do that in your pandas dataframe, you can convert that easily to a Dash DataTable.