From this sample of my dataset, I need to drop duplicate rows that have that have same values in all columns except for "parent_station". The duplicate row that will be removed has to be the one with NaN in "parent_station" column and keep the row that has "parent_station" value different than NaN. In this example, the row that needs to be removed is the 4th row, with index of 7789. How can I do this ? I have not yet been able to figure out how.
stop_id stop_name parent_station trip_id arrival_time departure_time stop_sequence route_id trip_headsign
7022 87413385 Gare de Yvetot StopArea:OCE87413385 OCESN003100F140147152 05:49:00 05:50:00 2.0 OCE1506035 3100.0
3518 87411017 Gare de Rouen-Rive-Droite StopArea:OCE87411017 OCESN003100F140147152 06:12:00 06:15:00 3.0 OCE1506035 3100.0
8040 87413013 Gare de Le Havre StopArea:OCE87413013 OCESN003100F140147152 05:20:00 05:20:00 0.0 OCE1506035 3100.0
7789 87413013 Gare de Le Havre NaN OCESN003100F140147152 05:20:00 05:20:00 0.0 OCE1506035 3100.0
7197 87413344 Gare de Bréauté-Beuzeville NaN OCESN003100F140147152 05:35:00 05:36:00 1.0 OCE1506035 3100.0
You can use a boolean mask:
out = df[~df.drop('parent_station', axis=1).duplicated(keep=False) | pd.notna(df['parent_station'])]
Output:
stop_id stop_name parent_station \
index
7022 87413385 Gare de Yvetot StopArea:OCE87413385
3518 87411017 Gare de Rouen-Rive-Droite StopArea:OCE87411017
8040 87413013 Gare de Le Havre StopArea:OCE87413013
7197 87413344 Gare de Bréauté-Beuzeville NaN
trip_id arrival_time departure_time stop_sequence \
index
7022 OCESN003100F140147152 05:49:00 05:50:00 2.0
3518 OCESN003100F140147152 06:12:00 06:15:00 3.0
8040 OCESN003100F140147152 05:20:00 05:20:00 0.0
7197 OCESN003100F140147152 05:35:00 05:36:00 1.0
route_id trip_headsign
index
7022 OCE1506035 3100.0
3518 OCE1506035 3100.0
8040 OCE1506035 3100.0
7197 OCE1506035 3100.0
Use drop_duplicates:
cols = df.columns[df.columns != 'parent_station']
out = df[~(df.duplicated(cols, keep=False) & df['parent_station'].isna())]
print(out)
# Output
stop_id stop_name parent_station trip_id arrival_time departure_time stop_sequence route_id trip_headsign
7022 87413385 Gare de Yvetot StopArea:OCE87413385 OCESN003100F140147152 05:49:00 05:50:00 2.0 OCE1506035 3100.0
3518 87411017 Gare de Rouen-Rive-Droite StopArea:OCE87411017 OCESN003100F140147152 06:12:00 06:15:00 3.0 OCE1506035 3100.0
8040 87413013 Gare de Le Havre StopArea:OCE87413013 OCESN003100F140147152 05:20:00 05:20:00 0.0 OCE1506035 3100.0
7197 87413344 Gare de Bréauté-Beuzeville NaN OCESN003100F140147152 05:35:00 05:36:00 1.0 OCE1506035 3100.0
Even though it's slightly longer than the explanations above.
ss = df.columns.drop('parent_station')
keep_rows = df[(df.duplicated(subset=ss, keep=False)) & (~df.parent_station.isna())]
non_duplicates = df[~(df.duplicated(subset=ss, keep=False))]
df = pd.concat([non_duplicates, keep_rows])
Here I make a clear distinction between the rows which have no double at all (non_duplicates) and the duplicate rows that you want to keep (keep_rows).