解析两个CSV日志文件，生成含项目关联的完整员工数据

Got it, let's work through this problem. You need to merge two CSV files to get a complete dataset of employees—including those who haven't been assigned any projects. Here are a couple of practical approaches using Python (great for flexibility) and awk (perfect for quick command-line processing):

First, let's clarify the input file structures for clarity:

file1.txt (employee ID → name mapping):

EmpId,EmpName
1,abc
2,ac
3,bc
4,acc
5,abb
6,bbc
7,aac
8,aba
9,aaa

file2.txt (employee ID ↔ project ID relationships, with duplicate entries):

EmpId,ProjectId
1,102
2,102
1,103
3,101
5,102
1,103
2,105
2,200
9,101

This method is easy to tweak if you need to adjust output format or add extra logic later.

# Step 1: Load employee name mappings into a dictionary
employee_map = {}
with open('file1.txt', 'r') as f:
    next(f)  # Skip header line
    for line in f:
        emp_id, emp_name = line.strip().split(',')
        employee_map[emp_id] = emp_name

# Step 2: Load project relationships (automatically remove duplicates)
project_map = {}
with open('file2.txt', 'r') as f:
    next(f)  # Skip header line
    for line in f:
        emp_id, proj_id = line.strip().split(',')
        if emp_id not in project_map:
            project_map[emp_id] = set()  # Use set to avoid duplicates
        project_map[emp_id].add(proj_id)

# Step 3: Generate complete employee data
print("EmpId,EmpName,AssignedProjects")
for emp_id, emp_name in employee_map.items():
    # Get projects for the employee, or default to "No projects assigned"
    assigned_projects = project_map.get(emp_id, set())
    proj_str = ','.join(assigned_projects) if assigned_projects else "No projects assigned"
    print(f"{emp_id},{emp_name},{proj_str}")

What this does: #

• Uses a dictionary to quickly look up employee names by ID
• Uses a set to automatically eliminate duplicate project entries for the same employee
• Handles employees with no projects by adding a clear placeholder text
• Outputs a clean CSV with all required fields

If you're working in a Linux/macOS terminal and don't want to write a full Python script, awk is a fast alternative.

Save this as combine_emp_proj.awk:

BEGIN {
    FS = ","  # Set input field separator to comma
    OFS = ","  # Set output field separator to comma
    print "EmpId,EmpName,AssignedProjects"
}

# Process file1.txt first: store employee names and initialize project lists
NR == FNR {
    if (FNR > 1) {  # Skip header line
        emp_name[$1] = $2
        projects[$1] = ""
    }
    next
}

# Process file2.txt: collect unique projects per employee
FNR > 1 {
    # Only add the project if it hasn't been added before for this employee
    if (!($1 in proj_seen) || !($2 in proj_seen[$1])) {
        if (projects[$1] != "") projects[$1] = projects[$1] ","
        projects[$1] = projects[$1] $2
        proj_seen[$1][$2] = 1
    }
}

# Final step: print all employee data
END {
    for (id in emp_name) {
        proj_str = projects[id] == "" ? "No projects assigned" : projects[id]
        print id, emp_name[id], proj_str
    }
}

Run it with:

awk -f combine_emp_proj.awk file1.txt file2.txt

Quick Notes: #

• Whitespace Handling: If your files have spaces around commas (e.g., 1, abc), adjust the field separator to FS = /\s*,\s*/ in awk, or use re.split(r'\s*,\s*', line.strip()) in Python.
• Keep Duplicates: If you want to retain duplicate project entries (instead of removing them), just remove the set/proj_seen checks in either approach.

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