How to remove duplicate rows from a DataFrame, where the duplicates have NaNs?

user_10834910 0
Viewed 150

I have a dataFrame with more than 200 features, and I put a part of the dataset to show the problem:

   index  ID   X1   X2       Date1      Y1     
      0   2   324  634  2016-01-01     NaN    
      1   2   324  634  2016-01-01   1224.0    
      3   4   543  843  2017-02-01     654    
      4   4   543  843  2017-02-01     NaN    
      5   5   523  843  2015-09-01     NaN    
      6   5   523  843  2015-09-01    1121.0
      7   6   500  897  2015-11-01     NaN

As you can see the rows are duplicated (in ID, X1, X2 and Date1) and I want to remove one of the rows which are similar in ID, X1, X2, Date1 and Y1 which contains NaN. So, my desired DataFrame should be:

   index  ID   X1   X2       Date1      Y1     

      1   2   324  634  2016-01-01   1224.0    
      3   4   543  843  2017-02-01     654    
      6   5   523  843  2015-09-01    1121.0
      7   6   500  897  2015-11-01     NaN

Does any one know, how I can handle it?

pandas dataframe duplicates python
2 Answers

Use sort_values on "Y1" to move NaNs to the bottom of the DataFrame, and then use drop_duplicates:

df2 = (df.sort_values('Y1', na_position='last')
         .drop_duplicates(['ID', 'X1', 'X2', 'Date1'], keep='first')
         .sort_index())

df2
       ID   X1   X2       Date1      Y1
index                                  
1       2  324  634  2016-01-01  1224.0
3       4  543  843  2017-02-01   654.0
6       5  523  843  2015-09-01  1121.0
7       6  500  897  2015-11-01     NaN
user_49090870
user_43996340
Related