How to remove duplicates from the framework?

Question

My current df looks like this:

    IDnumber     Subid      Subsubid     Date        Originaldataindicator
      a            1          x           2006           NaN
      a            1          x           2007           NaN
      a            1          x           2008           NaN
      a            1          x           2008           1

The original dataindicator is the result of some of these observations being created to get all three years for each IDnumber, while some of them existed in the original dataset. I want to achieve that to delete duplicates and previously to save initial data. Note that the original dataindicator will not always be the last observation. To solve this problem, I first sort by Idnumber Date Originaldataindicator

However, when I use:

  df=df.drop_duplicates(subset=['IDnumber', 'Subid', 'Subsubid', 'Date'])

Nothing happens and I'm still seeing a duplicate.

  df=df.drop_duplicates(subset=['IDnumber', 'Subid', 'Subsubid', 'Date'], inplace=True)

gives me an empty data frame.

Am I misinterpreting what drop_duplicates does?

To avoid confusion, this is what I want:

    IDnumber     Subid      Subsubid     Date        Originaldataindicator
      a            1          x           2006           NaN
      a            1          x           2007           NaN
      a            1          x           2008           1

Data includes thousands of these identifiers.

Answer 1

, groupby sort_values, keep=first drop_duplicates:

print df
  IDnumber  Subid Subsubid  Date  Originaldataindicator
0        a      1        x  2006                    NaN
1        a      1        x  2007                    NaN
2        a      1        x  2008                    NaN
3        a      1        x  2008                      1
4        a      1        x  2008                    NaN

df = df.groupby(['IDnumber', 'Subid', 'Subsubid', 'Date'])
              .apply(lambda x: x.sort_values('Originaldataindicator')).reset_index(drop=True)
print df
  IDnumber  Subid Subsubid  Date  Originaldataindicator
0        a      1        x  2006                    NaN
1        a      1        x  2007                    NaN
2        a      1        x  2008                      1
3        a      1        x  2008                    NaN
4        a      1        x  2008                    NaN

df1=df.drop_duplicates(subset=['IDnumber', 'Subid', 'Subsubid', 'Date'], keep='first')
print df1
  IDnumber  Subid Subsubid  Date  Originaldataindicator
0        a      1        x  2006                    NaN
1        a      1        x  2007                    NaN
2        a      1        x  2008                      1

inplace:

df.drop_duplicates(subset=['IDnumber','Subid','Subsubid','Date'], keep='first', inplace=True)
print df
  IDnumber  Subid Subsubid  Date  Originaldataindicator
0        a      1        x  2006                    NaN
1        a      1        x  2007                    NaN
2        a      1        x  2008                      1

Originaldataindicator , duplicated (, IDnumber, Subid, Subsubid, Date) isnull:

print df
  IDnumber  Subid Subsubid  Date  Originaldataindicator
0        a      1        x  2006                    NaN
1        a      1        x  2007                    NaN
2        a      1        x  2008                    NaN
3        a      1        x  2008                      1
4        a      1        x  2008                      1

print df[~((df.duplicated('Date',keep=False))&~(pd.notnull(df['Originaldataindicator'])))]
  IDnumber  Subid Subsubid  Date  Originaldataindicator
0        a      1        x  2006                    NaN
1        a      1        x  2007                    NaN
3        a      1        x  2008                      1
4        a      1        x  2008                      1

:

print df.duplicated('Date', keep=False)
0    False
1    False
2     True
3     True
4     True
dtype: bool

print (pd.isnull(df['Originaldataindicator']))
0     True
1     True
2     True
3    False
4    False
Name: Originaldataindicator, dtype: bool

print ~((df.duplicated('Date', keep=False)) & (pd.isnull(df['Originaldataindicator'])))
0     True
1     True
2    False
3     True
4     True
dtype: bool

Answer 2

:

df = pd.DataFrame({'a': [1, 2, 3, 3, 3], 'b': [1, 2, None, 1, None]})

>>> df.sort_values(by=['a', 'b']).groupby(df.a).first()[['b']].reset_index()
    a   b
0   1   1
1   2   2
2   3   1

a, b ( , None ), .

, .