CODEWITHSUNDEEP
pythonpandas
Alex T
Alex T

Reputation: 3754

Substracting values in dataframe based on date

I have dataframe like this:

ID  Date       Paid  Due
A  2019-07-01  40000 50000
B  2018-08-20  1500  9000
B  2018-08-20  2500  9000
B  2018-09-18  3000  9000
C  2018-10-03  1000  2000
C  2018-10-30  1000  2000
D  2018-08-20  2000  3000

Now I'm trying to substract the values based on the month (and based on ID), so I need to create new column 'Month remanining' and the values that remain after subsctracting transfer over to the next month:

ID  Date       Paid  Due     Month     Month_Remaining
A  2019-07-01  40000 50000  2019-07      10000
B  2018-08-20  1500  9000   2018-08      7500
B  2018-08-20  2500  9000   2018-08      5000
B  2018-09-18  3000  9000   2018-09      2000
C  2018-10-03  1000  2000   2018-10      1000
C  2018-10-30  1000  2000   2018-10      0
D  2018-08-20  2000  3000   2018-08      1000

Is it possible in Pandas?

Upvotes: 0

Views: 50

Answers (1)

Serge Ballesta
Serge Ballesta

Reputation: 148880

If Date is a plain string, you can build the Month column with either:

df['Month'] = pd.to_datetime(df['Date']).dt.strfime('%Y-%m')

or

df['Month'] = df['Date'].str.slice(0,7)

If it is a Datetime columns, you can use:

df['Month'] = df['Date'].dt.strfime('%Y-%m')

You can then build the Month_Remaining column with:

df['Month_Remaining'] = df['Due'] - df.groupby(['ID'])['Paid'].cumsum()

You should finaly get:

  ID        Date   Paid    Due    Month  Month_Remaining
0  A  2019-07-01  40000  50000  2019-07            10000
1  B  2018-08-20   1500   9000  2018-08             7500
2  B  2018-08-20   2500   9000  2018-08             5000
3  B  2018-09-18   3000   9000  2018-09             2000
4  C  2018-10-03   1000   2000  2018-10             1000
5  C  2018-10-30   1000   2000  2018-10                0
6  D  2018-08-20   2000   3000  2018-08             1000

Upvotes: 1

Related Questions