python - 需要帮助从 pandas 数据框中过滤前 3 个计数

Question

您好，我希望通过时间线获取数据框标题下的前 3 个字符串计数，下面的代码能够提取所有字符串的计数，但如何应用 top3 或 5 的过滤器来仅获取这些字符串。

df['Date'] = pd.to_datetime(df['Date'])
table1 = pd.crosstab([df['name'], df['city']], df['Date'].dt.to_period('q'))
table.columns = [table.columns.year, table.columns.strftime('q')]

print(table1)

#Data Look like below

name    age  city        Date                  country   hight  MessageList  gender
Tom     10   NewYork       1/1/2021 08:35:58Z  US        NaN      X List      Male
Mark     5   London        5/1/2021 08:35:58Z  UK        NaN      X List      Male
Pam      7   London        3/6/2021 08:35:58Z  UK        NaN      Y List      Female
Tom     18   California    4/6/2021 08:35:58Z  US        163      Y List      Male
Lena    23   NewYork     12/12/2020 08:35:58Z  US        NaN      Y List      Female
Ben     17   Colombo     11/12/2020 08:35:58Z  Srilanka  NaN      X List      Male
Lena    23   Paris         8/1/2020 08:35:58Z  France    NaN      Y List      Female
Ben     51   Colombo       7/1/2020 08:35:58Z  Srilanka  NaN      Z List      Male
Tom     18   Paris         1/1/2021 08:35:58Z  France    NaN      Z List      Male
Mark     5   Paris         5/1/2021 08:35:58Z  Japan     NaN      Z List      Male
Tom     18   London        3/6/2021 08:35:58Z  UK        NaN      X List      Male
Tom     18   Paris         4/6/2021 08:35:58Z  France    163      Z List      Male
Tom     10   NewYork       1/1/2021 08:35:58Z  US        NaN      X List      Male
Mark     5   London        5/1/2021 08:35:58Z  UK        NaN      X List      Male
Pam      7   London        3/6/2021 08:35:58Z  UK        NaN      Y List      Female
Tom     18   California    4/6/2021 08:35:58Z  US        163      Y List      Male
Lena    23   NewYork     12/12/2020 08:35:58Z  US        NaN      Y List      Female
Ben     17   Colombo     11/12/2020 08:35:58Z  India     NaN      X List      Male
Lena    23   Paris         8/1/2020 08:35:58Z  France    NaN      Y List      Female
Ben     51   Colombo       7/1/2020 08:35:58Z  India     NaN      Z List      Male
Tom     18   Paris         1/1/2021 08:35:58Z  France    NaN      Z List      Male
Mark     5   Paris         5/1/2021 08:35:58Z  Japan     NaN      Z List      Male
Tom     18   London        3/6/2021 08:35:58Z  UK        NaN      X List      Male
Tom     18   Paris         4/6/2021 08:35:58Z  France    163      Z List      Male
Tom     10   NewYork       1/1/2021 08:35:58Z  US        NaN      X List      Male
Mark     5   London        5/1/2021 08:35:58Z  UK        NaN      X List      Male
Pam      7   London        3/6/2021 08:35:58Z  UK        NaN      Y List      Female
Tom     18   California    4/6/2021 08:35:58Z  US        163      Y List      Male
Lena    23   NewYork     12/12/2020 08:35:58Z  US        NaN      Y List      Female
Ben     17   Colombo     11/12/2020 08:35:58Z  Srilanka  NaN      X List      Male
Lena    23   Paris         8/1/2020 08:35:58Z  France    NaN      Y List      Female
Ben     51   Colombo       7/1/2020 08:35:58Z  Srilanka  NaN      Z List      Male
Tom     18   Paris         1/1/2021 08:35:58Z  France    NaN      Z List      Male
Mark     5   Paris         5/1/2021 08:35:58Z  Japan     NaN      Z List      Male
Tom     18   London        3/6/2021 08:35:58Z  UK        NaN      X List      Male
Tom     18   California    4/6/2021 08:35:58Z  US        163      Y List      Male
Lena    23   NewYork     12/12/2020 08:35:58Z  US        NaN      Y List      Female
Ben     17   Colombo     11/12/2020 08:35:58Z  India     NaN      X List      Male
Lena    23   Paris         8/1/2020 08:35:58Z  France    NaN      Y List      Female
Ben     51   Colombo       7/1/2020 08:35:58Z  India     NaN      Z List      Male
Tom     18   Paris         1/1/2021 08:35:58Z  France    NaN      Z List      Male
Mark     5   Paris         5/1/2021 08:35:58Z  Japan     NaN      Z List      Male
Tom     18   London        3/6/2021 08:35:58Z  UK        NaN      X List      Male
Tom     18   Paris         4/6/2021 08:35:58Z  France    163      Z List      Male
Tom     10   NewYork       1/1/2021 08:35:58Z  US        NaN      X List      Male
Mark     5   London        5/1/2021 08:35:58Z  UK        NaN      X List      Male
Pam      7   London        3/6/2021 08:35:58Z  UK        NaN      Y List      Female
Tom     18   California    4/6/2021 08:35:58Z  US        163      Y List      Male
Lena    23   NewYork     12/12/2020 08:35:58Z  US        NaN      Y List      Female
Ben     17   Colombo     11/12/2020 08:35:58Z  Srilanka  NaN      X List      Male
Lena    23   Paris         8/1/2020 08:35:58Z  France    NaN      Y List      Female
Ben     51   Colombo       7/1/2020 08:35:58Z  Srilanka  NaN      Z List      Male
Tom     18   Paris         1/1/2021 08:35:58Z  France    NaN      Z List      Male
Mark     5   Paris         5/1/2021 08:35:58Z  Japan     NaN      Z List      Male
Tom     18   London        3/6/2021 08:35:58Z  UK        NaN      X List      Male

#Output expected 
Quarter        Q1     Q2    Q3    Q4    Total
city    US     12     8     24    11     55
        Japan  6      7     5     3      21
        Italy  8      3     2     5      18

如何在行和列上保留过滤器，例如在 Excel 中进行旋转，请帮忙

Answer 1

我愿意执行以下操作。创建四分之一列:

df["quarter"] = df["Date"].dt.to_period("q")

然后旋转数据框，删除列总和，对行总和进行排序并返回前 3 行:

df.pivot_table(
    index="city",
    columns="quarter",
    values="name",
    aggfunc="count",
    fill_value=0,
    margins=True,
)[:-1].sort_values(by="All", ascending=False)[:3]

输出:

quarter  2020Q3  2020Q4  2021Q1  2021Q2  All
city                                        
Paris         5       0       5       8   18
London        0       0       9       4   13
Colombo       5       5       0       0   10