Filtering pandas data frame based on different entries in a column (list of comma-separated strings)

user_19054077 0
Viewed 72

I have a Pandas DataFrame where I want to filter for all "IDs" that have different entries in "TCK" (list of comma-separated strings), i.e. are not the same for all entries.

My DataFrame looks likes this:

df1 = pd.DataFrame({"ID": [1, 2, 3, 4],
        "TCK": [["AA, AA, AC"], ["LL, LL"], ["DD , DB, DF, DE"], ["LO , LO, LO, LO, LO, LO"]]})

The desired output should look like this:

df2 = pd.DataFrame({"ID": [1, 3],
        "TCK": [["AA, AA, AC"],["DD , DB, DF, DE"]]})

I know that one way would be to first split the strings into new columns (based on commas) and then use a loop to identify the different tickers. However, since there would also be np.nans, this would be a rather complicated solution.

Does anyone know a speedy and elegant solution to this problem?

pandas filter python string
3 Answers

You may first split your strings and apply set to check if the set has more than 1 element, i.e., has different elements:

tck = df1.TCK.apply(lambda x: [item.strip() for item in x[0].split(',')])
df1[tck.apply(lambda x: len(set(x)) > 1)]
user_192557490

Using str.extractall:

m = df1['TCK'].str[0].str.extractall('(\w+)')[0].groupby(level=0).nunique().gt(1)

df1[m]

output:

   ID                TCK
0   1       [AA, AA, AC]
2   3  [DD , DB, DF, DE]
user_163434640

using regex:

df = pd.DataFrame({"ID": [1, 2, 3, 4],
        "TCK": [["AA, AA, AC"], ["LL, LL"], ["DD , DB, DF, DE"], ["LO , LO, LO, LO, LO, LO"]]})

df['TCK'] = [','.join(l) for l in df['TCK']]
df['TCK']= df['TCK'].replace(r'(\w+)(?:\W+\1\b)+$', None, regex=True)
df.dropna(inplace=True)
print(df.head())

output:

   ID              TCK
0   1       AA, AA, AC
2   3  DD , DB, DF, DE
user_87671860
Related