Subset dataframe based on condition if column value has string

user_5203203 0
Viewed 503

I need to keep rows in the dataframe if, in a cluster of records, the column value starts with a specified string meeting two conditions. This code works in one part of my program, but here it returns an empty dataframe. I'm not sure if it is because of the groupby or some other issue.

df = pd.DataFrame({'ClusterId': [0, 1, 0, 0], 'LastName': ['Smith', 'Jones', 'Smith', 'Roe'], 'FirstName': ['Joe', 'Mike', 'Mike', 'Edna'], 'City': ['Denver', 'Boston', 'Denver', 'Boulder']})

final_set = []
diff_records = df.groupby('ClusterId', as_index=False)        
for group, frame in diff_records:
    original_record = pd.DataFrame(frame.head(1))
    
    blocking_last_name = (original_record['LastName'].astype(str).str[:3].to_string()) 
    blocking_first_name = (original_record['FirstName'].astype(str).str[:3].to_string()) 
    blocking_city = (original_record['City'].astype(str).str[:3].to_string())   

    frame = frame[(frame['LastName'].str.startswith(blocking_lastname) & frame['City'].str.startswith(blocking_city)) |
    (frame['FirstName'].str.startswith(blocking_first_name) & frame['City'].str.startswith(blocking_city)) ]

The output would look like this, dropping Edna Roe from Boulder in Cluster 0 because it does not meet the criteria of matching to Joe and Mike Smith from Denver.

output = pd.DataFrame({'ClusterId': [0, 1, 0], 'LastName': ['Smith', 'Jones', 'Smith'], 'FirstName': ['Joe', 'Mike', 'Mike'], 'City': ['Denver', 'Boston', 'Denver']})

This is the solution that I used based on logic provided below.

final_set = []
diff_records = predict_list.groupby('ClusterId', as_index=False)        
    for group, frame in diff_records:
        frame = frame.loc[(frame['FirstName'].str[:3] == frame.head(1).FirstName.str[:3].iloc[0]) & (frame['City'].str[:3] == frame.head(1).City.str[:3].iloc[0]) | 
                              (frame['LastName'].str[:3] == frame.head(1).LastName.str[:3].iloc[0]) &  (frame['City'].str[:3] == frame.head(1).City.str[:4].iloc[0]) ]
                              
        final_set.append(frame)
final_set = pd.concat(final_set)
pandas-groupby pandas dataframe python
1 Answers

Without your expected output I cannot fully validate.

  • same approach but using apply() to do looping
  • cleanup of multi-index with droplevel()
df = pd.DataFrame({'ClusterId': [0, 1, 0], 'LastName': ['Smith', 'Jones', 'Smith'], 'FirstName': ['Joe', 'Mike', 'Mike'], 'City': ['Denver', 'Boston', 'Denver']})

def f(d):
    return d.loc[(d.LastName.str[:3] == d.head(1).LastName.str[:3].iloc[0]) 
                & (d.FirstName.str[:3] == d.head(1).FirstName.str[:3].iloc[0]) 
                & (d.City.str[:3] == d.head(1).City.str[:3].iloc[0]) 
                ]

df.groupby("ClusterId", as_index=False).apply(f).droplevel(1,0)

output

ClusterId LastName FirstName City
0 0 Smith Joe Denver
1 1 Jones Mike Boston
user_94414040
Related