python-3.x - 根据来自另一个数据集的条件添加输出列

Question

job Education   Age   Number of relatives   
1   1            25          5  
1   2            23          20 
3   4            26          50 
2   1            37          100    
4   3            29          34 

output    Job   Education   agemin  agemax  relativesmin    relativesmax
Category1   1   1            25      34       1                 11
Category2   2   3            35      44       11                50
Category3   3   2            45      100      50                200

所以问题是如何在第一个数据集中添加列输出，但基于条件（df1.job == df2.Job ...并且年龄在第二个数据集的agemin和agemax之间）输出应该如下所示：

job Education   Age   Number of relatives    output 
1   1            25          5                Category1
1   2            23          20               Category2
3   4            26          50               Uncategorized
2   1            37          100              ....
4   3            29          34               ....

我尝试了几种方法，包括 iterrows 并加入两个数据集，但我没有得到我需要的结果

标签： python-3.xpandasdataset

Answer 1

国际大学联合会，

我们可以合并然后使用带有列分配的简单过滤器：

df2.columns = df2.columns.str.lower()
df_new = pd.merge(df1, df2[["job", "agemin", "agemax", "output"]], on="job", how="left")

df_new.loc[
    ~((df_new["Age"] >= df_new["agemin"]) & (df_new["Age"] <= df_new["agemax"])), "output"
] = "Uncategorised"

---

print(df_new)

   job  Education  Age  Number_of_relatives  agemin  agemax         output
0    1          1   25                    5    25.0    34.0      Category1
1    1          2   23                   20    25.0    34.0  Uncategorised
2    3          4   26                   50    45.0   100.0  Uncategorised
3    2          1   37                  100    35.0    44.0      Category2
4    4          3   29                   34     NaN     NaN            NaN