Pandas DataFrame：当索引与列值的斜杠数量不匹配时将值替换为np.NaN

Got it, let's break down how to solve this problem efficiently. The goal is to replace any URL in your DataFrame columns with np.NaN if its number of "/" doesn't match the count in the corresponding index URL. Here's a step-by-step approach:

Step 1: Calculate Slash Counts for the Index #

First, we need to get the number of "/" in each index URL. We can do this using Pandas' string method str.count():

import pandas as pd
import numpy as np

# Example DataFrame (replace with your actual data)
data = {
    'url_col1': ['https://test.com/x/y', 'https://example.com/1/2/3', 'https://abc.com'],
    'url_col2': ['https://example.com/a/b/c', 'https://test.com/p/q', 'https://xyz.com/m/n/o']
}
index_urls = ['https://example.com/a/b/c', 'https://test.com/x/y', 'https://abc.com/d']
df = pd.DataFrame(data, index=index_urls)

# Calculate slash count for each index entry
index_slash_count = df.index.str.count('/')

Step 2: Replace Mismatched URLs with NaN #

We have two straightforward ways to apply the comparison and replacement:

Method 1: Using apply() (Readable for Small to Medium DataFrames) #

This approach iterates over each column, compares the slash count of every cell to the index's count, and replaces mismatches with np.NaN:

df_cleaned = df.apply(lambda col: np.where(col.str.count('/') == index_slash_count, col, np.NaN))

Method 2: Vectorized Approach (Faster for Large DataFrames) #

For bigger datasets, a vectorized method is more efficient. We first compute slash counts for all cells, then broadcast the index counts to compare against the entire DataFrame:

# Calculate slash count for every cell in the DataFrame
cell_slash_counts = df.applymap(lambda url: url.count('/'))

# Keep only cells where counts match the index, else set to NaN
df_cleaned = df.where(cell_slash_counts == index_slash_count.values[:, None], np.NaN)

Verification #

If you print df_cleaned, you'll see that only URLs with the same number of "/" as their row's index are kept. For example:

• The first row index has 3 "/" — so url_col1's value (which has 2 "/") gets replaced with NaN, while url_col2's value (3 "/") stays.
• The second row index has 2 "/" — url_col1's value (3 "/") becomes NaN, url_col2's value (2 "/") remains.

内容的提问来源于stack exchange，提问作者obkmrk