Python 和 Pandas - 确定帐单是否逾期

Question

我有一个包含时间序列和分类数据的数据框。

  ╔═════════════════════════════════════════════╗
  ║ Name       BillDate             Bill Status ║
  ╠═════════════════════════════════════════════╣
  ║ Company A  2015-07-22 15:51:00  Paid        ║
  ║ Company B  2015-01-31 12:01:00  Unpaid      ║
  ║ Company C  2016-01-12 00:00:00  Unpaid      ║
  ╚═════════════════════════════════════════════╝

我正在尝试添加另一列，告诉我帐单是否因两个因素而逾期。第一个因素是当前日期是否为 BillDate+180 天或更长，第二个因素是账单状态为未付。

我可能正在热衷于如何做到这一点。我的想法是执行以下操作:

   billpayperiod = timedelta(days = 180)
   currentdate = datetime.now()
   df['Bill Due Date'] = df['BillDate'].apply(lambda x: x + billpayperiod)

然后创建一些函数来检查是否

 currendate > Bill Due Date and Bill Status = unpaid. 
 If True = Overdue
 If False = No Due,
 If Bill Status = paid, then Paid. 

我很欣赏你的想法:1.这个方法是否有意义？2.帮助创建执行检查的函数

由于我对 Excel 的了解要好得多，因此我会使用它来执行以下操作:

  Create the Bill Date + 180 column (name it DueDate
  Set a cell = currentdate
  Create a new column: formula    IF(BillStatus="Paid","Paid",IF(AND(BillStatus="Unpaid",currentdate>DueDate),"Overdue","Not Overdue"))    

Answer 1

IIUC 这将满足您的要求:

In [21]:
df[(((df['BillDate'] - dt.datetime.now()).dt.days).abs() > 180) & (df['Bill Status'] == 'Unpaid')]

Out[21]:
        Name            BillDate Bill Status
1  Company B 2015-01-31 12:01:00      Unpaid

我们可以调用dt.days时间增量并比较绝对值:

In [25]:
(df['BillDate'] - dt.datetime.now()).dt.days

Out[25]:
0   -182
1   -354
2     -8
Name: BillDate, dtype: int64

In [24]:
(df['BillDate'] - dt.datetime.now()).dt.days
((df['BillDate'] - dt.datetime.now()).dt.days).abs()

Out[24]:
0    182
1    354
2      8
Name: BillDate, dtype: int64

编辑

要设置新状态，您可以定义几个掩码并使用np.where:

In [29]:
import pandas as pd
import numpy as np
import datetime as dt
overdue = (((df['BillDate'] - dt.datetime.now()).dt.days).abs() > 180) & (df['Bill Status'] == 'Unpaid')
paid = (df['Bill Status'] == 'Paid')
df['new status'] = np.where(paid, 'paid', np.where(overdue, 'overdue', 'no due'))
df
​
Out[29]:
        Name            BillDate Bill Status new status
0  Company A 2015-07-22 15:51:00        Paid       paid
1  Company B 2015-01-31 12:01:00      Unpaid    overdue
2  Company C 2016-01-12 00:00:00      Unpaid     no due