Replace last value(s) of group with NaN

Question

My goal is to replace the last value (or the last several values) of each id with NaN. My real dataset is quite large and has groups of different sizes.

Example:

import pandas as pd

ids = [1,1,1,1,1,1,2,2,2,2,2,2,3,3,3,3,3,3]
year = [2000,2001,2002,2003,2004,2005,1990,1991,1992,1993,1994,1995,2010,2011,2012,2013,2014,2015]
percent = [120,70,37,40,50,110,140,100,90,5,52,80,60,40,70,60,50,110]

dictex ={"id":ids,"year":year,"percent [%]": percent}
dfex = pd.DataFrame(dictex)
print(dfex)

           id               year           percent [%]
0           1               2000                  120
1           1               2001                   70
2           1               2002                   37
3           1               2003                   40
4           1               2004                   50
5           1               2005                  110
6           2               1990                  140
7           2               1991                  100
8           2               1992                   90
9           2               1993                    5
10          2               1994                   52
11          2               1995                   80
12          3               2010                   60
13          3               2011                   40
14          3               2012                   70
15          3               2013                   60
16          3               2014                   50
17          3               2015                  110

My goal is to replace the last 1 / or 2 / or 3 values of the "percent [%]" column for each id (group) with NaN.

The result should look like this: (here: replace the last 2 values of each id)

           id               year           percent [%]
0           1               2000                  120
1           1               2001                   70
2           1               2002                   37
3           1               2003                   40
4           1               2004                  NaN
5           1               2005                  NaN
6           2               1990                  140
7           2               1991                  100
8           2               1992                   90
9           2               1993                    5
10          2               1994                  NaN
11          2               1995                  NaN
12          3               2010                   60
13          3               2011                   40
14          3               2012                   70
15          3               2013                   60
16          3               2014                  NaN
17          3               2015                  NaN

I know there should be a relatively easy solution for this, but i'm new to python and simply haven't been able to figure out an elegant way. Thanks for the help!

user_5847854 · Answer

try using groupby, tail and index to find the index of those rows that will be modified and use loc to change the values

nrows = 2
idx = df.groupby('id').tail(nrows).index
df.loc[idx, 'percent [%]'] = np.nan

#output
    id  year    percent [%]
0   1   2000    120.0
1   1   2001    70.0
2   1   2002    37.0
3   1   2003    40.0
4   1   2004    NaN
5   1   2005    NaN
6   2   1990    140.0
7   2   1991    100.0
8   2   1992    90.0
9   2   1993    5.0
10  2   1994    NaN
11  2   1995    NaN
12  3   2010    60.0
13  3   2011    40.0
14  3   2012    70.0
15  3   2013    60.0
16  3   2014    NaN
17  3   2015    NaN

Replace last value(s) of group with NaN

1 Answers