无公共键时如何将单列DataFrame或单值Series合并至目标DataFrame

Let's break down your two questions step by step, since they're related to adding new columns to a DataFrame without a direct common key:

When you don't have a shared key (like a matching column or index) to align the data, the approach depends on whether you want to align by position or broadcast a single value across all rows:

• Case 1: Both DataFrames have the same length
  If the single-column DataFrame has exactly as many rows as the target DataFrame, you can directly assign it as a new column. Pandas will align by index by default, but if you want to ignore indexes and match by position, reset both indexes first:

  # Example data
  target_df = pd.DataFrame({"User_Id": [1,2,3], "VIEWED_MOVIE": [4,20,0]})
  single_col_df = pd.DataFrame({"NEW_COL": [10,20,30]})
  
  # Assign directly (aligns by index)
  target_df["NEW_COL"] = single_col_df["NEW_COL"]
  
  # Or match by position (ignore indexes)
  target_df = pd.concat([target_df.reset_index(drop=True), single_col_df.reset_index(drop=True)], axis=1)
  

• Case 2: Single-column DataFrame has one row (broadcast to all rows)
  If you want to add a single value to every row of the target DataFrame, just extract the scalar value and assign it—Pandas will automatically broadcast it:

  single_val_df = pd.DataFrame({"NEW_COL": [99]})
  target_df["NEW_COL"] = single_val_df.iloc[0, 0]  # Extracts the single value 99
  

For your given data, where the Series only has a value for User_Id=3, you need to align the value to the matching User_Id in your target DataFrame, and fill missing values (for other users) appropriately. Here are two efficient vectorized approaches:

Approach 1: Use merge with left join #

Convert the Series to a DataFrame with the User_Id column, then merge it to your original DataFrame. This ensures the value is mapped to the correct user:

import pandas as pd

# Your original DataFrame
original_df = pd.DataFrame({
    "User_Id": [1,2,3],
    "MARKED_CONTENT_AS_FAVOURITE": ["6916484f-b7bd-431a-818d-d1a63ff7c717", "9fbb7702-5209-46c8-b7c8-2c3d03550b56", "cb1fc554-8566-4c9f-a3ca-f64be302d65e"],
    "RATE_CONTENT": [0,2,0],
    "SEARCH": [0,2,0],
    "VIEWED_CELEBRITY": [1,1,1],
    "VIEWED_MOVIE": [4,20,0],
    "VIEWED_TVSHOW": [0,3,0]
})

# Your single-value Series
check_in_series = pd.Series([0], index=[3], name="USER_CHECKED_IN_CONTENT")

# Convert Series to DataFrame with User_Id as a column
check_in_df = check_in_series.reset_index().rename(columns={"index": "User_Id"})

# Merge with left join to keep all original rows
result_df = original_df.merge(check_in_df, on="User_Id", how="left")

# Fill missing values with 0 (or NaN if you prefer)
result_df["USER_CHECKED_IN_CONTENT"] = result_df["USER_CHECKED_IN_CONTENT"].fillna(0)

Approach 2: Use map (pure vectorized) #

Convert the Series to a dictionary, then use map to match values to the User_Id column. This is a fast, vectorized operation:

# Convert Series to a dictionary: {3: 0}
check_in_map = check_in_series.to_dict()

# Map the values to User_Id, fill missing with 0
original_df["USER_CHECKED_IN_CONTENT"] = original_df["User_Id"].map(check_in_map).fillna(0)

Both methods avoid loops and leverage Pandas' optimized vectorized operations for speed.

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