如何基于空值拼接pandas DataFrame列并创建新列var？

Hey there! Let's tackle this pandas DataFrame column merging problem. First off, I noticed there might be a typo in your conditions 2 and 3—for example, condition 2 says opt1, opt3, and opt2 are all empty but you want to concatenate opt1 and opt3, which doesn't make logical sense. Looking at all four conditions together, I think your actual goal is to join all non-empty values (excluding None, empty strings, and NaNs) from opt1, opt2, opt3 using a | separator—this perfectly covers all the scenarios you listed:

• Only opt1 is non-empty → result is opt1
• opt1 and opt3 are non-empty, opt2 is empty → result is opt1|opt3
• opt1 and opt2 are non-empty, opt3 is empty → result is opt1|opt2
• All other cases (like all three non-empty, only opt2 non-empty, etc.) → join all non-empty fields

If that's the case, we can implement this much more cleanly than writing a bunch of conditional checks. Here are the solutions for Python 3.6:

Solution 1: Using apply (intuitive, great for small datasets) #

This approach is straightforward and easy to read, though it's less efficient for very large DataFrames:

import pandas as pd
import numpy as np

# Sample DataFrame to test with
df = pd.DataFrame({
    'opt1': ['a', '', None, 'd', np.nan],
    'opt2': [None, 'b', '', 'e', 'f'],
    'opt3': ['c', '', 'g', None, np.nan]
})

# Helper function to check if a value is empty
def is_empty(val):
    return pd.isna(val) or val == ''

# Function to process each row: collect non-empty values and join them
def join_non_empty(row):
    non_empty_vals = [col for col in [row['opt1'], row['opt2'], row['opt3']] if not is_empty(col)]
    return '|'.join(non_empty_vals)

# Create the new column
df['var'] = df.apply(join_non_empty, axis=1)

After running this, your df['var'] will look like this:

0    a|c
1      b
2      g
3    d|e
4      f

Solution 2: Vectorized Operations (more efficient for large datasets) #

apply can be slow with big data, so this vectorized approach is better for scalability:

import pandas as pd
import numpy as np

# Sample DataFrame
df = pd.DataFrame({
    'opt1': ['a', '', None, 'd', np.nan],
    'opt2': [None, 'b', '', 'e', 'f'],
    'opt3': ['c', '', 'g', None, np.nan]
})

# Replace empty strings and None with NaN to standardize empty values
df_clean = df.replace(['', None], np.nan)

# Join non-NaN values for each row
df['var'] = df_clean.apply(lambda x: '|'.join(x.dropna()), axis=1)

This does the same job as Solution 1 but runs faster on large datasets by leveraging pandas' vectorized operations.

If you strictly need to follow your original (conflicting) conditions #

If you really need to stick to the exact conditions you wrote (even though some are logically conflicting), we can use nested np.where statements. Note that I've adjusted the conflicting conditions to what I think you meant (since the original wording leads to nonsensical results):

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'opt1': ['a', '', None, 'd', np.nan, 'x'],
    'opt2': [None, 'b', '', 'e', 'f', None],
    'opt3': ['c', '', 'g', None, np.nan, 'y']
})

# Vectorized function to check for empty values
def is_empty_vect(series):
    return series.isna() | (series == '')

# Create masks for each column being empty
opt1_empty = is_empty_vect(df['opt1'])
opt2_empty = is_empty_vect(df['opt2'])
opt3_empty = is_empty_vect(df['opt3'])

# Apply conditions in priority order
df['var'] = np.where(
    # Condition 1: opt2 and opt3 empty, opt1 non-empty
    opt2_empty & opt3_empty & ~opt1_empty,
    df['opt1'],
    np.where(
        # Adjusted Condition 2: opt1 and opt3 empty, opt2 non-empty
        opt1_empty & opt3_empty & ~opt2_empty,
        df['opt2'],
        np.where(
            # Adjusted Condition 3: opt1 and opt2 empty, opt3 non-empty
            opt1_empty & opt2_empty & ~opt3_empty,
            df['opt3'],
            np.where(
                # Original intended Condition 2: opt2 empty, opt1 and opt3 non-empty
                opt2_empty & ~opt1_empty & ~opt3_empty,
                df['opt1'] + '|' + df['opt3'],
                np.where(
                    # Original intended Condition 3: opt3 empty, opt1 and opt2 non-empty
                    opt3_empty & ~opt1_empty & ~opt2_empty,
                    df['opt1'] + '|' + df['opt2'],
                    # Condition 4: all other cases (fill empty values with empty string first)
                    df['opt1'].fillna('') + '|' + df['opt2'].fillna('') + '|' + df['opt3'].fillna('')
                )
            )
        )
    )
)

That said, this nested approach is messy and error-prone, so the first two solutions are strongly recommended.

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