CODEWITHSUNDEEP
pythonpandasdataframedelete-row
everestial
everestial

Reputation: 7255

How to remove a specific repeated line in pandas dataframe?

In this pandas dataframe:

df =

pos    index  data
21      36    a,b,c
21      36    a,b,c
23      36    c,d,e
25      36    f,g,h
27      36    g,h,k
29      39    a,b,c
29      39    a,b,c
31      39    .
35      39    c,k
36      41    g,h
38      41    k,l
39      41    j,k
39      41    j,k

I want to remove the repeated line that are only in the same index group and when they are in the head regions of the subframe.

So, I did:

 df_grouped = df.groupby(['index'], as_index=True)

now,

 for i, sub_frame in df_grouped:
    subframe.apply(lamda g: ... remove one duplicate line in the head region if pos value is a repeat)

I want to apply this method because some pos value will be repeated in the tail region which should not be removed.

Any suggestions.

Expected output:

 pos    index  data
removed
21      36    a,b,c
23      36    c,d,e
25      36    f,g,h
27      36    g,h,k
removed
29      39    a,b,c
31      39    .
35      39    c,k
36      41    g,h
38      41    k,l
39      41    j,k
39      41    j,k

Upvotes: 1

Views: 956

Answers (1)

Craig
Craig

Reputation: 4855

If it doesn't have to be done in a single apply statement, then this code will only remove duplicates in the head region:

data= {'pos':[21, 21, 23, 25, 27, 29, 29, 31, 35, 36, 38, 39, 39],
       'idx':[36, 36, 36, 36, 36, 39, 39, 39, 39, 41, 41, 41, 41], 
       'data':['a,b,c', 'a,b,c', 'c,d,e', 'f,g,h', 'g,h,k', 'a,b,c', 'a,b,c', '.', 'c,k', 'g,h', 'h,l', 'j,k', 'j,k']
}

df = pd.DataFrame(data)

accum = []
for i, sub_frame in df.groupby('idx'):
    accum.append(pd.concat([sub_frame.iloc[:2].drop_duplicates(), sub_frame.iloc[2:]]))

df2 = pd.concat(accum)

print(df2)

EDIT2: The first version of the chained command that I posted was wrong and and only worked for the sample data. This version provides a more general solution to remove duplicate rows per the OP's request:

df.drop(df.groupby('idx')         # group by the index column
          .head(2)                # select the first two rows
          .duplicated()           # create a Series with True for duplicate rows
          .to_frame(name='duped') # make the Series a dataframe
          .query('duped')         # select only the duplicate rows
          .index)                 # provide index of duplicated rows to drop

Upvotes: 1

Related Questions