如何在Python3.8中创建包含公共id内另一行年龄的变量？

Question

df = pd.DataFrame({'id': [1,1,2,2,2,3,3,4,4,4,5,5,6,6],
                   'rank' : [1,2,1,2,3,1,2,1,2,3,1,2,1,2],
                   'age' : [20,2,30,28,4,26,3,22,23,1,34,29,56,25],
                   'sex' : ['female','female','male','female','male','female','male','female','male','male','male','female','female','female'],
                   'care' : [np.nan,np.nan,3,np.nan,np.nan,2,np.nan,np.nan,3,np.nan,np.nan,np.nan,2,np.nan]})

^{tb1}$

大家好，上表包含以下变量：

• id：家庭识别号
• 等级：家庭中个人的等级，例如，1表示户主，2配偶3子女
• 年龄：人的年龄
• 性别：人的性别
• 护理：包含家庭中接受护理服务的人员的排名

所以我想创建一个名为new的变量，它包含在家中接受护理的人的年龄。我正试图在Python3.8中实现这一点

我在Stata用这个命令做了这个练习：

rangestat new = age, by(id) int(rank care care)

在rtudio也这样做：

df <- df %>%                             
  group_by(id) %>%
  mutate(new = age[match(care, rank)]) %>%
  ungroup

我希望我已经让自己明白，也不会造成不便，我提前感谢你

Answer 1

你想要这样的吗

new = pd.Series([2,np.nan,4,np.nan,np.nan,3,np.nan,np.nan,1,np.nan], name="new")
new_df = pd.concat([df, new], axis=1)
print(new_df)

"""
   id  rank  age     sex  care  new
0   1     1   20  female   2.0  2.0
1   1     2    2  female   NaN  NaN
2   2     1   30    male   3.0  4.0
3   2     2   28  female   NaN  NaN
4   2     3    4    male   NaN  NaN
5   3     1   26  female   2.0  3.0
6   3     2    3    male   NaN  NaN
7   4     1   22  female   NaN  NaN
8   4     2   23    male   3.0  1.0
9   4     3    1    male   NaN  NaN
"""

Answer 2

试试这一行：

result = pd.merge(
    df,
    df[['id', 'rank', 'age']].rename(
        columns={'rank': 'care', 'age': 'new'},
    ),
    how='left',
)

结果正是您想要的：

>>> result
   id  rank  age     sex  care   new
0   1     1   20  female     2     2
1   1     2    2  female  <NA>  <NA>
2   2     1   30    male     3     4
3   2     2   28  female  <NA>  <NA>
4   2     3    4    male  <NA>  <NA>
5   3     1   26  female     2     3
6   3     2    3    male  <NA>  <NA>
7   4     1   22  female  <NA>  <NA>
8   4     2   23    male     3     1
9   4     3    1    male  <NA>  <NA>