Python Pandas dataframe : For each month of the year, 如果月份不存在，则将月份最后一天的日期添加到索引中，或者删除重复项

Question

首先，我为这个有点复杂的标题道歉。

我努力寻找一种方法来简洁地描述我几个小时以来一直在努力实现的目标。请允许我更清楚地解释这个问题(仅供引用，我使用的是 Python 3.6 和 Pandas 20.3)。

我有一个 MultiIndex DataFrame，目前看起来像这样:

                            d   p
name            paymentDate

Rib Smoth       2011-01-01  0   0
                2011-02-01  0   0
                2011-03-01  0   0
                2011-04-01  0   0
                2011-05-01  0   0
                2011-06-01  0   0
                2011-07-01  0   0
                2011-08-01  0   0
                2011-09-01  0   0
                2011-10-01  0   0
                2011-11-01  0   0
                2011-12-01  0   0
Balrud Big      2011-01-02  1   1
                2011-01-12  2   1
                2011-02-13  2   1
                2011-03-28  3   1
                2011-04-16  2   1
                2011-06-09  1   1
                2011-06-27  3   1
                2011-07-17  2   1
                2011-09-05  1   1
                2011-09-16  2   1
                2011-10-29  3   1
                2011-11-06  1   0
Mr. Bean        2011-01-01  0   0
                2011-02-02  1   0
                        .
                        .
                        .

如您所见，第二层是一系列日期，指的是人们支付房租的日期。一些租房者在某些月份错过了付款，或者在其他月份支付了不止一次。我需要“同质化”paymentDate，换句话说，我希望数据框中所有租户的第二级正好有 12 个条目。

我相信下面应该解决它，但不知道该怎么做:

1. 对于每个承租人，如果他们没有任何给定月份的 paymentDate，则插入该行，paymentDate 是该月的最后一天，和 d=3 p=1。在上面的示例中，这需要将五月的行添加到 Balrud Big，例如 2011-05-31 1 3。

2. 对于每个承租人，我还需要删除同一个月有两个或多个 paymentDate 的情况。同样，如果我们查看 Balrud Big，我们会看到一月份的两个条目。只要有这样的重复项，我希望只保留最新条目，在本例中为 2011-01-12 2 1。

   <

如果将以上内容应用于所示示例，注意到 Balrud Big 有多个条目丢失和重复的情况，我希望最终得到:

                            d   p
name            paymentDate

Rib Smoth       2011-01-01  0   0
                2011-02-01  0   0
                2011-03-01  0   0
                2011-04-01  0   0
                2011-05-01  0   0
                2011-06-01  0   0
                2011-07-01  0   0
                2011-08-01  0   0
                2011-09-01  0   0
                2011-10-01  0   0
                2011-11-01  0   0
                2011-12-01  0   0
Balrud Big      2011-01-12  2   1
                2011-02-13  2   1
                2011-03-28  3   1
                2011-04-16  2   1
                2011-05-31  3   1
                2011-06-27  3   1
                2011-07-17  2   1
                2011-08-31  3   1
                2011-09-16  2   1
                2011-10-29  3   1
                2011-11-06  1   0
                2011-12-31  3   1
Mr. Bean        2011-01-01  0   0
                2011-02-02  1   0
                        .
                        .
                        .

最后，我可以用整数 1-12(代表 12 个月)重新索引第二层，安全地知道每个租户都有 确切 12 个月的历史记录。然后，通过使用 DataFrame.pivot 或其他方式，转换数据框以得到如下内容:

                d1  p1  d2  p2  d3  p3  d4  p4  d5  p5  d6  p6  d7  p7  d8  p8  d9  p9  d10  p10  d11  p11  d12  p12
name

Rib Smoth       0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0    0    0    0    0    0
Balrud Big      2   1   2   1   3   1   2   1   3   1   3   1   2   1   3   1   2   1   3    1    1    0    3    0
Mr. Bean        0   0   1   0   ...(and so on)

这似乎是一项相当复杂的任务，但我认为使用 DateTime 或 Pandas 广泛的日期/时间功能可能会有一些巧妙的技巧。我已经尝试了一段时间，但仍然感到困惑。

非常感谢您在此方面的任何帮助，在此先感谢您!

编辑:我有一个解决方案，但在我分享之前需要做一些整理。

Answer 1

首先，创建示例数据

import pandas as pd
import numpy as np

