Remove duplicate values in a pandas column, but ignore one value

user_10500424 0
Viewed 1422

I'm sure there is an elegant solution for this, but I cannot find one. In a pandas dataframe, how do I remove all duplicate values in a column while ignoring one value?

repost_of_post_id                                              title
0        7139471603    Man with an RV needs a place to park for a week   
1        6688293563                                     Land for lease   
2              None                  2B/1.5B, Dishwasher, In Lancaster   
3              None  Looking For Convenience? Check Out Cordova Par...   
4              None  2/bd 2/ba, Three Sparkling Swimming Pools, Sit...   
5              None  1 bedroom w/Closet is bathrooms in Select Unit...   
6              None  Controlled Access/Gated, Availability 24 Hours...   
7              None         Beautiful 3 Bdrm 2 & 1/2 Bth Home For Rent   
8        7143099582                        Need Help Getting Approved?   
9              None            *MOVE IN READY APT* REQUEST TOUR TODAY!

What I want is to keep all None values in repost_of_post_id, but omit any duplicates of the numerical values, for example if there are duplicates of 7139471603 in the dataframe.


[UPDATE] I got the desired outcome using this script, but I would like to accomplish this in a one-liner, if possible. 

# remove duplicate repost id if present (i.e. don't remove rows where repost_of_post_id value is "None")
# ca_housing is the original dataframe that needs to be cleaned

ca_housing_repost_none = ca_housing.loc[ca_housing['repost_of_post_id'] == "None"]
ca_housing_repost_not_none = ca_housing.loc[ca_housing['repost_of_post_id'] != "None"]
ca_housing_repost_not_none_unique = ca_housing_repost_not_none.drop_duplicates(subset="repost_of_post_id")

ca_housing_unique = ca_housing_repost_none.append(ca_housing_repost_not_none_unique)
pandas dataframe python-3.x numpy python
2 Answers

You could try dropping the None values, then detecting duplicates, then filtering them out of the original list.

In [1]: import pandas as pd 
   ...: from string import ascii_lowercase 
   ...:  
   ...: ids = [1,2,3,None,None, None, 2,3, None, None,4,5] 
   ...: df = pd.DataFrame({'id': ids, 'title': list(ascii_lowercase[:len(ids)])}) 
   ...: print(df) 
   ...:  
   ...: print(df[~df.index.isin(df.id.dropna().duplicated().loc[lambda x: x].index)])                                 
     id title
0   1.0     a
1   2.0     b
2   3.0     c
3   NaN     d
4   NaN     e
5   NaN     f
6   2.0     g
7   3.0     h
8   NaN     i
9   NaN     j
10  4.0     k
11  5.0     l

     id title
0   1.0     a
1   2.0     b
2   3.0     c
3   NaN     d
4   NaN     e
5   NaN     f
8   NaN     i
9   NaN     j
10  4.0     k
11  5.0     l
user_17178280

You could use drop_duplicates and merge with the NaNs as follows:

df_cleaned = df.drop_duplicates('post_id', keep='first').merge(df[df.post_id.isnull()], how='outer')

This will keep the first occurence of ids duplicated and all NaNs rows.

user_75988960
Related