-1

Here is the data:

col1 col2 col3
category1 subcat1 10
category1 subcat2 15
category1 subcat3 1
category1 subcat4 23
category2 subcat1 10
category2 subcat2 99
category2 subcat3 23
category2 subcat4 12
category2 subcat5 10
category2 subcat6 11
category2 subcat7 45
category2 subcat8 105
category3 subcat1 2
category3 subcat2 9
category3 subcat3 14
category3 subcat4 25
... ... ...

What i want is something like this (max3 subcat + values per category):

category1:
          subcat1  10
          subcat2  15
          subcat3  10
category2:
          subcat2  99
          subcat7  45
          subcat8  105
category3:
          subcat2  9
          subcat3  14
          subcat4  25

Many thanks in advance.

I´m fairly new here, hope for your understanding while reading this explaination. :)

Sean.H
  • 640
  • 1
  • 6
  • 18
Anton
  • 3
  • 2

2 Answers2

0

Here is work around, I still think there is a better way of doing it, but this should help you out for now:

result = pd.DataFrame()
for item in df['category'].unique():
    result = result.append(df[df.category == item].nlargest(3, 'value'))

Basically you use the nlargest method on a sub-dataframe for each category, in a kind of manual groupby function, because it is not supported on groupby objects.

Yashar
  • 762
  • 8
  • 16
  • Hi Yashar, thanks for the hint! I will try this a bit later today and see if I can get to make it work :) – Anton Dec 06 '22 at 08:42
0

As for your demo data:

df = pd.DataFrame({'col1': ['category1', 'category1','category1', 'category1', 'category2', 'category2','category2', 'category2', 'category2', 'category2','category2', 'category2', 'category3', 'category3','category3', 'category3'],
                  'col2': ['subcat1', 'subcat2', 'subcat3', 'subcat4', 'subcat1', 'subcat2', 'subcat3', 'subcat4', 'subcat5', 'subcat6', 'subcat7', 'subcat8', 'subcat1', 'subcat2', 'subcat3', 'subcat4'],
                  'col3': [10, 15, 1, 23, 10, 99, 23, 12, 10, 11, 45, 105, 2, 9, 14, 25]})
print(df)

    col1    col2    col3
0   category1   subcat1 10
1   category1   subcat2 15
2   category1   subcat3 1
3   category1   subcat4 23
4   category2   subcat1 10
5   category2   subcat2 99
6   category2   subcat3 23
7   category2   subcat4 12
8   category2   subcat5 10
9   category2   subcat6 11
10  category2   subcat7 45
11  category2   subcat8 105
12  category3   subcat1 2
13  category3   subcat2 9
14  category3   subcat3 14
15  category3   subcat4 25

Here is the solution offered by joris in another question:

df_agg = df.groupby(['col1', 'col2']).agg({'col3': sum})
a = df_agg['col3'].groupby('col1', group_keys=False).nlargest(3)

print(a)
col1       col2   
category1  subcat4     23
           subcat2     15
           subcat1     10
category2  subcat8    105
           subcat2     99
           subcat7     45
category3  subcat4     25
           subcat3     14
           subcat2      9
Sean.H
  • 640
  • 1
  • 6
  • 18
  • 1
    Thank you, this solution linked is what I was looking for. On the way to there, I also discovered another possibility to do so, using pandas pivot tables. I´m giving here the link, just in case someone has a similar issue: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.pivot_table.html – Anton Dec 07 '22 at 11:08