how to create variable containing the age of another row inside a common id in Python 3.8?

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]})
id rank age sex care new
1 1 20 female NA NA
1 2 2 female NA NA
2 1 30 male 3 4
2 2 28 female NA NA
2 3 4 male NA NA
3 1 26 female 2 3
3 2 3 male NA NA
4 1 22 female NA NA
4 2 23 male 3 1
4 3 1 male NA NA
5 1 34 male NA NA
5 2 29 female NA NA
6 1 56 female 2 25
6 2 25 female NA NA

Hello people, the above table contains the following variables:

  • id : home identification number
  • rank : ranking of the person within the household, e.g., 1 means head of household, 2 spouse 3 children
  • age : age of the person
  • sex : sex of the person
  • care : contains the ranking of the person within the household to whom care services are provided

so I want to create a variable called new that contains the age of the person receiving care within the home. I am trying to do this in Python 3.8

I did this exercise in Stata with this command:

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

and also did it in rtudio like this:

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

I hope I have made myself understood and also not cause inconvenience, I thank you in advance

Answer

Try this one liner:

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

The result is exactly what you want:

>>> 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>