How to Flag Overlapping Strings in Excel with Pandas: 3 Simple Methods

Introduction

Hello everyone, I am a Python enthusiast sharing a practical pandas example.

Problem Statement

A fan asked how to mark rows in an Excel file when two string columns contain overlapping characters. The original screenshot of the question is shown below.

Solution Overview

The initial code provided creates a new column "标记列" (mark column) by applying a lambda that checks the intersection of the character sets of the two string columns. The result column initially contains Boolean values True or False, as illustrated.

import pandas as

df = pd.read_excel('test.xlsx')
df["标记列"] = df[["字符串1", "字符串2"]].apply(
    lambda x: len(set(x['字符串1']) & set(x['字符串2'])) > 0,
    axis=1)
print(df)

Method 1: Map Booleans to 0/1

import pandas as

df = pd.read_excel('test.xlsx')
df["标记列"] = df[["字符串1", "字符串2"]].apply(
    lambda x: len(set(x['字符串1']) & set(x['字符串2'])) > 0,
    axis=1)
bool_map = {True: 1, False: 0}
df['new_标记列'] = df['标记列'].map(bool_map)
print(df)

Method 2: Directly Return 0/1 in Lambda

import pandas as

df = pd.read_excel('test.xlsx')
df["标记列"] = df[["字符串1", "字符串2"]].apply(
    lambda x: 1 if len(set(x['字符串1']) & set(x['字符串2'])) > 0 else 0,
    axis=1)

Method 3: Apply on Whole Row

import pandas as

df = pd.read_excel('test.xlsx')
df["标记列"] = df.apply(
    lambda x: 1 if len(set(x['字符串1']) & set(x['字符串2'])) > 0 else 0,
    axis=1)
print(df)

Conclusion

The article demonstrates three concise pandas techniques to generate a numeric flag (0 or 1) indicating whether two string columns in an Excel sheet share any characters, enabling quick data analysis and downstream processing.