如何将列转换为矩阵、按日期筛选和排序索引？

Question

我对python还很陌生，正在努力学习。我有两列数据，我想用来创建一个矩阵。我正在处理大量数据，但我创建了一个示例集，如下所示：

import pandas as pd

data = [
    ['10/31/2019','12345','0','1'],['10/31/2019','54321','1','0'],['10/31/2019','43215','2','2'],
    ['10/31/2019','12346','0','1'],['10/31/2019','54322','1','0'],['9/28/2019','53215','3','3'],
    ['9/28/2019','73215','4','4'],['9/28/2019','13215','5','5'],['9/28/2019','42215','6','6'],
    ['9/28/2019','83215','7','7'],['9/28/2019','93215','8','8'],['9/28/2019','12215','9','9'],
    ['9/28/2019','73212','10','10'],['8/31/2019','22215','5','5'],['8/31/2019','56215','6','6']
]

df = pd.DataFrame(data, columns = ['Date','AccountNo','BalanceTier','PriorMonthBalanceTier'])
df

这给了我以下输出：

    Date    AccountNo BalanceTier   PriorMonthBalanceTier   
0   10/31/2019  12345     0           1
1   10/31/2019  54321     1           0
2   10/31/2019  43215     2           2
3   10/31/2019  12346     0           1
4   10/31/2019  54322     1           0
5   9/28/2019   53215     3           3
6   9/28/2019   73215     4           4
7   9/28/2019   13215     5           5
8   9/28/2019   42215     6           6
9   9/28/2019   83215     7           7
10  9/28/2019   93215     8           8
11  9/28/2019   12215     9           9
12  9/28/2019   73212     10         10
13  8/31/2019   22215     5           5
14  8/31/2019   56215     6           6

然后我尝试从这个数据中得到一个摘要矩阵，其中列是'BalanceTier'，行是'PriorMonthBalanceTier'。每个矩阵都有一个特定的日期。例如，如果我按10/31筛选，摘要矩阵将显示帐户如何从9/30（PriorMonthBalanceTier）的一个余额层转换到10/31（BalanceTier）的另一个余额层。我尝试了下面的代码，但出现了以下错误：

TypeError: filter function returned a DataFrame, but expected a scalar bool

matrix = df.groupby('Date').filter(lambda x: x == '10/31/2019').groupby(['PriorMonthBalanceTier','BalanceTier']).size().unstack().fillna(0)
matrix

虽然结果没有意义，但代码在没有筛选器的情况下运行，但列/行没有排序。对于exmaple，行/列的顺序是0、1、10、2、3、4。。。我希望它是0，1，2，3，4，…，10。感谢您的帮助，因为我才刚刚开始！你知道吗

10/31/19的输出应为：

Answer 1

你想到这个了吗？你知道吗

df.groupby('Date') \
  .filter(lambda grp: grp.Date.iloc[0]=="10/31/2019") \
  .groupby(['PriorMonthBalanceTier','BalanceTier'] \
  .size().unstack().fillna(0).astype(int)

BalanceTier            0  1  2
PriorMonthBalanceTier         
0                      0  2  0
1                      2  0  0
2                      0  0  1

编辑：这样更简单：

df[df.Date.eq("10/31/2019")] \ 
      .groupby(['PriorMonthBalanceTier','BalanceTier']) \ 
      .size().unstack().fillna(0).astype(int)

Answer 2

我想你在找^{}

因为您的层变量是字符串类型，所以排序将是0 1 10 2 ...。您应该使用.astype(int)将数据类型转换为整数，以获得基于数值的排序。你知道吗

s1 = pd.Series(['2', '10', '0', '11' , '3', '1'])

print(s1.sort_values().values)
# ['0' '1' '10' '11' '2' '3']
print(s1.astype(int).sort_values().values)
# [ 0  1  2  3 10 11]

所以这里有一个可能的解决方案：

tbl = pd.crosstab(
    df['PriorMonthBalanceTier'].astype(int), 
    df['BalanceTier'].astype(int)
)
print(tbl)
# BalanceTier            0   1   2   3   4   5   6   7   8   9   10
# PriorMonthBalanceTier
# 0                       0   2   0   0   0   0   0   0   0   0   0
# 1                       2   0   0   0   0   0   0   0   0   0   0
# 2                       0   0   1   0   0   0   0   0   0   0   0
# 3                       0   0   0   1   0   0   0   0   0   0   0
# 4                       0   0   0   0   1   0   0   0   0   0   0
# 5                       0   0   0   0   0   2   0   0   0   0   0
# 6                       0   0   0   0   0   0   2   0   0   0   0
# 7                       0   0   0   0   0   0   0   1   0   0   0
# 8                       0   0   0   0   0   0   0   0   1   0   0
# 9                       0   0   0   0   0   0   0   0   0   1   0
# 10                      0   0   0   0   0   0   0   0   0   0   1

应用筛选

同样的技术可以应用于筛选列，但是您可能会发现级别3 4 5 ...消失了，因为它们不在筛选数据集中。你知道吗

ind = (df['Date'].values == '10/31/2019')
tbl = pd.crosstab(
    df.loc[ind, 'PriorMonthBalanceTier'].astype(int), 
    df.loc[ind, 'BalanceTier'].astype(int), 
    dropna=False
)
print(tbl)
# BalanceTier            0  1  2
# PriorMonthBalanceTier
# 0                      0  2  0
# 1                      2  0  0
# 2                      0  0  1

因此，如果要在过滤后保留所有级别，可能需要^{}。你知道吗

ind = (df['Date'].values == '10/31/2019')

col1 = df['PriorMonthBalanceTier'].astype(int)
col1 = pd.Categorical(col1[ind], col1.sort_values().unique())

col2 = df['BalanceTier'].astype(int)
col2 = pd.Categorical(col2[ind], col2.sort_values().unique())

tbl = pd.crosstab(
    col1, col2, 
    rownames=['PriorMonthBalanceTier'], 
    colnames=['BalanceTier'], 
    dropna=False
)
print(tbl)
# BalanceTier            0   1   2   3   4   5   6   7   8   9   10
# PriorMonthBalanceTier
# 0                       0   2   0   0   0   0   0   0   0   0   0
# 1                       2   0   0   0   0   0   0   0   0   0   0
# 2                       0   0   1   0   0   0   0   0   0   0   0
# 3                       0   0   0   0   0   0   0   0   0   0   0
# 4                       0   0   0   0   0   0   0   0   0   0   0
# 5                       0   0   0   0   0   0   0   0   0   0   0
# 6                       0   0   0   0   0   0   0   0   0   0   0
# 7                       0   0   0   0   0   0   0   0   0   0   0
# 8                       0   0   0   0   0   0   0   0   0   0   0
# 9                       0   0   0   0   0   0   0   0   0   0   0
# 10                      0   0   0   0   0   0   0   0   0   0   0