Dataframe group certain rows

Question

I am wondering if there is a way or I just need to loop through my dataframe to group rows where my index is greater than, in other words based in the following dataframe:

import pandas as pd
data = {"Name": ["Apple", "Carrot", "Pear", "Tomato", "Orage", "Pineapple", "Mandarin"],"Value": [10,5,6,3,8,9,9]}
df = pd.DataFrame(data)
print(df)

I will get something like

        Name  Value
0      Apple     10
1     Carrot      5
2       Pear      6
3     Tomato      3
4      Orage      8
5  Pineapple      9
6   Mandarin      9

If I sum all the values I should get 50, is there a way that I can create a "summary" of this dataframe, for instance display the first 3 and group the rest so my results looks like:

        Name  Value
0      Apple     10
1     Carrot      5
2       Pear      6
3      Other     29

Where 29 is 50 minus (10+5+6) and other was just a random name i gave it?

Answer 1

This is more of a programming than a data science question and would therefore be better suited for the stackoverflow page. There are two ways of doing this, depending on the number of items/rows you want to leave separate and if you can manually provide them. Both methods first change the Name column to group names in your example and then group and sum the rows.

import numpy as np

# manually provide three items which should not be grouped
(
    df
    .assign(Name = lambda x: np.where(~x["Name"].isin(["Apple", "Carrot", "Pear"]), "Other", x["Name"]))
    .groupby("Name")
    .sum()
    .reset_index()
)

# automatically use items after third row for grouping
(
    df
    .assign(Name = lambda x: np.where(x["Name"].isin(x["Name"].iloc[3:]), "Other", x["Name"]))
    .groupby("Name")
    .sum()
    .reset_index()
)