如何在R中基于数据框列值与另一数据框列名条件创建新列

Hey there! Let's walk through how to solve this problem—matching the averageDate values in df1 to the date-named columns in df2, then pulling the corresponding economic indicator values into a new column in df1. I'll cover two practical approaches using pandas, depending on your df2 structure.

First, Let's Set Up Example Data #

To make this concrete, here's sample data that mirrors your scenario:

import pandas as pd

# Sample df1 with averageDate column (%Y-%m format)
df1 = pd.DataFrame({
    'record_id': [1, 2, 3, 4],
    'averageDate': ['2023-01', '2023-02', '2023-03', '2023-01']
})

# Sample df2 with date-named columns holding economic values
# (This example uses a single row of data, common for aggregated time-series metrics)
df2 = pd.DataFrame({
    '2023-01': [125.6],
    '2023-02': [130.1],
    '2023-03': [132.8]
})

Approach 1: Dictionary Mapping (Quick for Single-Row df2) #

If df2 only has one row of data (e.g., a single economic metric tracked over dates), converting that row to a dictionary and using map() is super efficient:

# Convert df2's first row to a date-to-value dictionary
date_value_map = df2.iloc[0].to_dict()

# Add the new column to df1 by mapping averageDate to the dictionary values
df1['economic_metric'] = df1['averageDate'].map(date_value_map)

This will directly populate the new column with the matching values from df2. For our sample, df1's first row (averageDate='2023-01') gets 125.6, and so on.

Approach 2: Reshape df2 to Long Format (Flexible for Multi-Row df2) #

If df2 has multiple rows (e.g., different economic metrics per row), reshaping it to a long format then merging with df1 is the way to go. This works for single-row df2 too:

# Reshape df2 from wide to long format
df2_long = df2.reset_index().melt(
    id_vars='index',  # Replace 'index' with your actual identifier column if you have one
    var_name='averageDate',
    value_name='economic_metric'
)

# Merge df1 with the reshaped df2 to get matching values
df3 = df1.merge(df2_long, on='averageDate', how='left')

The how='left' ensures we keep all rows from df1 even if there's no matching date in df2 (those will show as NaN).

Critical Pre-Check: Ensure Date Format Consistency #

Before matching, make sure your date formats are identical in both dataframes to avoid missing matches:

# Standardize df1's averageDate format
df1['averageDate'] = pd.to_datetime(df1['averageDate'], format='%Y-%m').dt.strftime('%Y-%m')

# Standardize df2's column names to the same format
df2.columns = pd.to_datetime(df2.columns, format='%Y-%m').dt.strftime('%Y-%m')

This fixes any subtle formatting issues (like leading zeros or different string representations) that could break the match.

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