Dataset(MWE)
JavaScript
x
14
14
1
location date people_vaccinated people_fully_vaccinated people_vaccinated_per_hundred2
AL 12-01-2021 70861 7270 1.453
AL 13-01-2021 74792 9245 1.534
AL 14-01-2021 80480 11366 1.645
AL 15-01-2021 86956 13488 1.776
AL 16-01-2021 93797 14202 1.917
AL 17-01-2021 100638 14917 2.058
AS 22-01-2021 5627 940 10.19
AS 23-01-2021 5881 948 10.5610
AS 24-01-2021 7096 948 12.7411
AS 25-01-2021 7096 949 12.9812
AS 26-01-2021 7230 950 13.2313
AS 27-01-2021 8133 950 14.614
I am trying to replace duplicates from columns {people_vaccinated,people_fully_vaccinated,people_vaccinated_per_hundred} with NaN while using groupby() on location. I tried some solution online, but couldn’t get them working for me, so instead used the below logic
JavaScript
1
11
11
1
def remove(df , a):2
df['duplicate'] = df[a].shift(1)3
df[a] = df.apply(lambda x: np.nan if x[a] == x['duplicate'] 4
else x[a], axis=1)5
df = df.drop('duplicate', axis=1)6
return df7
8
dfn = remove(dfn,'people_vaccinated')9
dfn = remove(dfn,'people_fully_vaccinated')10
dfn = remove(dfn,'people_vaccinated_per_hundred')11
The above logic fails when you have consecutive nulls(more than 2). I need to replace duplicates(while keeping the first instance) with NaNs. What is the best way to do this? You can observe from the above snippet that column people_fully_vaccinated has duplicate values
Sample output
JavaScript
1
14
14
1
location date people_vaccinated people_fully_vaccinated people_vaccinated_per_hundred2
AL 12-01-2021 70861 7270 1.453
AL 13-01-2021 74792 9245 1.534
AL 14-01-2021 80480 11366 1.645
AL 15-01-2021 86956 13488 1.776
AL 16-01-2021 93797 14202 1.917
AL 17-01-2021 100638 14917 2.058
AS 22-01-2021 5627 940 10.19
AS 23-01-2021 5881 948 10.5610
AS 24-01-2021 7096 NaN 12.7411
AS 25-01-2021 NaN 949 12.9812
AS 26-01-2021 7230 950 13.2313
AS 27-01-2021 8133 NaN 14.614
Advertisement
Answer
mask + duplicated
We can define a list of column names, then for each column inside a for loop mask the duplicate values per unique location
JavaScript
1
4
1
cols = ['people_vaccinated', 'people_fully_vaccinated', 'people_vaccinated_per_hundred']2
for c in cols:3
df[c] = df[c].mask(df.duplicated(['location', c]))4
JavaScript
1
14
14
1
location date people_vaccinated people_fully_vaccinated people_vaccinated_per_hundred2
0 AL 12-01-2021 70861.0 7270.0 1.453
1 AL 13-01-2021 74792.0 9245.0 1.534
2 AL 14-01-2021 80480.0 11366.0 1.645
3 AL 15-01-2021 86956.0 13488.0 1.776
4 AL 16-01-2021 93797.0 14202.0 1.917
5 AL 17-01-2021 100638.0 14917.0 2.058
6 AS 22-01-2021 5627.0 940.0 10.109
7 AS 23-01-2021 5881.0 948.0 10.5610
8 AS 24-01-2021 7096.0 NaN 12.7411
9 AS 25-01-2021 NaN 949.0 12.9812
10 AS 26-01-2021 7230.0 950.0 13.2313
11 AS 27-01-2021 8133.0 NaN 14.6014