使用awk处理Titanic CSV时，第12列(Embarked)替换仅最后一行生效求助

处理泰坦尼克号CSV数据集时，用awk脚本替换第5列（Sex）和第12列（Embarked）内容：

• Sex列：将male/female替换为m/f
• Embarked列：将S/C/Q替换为Southampton/Cherbourg/Queenstown

执行后Sex列替换正常，但仅最后一行Embarked列替换生效，其余行无变化。已确认第12列无特殊字符，Python中相同逻辑可正常运行。

原始CSV样例 #

PassengerId,Survived,Pclass,Name,Sex,Age,SibSp,Parch,Ticket,Fare,Cabin,Embarked
1,0,3,"Braund, Mr. Owen Harris",male,22,1,0,A/5 21171,7.25,Nan,S
2,1,1,"Cumings, Mrs. John Bradley (Florence Briggs Thayer)",female,38,1,0,PC 17599,71.28,C85,C
...
886,0,3,"Rice, Mrs. William (Margaret Norton)",female,39,0,5,382652,29.12,Nan,Q
890,1,1,"Behr, Mr. Karl Howell",male,26,0,0,111369,30.00,C148,C
891,0,3,"Dooley, Mr. Patrick",male,32,0,0,370376,7.75,Nan,Q

预期修改后结果 #

PassengerId,Survived,Pclass,Name,Sex,Age,SibSp,Parch,Ticket,Fare,Cabin,Embarked
1,0,3,"Braund, Mr. Owen Harris",m,22,1,0,A/5 21171,7.25,Nan,Southampton
2,1,1,"Cumings, Mrs. John Bradley (Florence Briggs Thayer)",f,38,1,0,PC 17599,71.28,C85,Cherbourg
...
886,0,3,"Rice, Mrs. William (Margaret Norton)",f,39,0,5,382652,29.12,Nan,Queenstown
890,1,1,"Behr, Mr. Karl Howell",m,26,0,0,111369,30.00,C148,Cherbourg
891,0,3,"Dooley, Mr. Patrick",m,32,0,0,370376,7.75,Nan,Queenstown

实际执行结果 #

PassengerId,Survived,Pclass,Name,Sex,Age,SibSp,Parch,Ticket,Fare,Cabin,Embarked
1,0,3,"Braund, Mr. Owen Harris",m,22,1,0,A/5 21171,7.25,Nan,S
2,1,1,"Cumings, Mrs. John Bradley (Florence Briggs Thayer)",f,38,1,0,PC 17599,71.28,C85,C
...
886,0,3,"Rice, Mrs. William (Margaret Norton)",f,39,0,5,382652,29.12,Nan,Q
890,1,1,"Behr, Mr. Karl Howell",m,26,0,0,111369,30.00,C148,C
891,0,3,"Dooley, Mr. Patrick",m,32,0,0,370376,7.75,Nan,Queenstown

使用的awk脚本 #

BEGIN {
    FPAT = "([^,]*)|(\"[^\"]+\")"
    OFS = ","
}

{
    # Cambiar el valor de la columna sexo a 0 si es "female" o a 1 si es "male"
    if ($5 == "female") 
        $5 = "f"
    else if ($5 == "male") 
        $5 = "m"
    
    # Realizar la sustitución en la columna embarked
    if ($12 == "C") 
        $12 = "Cherbourg"
    else if ($12 == "Q") 
        $12 = "Queenstown"
    else if ($12 == "S") 
        $12 = "Southampton"
     
    print $0
}

问题根源是awk的字段重赋值机制：修改任意字段后，awk会立即用OFS重新拼接生成新的$0，此时FPAT的解析规则失效，后续访问的$12已经不是原始的第12列。

因为你先修改了$5，之后再访问$12时，awk是按默认空白分隔符分割新的$0，而非原始的FPAT解析结果，导致大部分行的$12无法匹配到S/C/Q，只有最后一行巧合命中。

修正方案1：先保存原始Embarked值 #

先把原始的第12列值保存到变量，修改Sex列后再用变量更新Embarked列：

BEGIN {
    FPAT = "([^,]*)|(\"[^\"]+\")"
    OFS = ","
}

{
    # 先保存原始Embarked列的值
    embarked = $12
    
    # 修改Sex列
    if ($5 == "female") 
        $5 = "f"
    else if ($5 == "male") 
        $5 = "m"
    
    # 根据保存的值更新Embarked列
    if (embarked == "C") 
        $12 = "Cherbourg"
    else if (embarked == "Q") 
        $12 = "Queenstown"
    else if (embarked == "S") 
        $12 = "Southampton"
     
    print $0
}

修正方案2：用数组存储所有字段 #

将所有字段存入数组，修改数组元素后再拼接输出，彻底避免字段重构的影响：

BEGIN {
    FPAT = "([^,]*)|(\"[^\"]+\")"
    OFS = ","
}

{
    # 把所有字段存入数组
    for (i=1; i<=NF; i++) {
        fields[i] = $i
    }
    
    # 修改Sex列
    fields[5] = (fields[5] == "female") ? "f" : (fields[5] == "male") ? "m" : fields[5]
    
    # 修改Embarked列
    switch(fields[12]) {
        case "C": fields[12] = "Cherbourg"; break
        case "Q": fields[12] = "Queenstown"; break
        case "S": fields[12] = "Southampton"; break
    }
    
    # 拼接数组并输出
    $0 = fields[1]
    for (i=2; i<=NF; i++) {
        $0 = $0 OFS fields[i]
    }
    print $0
}

两种方案都能让所有行的Sex和Embarked列正确替换。

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