arrays = [
    np.array(['Rib Smoth']*12 + ['Balrud Big']*12 + ['Mr. Bean']*2),
    pd.to_datetime([
        '2011-01-01', '2011-02-01', '2011-03-01', '2011-04-01', '2011-05-01',
        '2011-06-01', '2011-07-01', '2011-08-01', '2011-09-01', '2011-10-01',
        '2011-11-01', '2011-12-01', '2011-01-02', '2011-01-12', '2011-02-13',
        '2011-03-28', '2011-04-16', '2011-06-09', '2011-06-27', '2011-07-17',
        '2011-09-05', '2011-09-16', '2011-10-29', '2011-11-06', '2011-01-01',
        '2011-02-02'])
]
df = pd.DataFrame(
    index=pd.MultiIndex.from_tuples(list(zip(*arrays)),
                                    names=['name', 'paymentDate'])
)
df['d'] = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 3, 2, 1, 3, 2, 1, 2, 3, 1, 0, 1]
df['p'] = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0]
# print(df.head(3))
#                        d  p
# name      paymentDate      
# Rib Smoth 2011-01-01   0  0
#           2011-02-01   0  0
#           2011-03-01   0  0

将 paymentDate 从索引级别移动到列

df = df.reset_index(level='paymentDate')
# print(df.head(3))
#           paymentDate  d  p
# name                       
# Rib Smoth  2011-01-01  0  0
# Rib Smoth  2011-02-01  0  0
# Rib Smoth  2011-03-01  0  0

创建一个系列以在按名称和月份分组时使用

payment_month = df['paymentDate'].dt.to_period('M').rename('month')
# print(payment_month.head(3))
# name
# Rib Smoth    2011-01
# Rib Smoth    2011-02
# Rib Smoth    2011-03
# Name: month, dtype: period[M]

分组，只保留每个月的最后一笔付款

df = df.groupby(['name', payment_month])[['paymentDate', 'd', 'p']].last()
# print(df.head(3))
#                    paymentDate  d  p
# name       month                    
# Balrud Big 2011-01  2011-01-12  2  1  # Note: last payment in 2011-01
#            2011-02  2011-02-13  2  1
#            2011-03  2011-03-28  3  1

将索引设置为每个月的最后一天，以便以后在没有付款的月份使用

df.index = df.index.set_levels(df.index.levels[-1].to_timestamp('M'), 'month')
# print(df.head(3))
#                       paymentDate  d  p
# name       month                       
# Balrud Big 2011-01-31  2011-01-12  2  1
#            2011-02-28  2011-02-13  2  1
#            2011-03-31  2011-03-28  3  1

通过将每个名称与所有月份组合，用缺失月份的行填充数据框

all_names = df.index.get_level_values('name').unique()
all_months = pd.date_range('2011-01-01', '2011-12-31', freq='M')
df = df.reindex(pd.MultiIndex.from_product(
    [all_names, all_months],
    names=['name', 'all_months']
))
# print(df.head())
#                       paymentDate    d    p
# name       all_months                      
# Balrud Big 2011-01-31  2011-01-12  2.0  1.0
#            2011-02-28  2011-02-13  2.0  1.0
#            2011-03-31  2011-03-28  3.0  1.0
#            2011-04-30  2011-04-16  2.0  1.0
#            2011-05-31         NaT  NaN  NaN # This row is new!

用想要的值完成数据

no_payment = df['paymentDate'].isnull()
df.loc[no_payment, ['d', 'p']] = [3, 1]
df.loc[no_payment, ['paymentDate']] = df.index.get_level_values(-1)[no_payment]
# print(df.head())
#                       paymentDate    d    p
# name       all_months                      
# Balrud Big 2011-01-31  2011-01-12  2.0  1.0
#            2011-02-28  2011-02-13  2.0  1.0
#            2011-03-31  2011-03-28  3.0  1.0
#            2011-04-30  2011-04-16  2.0  1.0
#            2011-05-31  2011-05-31  3.0  1.0 # The column values are fixed!

最后，用正确值的列替换临时索引级别

df = df.set_index([df.index.get_level_values('name'), 'paymentDate'])
# print(df.head(3))
#                           d    p
# name       paymentDate          
# Balrud Big 2011-01-12   2.0  1.0
#            2011-02-13   2.0  1.0
#            2011-03-28   3.0  1.0

恢复正确的数据类型

df['d'] = df['d'].astype(int)
df['p'] = df['p'].astype(int)
# print(df.head(3))
#                         d  p
# name       paymentDate      
# Balrud Big 2011-01-12   2  1
#            2011-02-13   2  1
#            2011-03-28   3  1

运行一些基本测试:

assert (df.loc[('Rib Smoth', slice(None))] == 0).all().all()
assert ('Balrud Big', '2011-01-02') not in df.index
assert ('Balrud Big', '2011-06-09') not in df.index
assert ('Balrud Big', '2011-09-05') not in df.index
assert (df.loc[('Balrud Big', '2011-01-12')] == [2, 1]).all()
assert (df.loc[('Balrud Big', '2011-12-31')] == [3, 1]).all()