如何在Python中实现一个DataFrame单列与另一个DataFrame两列的数据匹配并提取对应值

Hey there! Let's work through this problem together. You want to match the Name column from data1 against both Name1 and Name2 in data2, then pull the associated Points1 or Points2 values for each entry in data1. Here's a straightforward way to do this with pandas:

Step-by-Step Implementation #

First, let's set up our DataFrames and reshape data2 to make the matching easier:

import pandas as pd

# Define your original DataFrames
data1 = pd.DataFrame(
    {'Name': ['Cody', 'Billy', 'Jeniffer', 'Franc', 'Mark', 'Tamis', 'Danye', 'Leesa', 'Hector', 'Coy'],
     'Area': ['California', 'Connecticut', 'Indiana', 'Georgia', 'Illinois', 'Connecticut', 'Illinois', 'Indiana', 'Illinois', 'California']}
)

data2 = pd.DataFrame(
    {'Name1': ['Billy' , 'Cody', 'Coy', 'Danye', 'Franc', 'Alish', 'Rob', 'Bob', 'Cidi', 'Codi', 'Yiki', 'Hana'],
     'Points1': ['21', '27.5', '25', '21', '21', '19', '40', '30', '20', '50', '40', '54'],
     'Name2': ['Harry', 'Thomas' , 'Eric', 'Lucy', 'Jeni', 'Hector', 'Jeniffer', 'Leesa', 'Mark', 'Tamis', 'Mona', 'Jerry'],
     'Points2': ['20', '15.6', '24', '21', '19', '40', '30', '20', '50', '90', '80', '85']}
)

# Reshape data2 to create a single Name-Points mapping
# Split data2 into two separate DataFrames for Name1/Points1 and Name2/Points2
name1_points = data2[['Name1', 'Points1']].rename(columns={'Name1': 'Name', 'Points1': 'Points'})
name2_points = data2[['Name2', 'Points2']].rename(columns={'Name2': 'Name', 'Points2': 'Points'})

# Combine the two into one DataFrame
all_name_points = pd.concat([name1_points, name2_points], ignore_index=True)

# Merge with data1 to get the final result
final_result = pd.merge(data1, all_name_points, on='Name', how='left')

# Display the desired columns
print(final_result[['Name', 'Area', 'Points']])

What This Does: #

1. Reshape data2: We split data2 into two smaller DataFrames, each pairing a name column with its corresponding points. Then we concatenate them to create a single list of all name-points pairs.
2. Merge with data1: Using pd.merge, we connect data1 to our combined name-points DataFrame using the Name column as the key. The how='left' ensures we keep all entries from data1 even if there was no match (though in your case, all names have matches).

Optional: Convert Points to Numeric Type #

Notice that the Points columns in data2 are stored as strings. If you need them to be numeric (for calculations later), add this line before merging:

all_name_points['Points'] = pd.to_numeric(all_name_points['Points'])

Output #

Running the code will give you exactly the result you're looking for:

Name          Area Points
0       Cody    California   27.5
1      Billy  Connecticut     21
2   Jeniffer      Indiana     30
3      Franc       Georgia     21
4       Mark      Illinois     50
5      Tamis  Connecticut     90
6      Danye      Illinois     21
7      Leesa      Indiana     20
8     Hector      Illinois     40
9        Coy    California     25

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