0
$\begingroup$

I got this US police shootings dataset where many age are null. Each person's race and gender is also given.

Here is a sample below:

df[['age', 'race', 'gender']].sample(10)
>>>
        age     race    gender
id          
705     27.0    B         M
657     23.0    W         M
2307    34.0    W         M
7529    34.0    NaN       M
5871    28.0    H         M
1449    37.0    B         M
7243    82.0    NaN       M
1374    27.0    B         M
479     36.0    W         M
345     58.0    W         M

Instead of simply using the mean or other method such as, ffill, bfill, to fill the null ages, I want to put the average age of the gender of that particular race to which the person belongs. For example, if the average age of males in Hispanic (H) race is 27, I want to put 27 in every null age where the person is a Hispanic Male.

I can't come up with any implementation of this using pandas. I could only proceed as far as this groupby() operation:

RAG_df = df[['race', 'gender', 'age']]
age_by_race_gender = RAG_df.groupby(['race','gender']).mean()
age_by_race_gender
>>>
                 age
race    gender  
A         F     43.67
          M     36.04
B         F     34.03
          M     32.80
H         F     32.47
          M     33.65
N         F     31.20
          M     32.62
O         F     25.00
          M     33.14
W         F     39.81
          M     40.08

I can't understand how can I get the average age of a race by gender out of here to fill the null values.

Improve this question
$\endgroup$
4
  • $\begingroup$ How about you don't do this? This will likely bias your results, why do you need this? $\endgroup$
    – user2974951
    Dec 21, 2022 at 7:15
  • $\begingroup$ Yeah, I was thinking that it may produce inappropriate data. Still I want to know how to do this from coding knowledge perspective. $\endgroup$
    – forest
    Dec 21, 2022 at 7:17
  • $\begingroup$ What do you suggest would be preferable to fill the null values? $\endgroup$
    – forest
    Dec 21, 2022 at 7:21
  • $\begingroup$ I don't know, how you treat missing values is very dependent on what your goal is, what kind of analysis you will run, and what options you have available. $\endgroup$
    – user2974951
    Dec 21, 2022 at 8:04

1 Answer 1

Reset to default
2
$\begingroup$

You can use transform after groupby to get the same dimension data out of it

RAG["age2"] = RAG_df.groupby(['race','gender'])["age"].transform("mean")

You can now substitute the missing values in age with the values in age2.

Note: this won't work for all the data if you have missing values in your race or gender variables, which you seem to have, you will need to figure out something to fill those rows.

Improve this answer
$\endgroup$
1
  • $\begingroup$ Instead of substituting, is there any other way such, as pandas fillna()? I tried with this code: df['new'] = df.groupby(['race','gender'])['age'].transform(lambda x: x.fillna(x.mean())), then with this: df["age"] = df['age'].fillna(df.groupby(['race','gender'])['age'].transform('mean')), but none are working. It's returning the same column as the age column. $\endgroup$
    – forest
    Dec 23, 2022 at 7:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.