基于R语言处理调查数据：识别儿童兄弟姐妹并标记是否存在≤1岁的兄弟姐妹

Got it, let's break down how to solve this problem. The key here is to first group children by their cluster and house_number (since siblings share the same household and cluster), then check if there are any other children in that group aged ≤1. Here's a step-by-step implementation using pandas:

Step 1: Prepare the Sample Data #

First, let's recreate your sample dataset to work with:

import pandas as pd

# Original sample data
data = {
    'cluster': [1,1,1,1,1,2,2,2,2,2],
    'house_number': [5,5,8,21,21,22,36,5,5,5],
    'age': [0,1,4,4,1,0,0,0,2,3]
}
df = pd.DataFrame(data)

Step 2: Calculate Household-Level Count of Children ≤1 #

We'll first compute how many children in each household are aged ≤1, and map that value to every row in the household using transform:

# Add a column with the count of children ≤1 in the same cluster+household
df['under1_count'] = df.groupby(['cluster', 'house_number'])['age'].transform(lambda x: (x <= 1).sum())

Step 3: Determine if There's a Sibling Aged ≤1 #

Now, for each child, we need to exclude their own age from the count (since we're checking for siblings, not themselves). If the adjusted count is greater than 0, it means there's at least one sibling aged ≤1:

# Create the binary result column
df['sibling_age1'] = df.apply(
    lambda row: 1 if (row['under1_count'] - (1 if row['age'] <= 1 else 0)) > 0 else 0,
    axis=1
)

# Clean up the intermediate column (optional)
df = df.drop('under1_count', axis=1)

Step 4: Verify the Result #

Running this code will give you exactly the target output you provided:

How This Works #

• Grouping: We use groupby(['cluster', 'house_number']) to isolate each household's children.
• Count Calculation: transform ensures every row gets the count of ≤1-year-olds in their household, instead of just one value per group.
• Adjust for Self: By subtracting 1 if the current child is ≤1 (since we don't want to count themselves as a sibling), we check if there are other children in the household who meet the age criteria.

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