Need help in filtering top 3 counts from pandas dataframe

user_16058167 0
Viewed 142

Hi I am looking to get the top 3 string counts under the data frame heads with time lines with below code able to extract the counts of all strings but how can I apply a filter of top3 or 5 to get only those.

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

How can I keep a filter on both rows and columns like pivoting in excel please help

pandas analytics dataframe numpy python
2 Answers

I would to the following. Create a quarter column:

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

Then pivot the data frame, remove the column sum, sort the row sum and return the first 3 rows:

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

Output:

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
user_107430470

Similar to the answer of @Henrik Bo, following your way with crosstab:

table1 = pd.crosstab([ df['city']], df['Date'].dt.to_period('q'))
table1["total"] = table1.sum(axis=1)
table1.sort_values(by="total",ascending=False)[:3]

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

When you are not interested in quarter period:

table2 = pd.crosstab([ df['city']],  df['Date'].dt.quarter.apply(lambda x: "Q" + str(x)))
table2["total"] = table2.sum(axis=1)
table2.sort_values(by="total",ascending=False)[:3]

Date     Q1  Q2  Q3  Q4  total
city                          
Paris     5   8   5   0     18
London    9   4   0   0     13
Colombo   0   0   5   5     10
user_151632570
Related