Dummy variable for Last Item in Group

user_8507034 0
Viewed 71

Given a dataframe such as the following, how can I add a column indicating that the record is the last item for the id.

pd.DataFrame({'ID' :["A"] *5 +[ "B"] *2 + ["C"] * 4,
                    'Year': [2017] * 4 +[2018] + [2017, 2019] + [2019] *2 +[2020] * 2,
                    'Quarter': [1,2,3,4,1,3,2,3,4,1,2]})


    ID  Year    Quarter
0   A   2017    1
1   A   2017    2
2   A   2017    3
3   A   2017    4
4   A   2018    1
5   B   2017    3
6   B   2019    2
7   C   2019    3
8   C   2019    4
9   C   2020    1
10  C   2020    2

Desired:

    ID  Year    Quarter Last
0   A   2017    1       0
1   A   2017    2       0
2   A   2017    3       0
3   A   2017    4       0
4   A   2018    1       1
5   B   2017    3       0
6   B   2019    2       1
7   C   2019    3       0
8   C   2019    4       0
9   C   2020    1       0
10  C   2020    2       1
pandas-groupby pandas python
3 Answers

You can do series.duplicated with keep='last and subtract it from 1

df['Last'] = 1-df['ID'].duplicated(keep='last')
#df['Last'] = 1-df['ID'].duplicated(keep='last').astype(int)

   ID  Year  Quarter  Last
0   A  2017        1     0
1   A  2017        2     0
2   A  2017        3     0
3   A  2017        4     0
4   A  2018        1     1
5   B  2017        3     0
6   B  2019        2     1
7   C  2019        3     0
8   C  2019        4     0
9   C  2020        1     0
10  C  2020        2     1
user_98406370

You can check for df['ID'] not equal to its next entry by using .ne() and .shift(-1) and then change this Boolean series to 0, 1 series by casting from Boolean type to integer type, as follows:

df['last'] = df['ID'].ne(df['ID'].shift(-1)).astype(int)


print(df)

   ID  Year  Quarter  last
0   A  2017        1     0
1   A  2017        2     0
2   A  2017        3     0
3   A  2017        4     0
4   A  2018        1     1
5   B  2017        3     0
6   B  2019        2     1
7   C  2019        3     0
8   C  2019        4     0
9   C  2020        1     0
10  C  2020        2     1
user_150706970

One more alternative -

df['Last'] = 0
def add_id(s):
    s.iloc[-1] =1
    return s
df['Last'] = df.groupby('ID')['Last'].transform(add_id)
user_154380330
Related