Dropping duplicate rows but keeping certain values Pandas

user_8378885 0
Viewed 1672

I have 2 similar dataframes that I concatenated that have a lot of repeated values because they are basically the same data set but for different years.

The problem is that one of the sets has some values missing whereas the other sometimes has these values.

For example:

Name        Unit       Year      Level
Nik         1          2000      12
Nik         1                    12
John        2          2001      11
John        2          2001      11
Stacy       1                    8
Stacy       1          1999      8
.
.

I want to drop duplicates on the subset = ['Name', 'Unit', 'Level'] since some repetitions don't have years.

However, I'm left with the data that has no Year and I'd like to keep the data with these values:

Name        Unit       Year      Level
Nik         1          2000      12
John        2          2001      11
Stacy       1          1999      8
.
.

How do I keep these values rather than the blanks?

pandas dataframe merge python-3.x python
3 Answers

Use sort_values with default parameter na_position='last', so should be omit, and then drop_duplicates:

print (df)
    Name  Unit    Year  Level
0    Nik     1     NaN     12
1    Nik     1  2000.0     12
2   John     2  2001.0     11
3   John     2  2001.0     11
4  Stacy     1     NaN      8
5  Stacy     1  1999.0      8

subset = ['Name', 'Unit', 'Level']
df = df.sort_values('Year').drop_duplicates(subset)

Or:

df = df.sort_values(subset + ['Year']).drop_duplicates(subset)

print (df)
    Name  Unit    Year  Level
5  Stacy     1  1999.0      8
1    Nik     1  2000.0     12
2   John     2  2001.0     11

Another solution with GroupBy.first for return first non missing value of Year per groups:

df = df.groupby(subset, as_index=False, sort=False)['Year'].first()
print (df)
    Name  Unit  Level    Year
0    Nik     1     12  2000.0
1   John     2     11  2001.0
2  Stacy     1      8  1999.0
user_29010020
user_64110610

I would suggest that you look at the creation step of your merged dataset.

When merging the data sets you can do so on multiple indices i.e. 

df = pd.merge(left, right, how='outer', on=['Name', 'Unit', 'Level'],  suffixes=['', '_r'])

With the outer join you collect all data sets and remove duplicates right away. The only thing left is to merge the Year column which you can do like so:

df['Year'] = df[['Year', 'Year_r']].apply(lambda x: x['Year'] if (x['Year'] is not np.nan and x['Year'] != '') else x['Year_r'], axis=1)

This fills the gaps and afterwards you are able to simply drop the 'Year_r' column.

The benefit here is that not only NaN values of missing years are covered but also missing Years which are represented as empty strings.

Following a small working example:

import pandas as pd
import numpy as np


left = pd.DataFrame({'Name': ['Adam', 'Beatrice', 'Crissy', 'Dumbo', 'Peter', 'Adam'],
                     'Unit': ['2', '4', '6', '2', '4', '12'],
                     'Year': ['', '2009', '1954', '2025', '2012', '2024'],
                     'Level': ['L1', 'L1', 'L0', 'L4', 'L3', 'L10']})

right = pd.DataFrame({'Name': ['Adam', 'Beatrice', 'Crissy', 'Dumbo'],
                     'Unit': ['2', '4', '6', '2'],
                     'Year': ['2010', '2009', '1954', '2025'],
                     'Level': ['L1', 'L1', 'L0', 'L4']})

df = pd.merge(left, right, how='outer', on=['Name', 'Unit', 'Level'],  suffixes=['', '_r'])
df['Year'] = df[['Year', 'Year_r']].apply(lambda x: x['Year'] if (x['Year'] is not np.nan and x['Year'] != '') else x['Year_r'], axis=1)
df
user_108163430
Related