Create a new column to return a string based on the values of other columns

  dataframe, numpy, pandas, python

I have the following dataframe:

 account_id contract_id date_activated  term_months 2020-08-01 00:00:00 2020-09-01 00:00:00 2020-10-01 00:00:00 2020-11-01 00:00:00 2020-12-01 00:00:00 2021-01-01 00:00:00 2021-02-01 00:00:00 2021-03-01 00:00:00 2021-04-02 00:00:00
0   12454545    ADD 2020-08-24  11  620.984848  620.984848  620.984848  620.984848  0.0 0.0 0.00    0.00    0.00
1   12454545    ADFGG   2021-02-01  12  0.000000    0.000000    0.000000    0.000000    0.0 0.0 687.94  687.94  687.94
2   1646468 ASDADGAD    2020-08-03  7   1504.700000 1504.700000 1504.700000 1504.700000 1504.7  1504.7  1504.70 0.00    0.00
3   5454555 ADGA    2020-08-10  8   528.000000  528.000000  528.000000  528.000000  528.0   528.0   0.00    0.00    528.00
4   48654   GHDG    2018-10-18  6   100.000000  100.000000  100.000000  0.000000    0.0 0.0 0.00    0.00    0.00

I want the following output:

 account_id contract_id date_activated  term_months status  2020-08-01 00:00:00 2020-09-01 00:00:00 2020-10-01 00:00:00 2020-11-01 00:00:00 2020-12-01 00:00:00 2021-01-01 00:00:00 2021-02-01 00:00:00 2021-03-01 00:00:00 2021-04-02 00:00:00
0   12454545    ADD 2020-08-24  11  Active  620.984848  620.984848  620.984848  620.984848  0.0 0.0 0.00    0.00    0.00
1   12454545    ADFGG   2021-02-01  12  Active  0.000000    0.000000    0.000000    0.000000    0.0 0.0 687.94  687.94  687.94
2   1646468 ASDADGAD    2020-08-03  7   Inactive    1504.700000 1504.700000 1504.700000 1504.700000 1504.7  1504.7  1504.70 0.00    0.00
3   5454555 ADGA    2020-08-10  8   Active  528.000000  528.000000  528.000000  528.000000  528.0   528.0   0.00    0.00    528.00
4   48654   GHDG    2018-10-18  6   Inactive    100.000000  100.000000  100.000000  0.000000    0.0 0.0 0.00    0.00    0.00

Each account can have one or more contracts. I have an external data which should be set manually to determine the current date, in this case it’s "May 1, 2021".

I need to identify each contract as either "Active" or "Inactive" based on the payments (headers with date) and the term in months. This will be in a new column "status". There is a grace period of 4 months for those that have stopped paying.

If a contract has been paying every month (no non-payment) equal to its term and has already ended its term before the current date (based on term_months), it will considered as "Inactive". Refer to contract_id "ASDADGAD".

If a contract has been paying regularly and has not expired as of the current date, it will be considered as "Active". Refer to contract_id "ADFGG".

If a contract has not yet expired (based on the term) and there are no payments on and before the current date but it is still within four months, it is still considered as "Active". Refer to contract_id "ADD" and "ADGA".

But, if the non-payments are beyond four months for a contract, it will be considered as "Inactive". Refer to contract_id "GHDG".

Here’s the dictionary for the result:

{'account_id': {0: 12454545, 1: 12454545, 2: 1646468, 3: 5454555, 4: 48654},
 'contract_id': {0: 'ADD', 1: 'ADFGG', 2: 'ASDADGAD', 3: 'ADGA', 4: 'GHDG'},
 'date_activated': {0: Timestamp('2020-08-24 00:00:00'),
  1: Timestamp('2021-02-01 00:00:00'),
  2: Timestamp('2020-08-03 00:00:00'),
  3: Timestamp('2020-08-10 00:00:00'),
  4: Timestamp('2018-10-18 00:00:00')},
 'term_months': {0: 11, 1: 12, 2: 7, 3: 8, 4: 6},
 'status': {0: 'Active',
  1: 'Active',
  2: 'Inactive',
  3: 'Active',
  4: 'Inactive'},
 datetime.datetime(2020, 8, 1, 0, 0): {0: 620.984848484848,
  1: 0.0,
  2: 1504.69999999999,
  3: 528.0,
  4: 100.0},
 datetime.datetime(2020, 9, 1, 0, 0): {0: 620.984848484848,
  1: 0.0,
  2: 1504.69999999999,
  3: 528.0,
  4: 100.0},
 datetime.datetime(2020, 10, 1, 0, 0): {0: 620.984848484848,
  1: 0.0,
  2: 1504.69999999999,
  3: 528.0,
  4: 100.0},
 datetime.datetime(2020, 11, 1, 0, 0): {0: 620.984848484848,
  1: 0.0,
  2: 1504.69999999999,
  3: 528.0,
  4: 0.0},
 datetime.datetime(2020, 12, 1, 0, 0): {0: 0.0,
  1: 0.0,
  2: 1504.69999999999,
  3: 528.0,
  4: 0.0},
 datetime.datetime(2021, 1, 1, 0, 0): {0: 0.0,
  1: 0.0,
  2: 1504.69999999999,
  3: 528.0,
  4: 0.0},
 datetime.datetime(2021, 2, 1, 0, 0): {0: 0.0,
  1: 687.94,
  2: 1504.69999999999,
  3: 0.0,
  4: 0.0},
 datetime.datetime(2021, 3, 1, 0, 0): {0: 0.0,
  1: 687.94,
  2: 0.0,
  3: 0.0,
  4: 0.0},
 datetime.datetime(2021, 4, 2, 0, 0): {0: 0.0,
  1: 687.94,
  2: 0.0,
  3: 528.0,
  4: 0.0}}

Source: Python Questions

LEAVE A COMMENT