Cross tabulation of two variables with averages in R?

Question

I have the following social network dataset where participants (ego) were asked who provided social, work, and care support in their lives. Those who provided support (alter) were classified according to their relationship with ego (circle) resulting in the following dataset:

ego    alter    circle    social    work   care
3400    3403      1         0         0     1
3400    3402      1         0         1     0
3400    3401      1         1         0     0
3500    3504      1         0         0     0
3500    3503      1         0         0     0
3500    3502      1         0         1     1
3500    3501      2         1         0     0
3600    3604      1         0         0     0
3600    3603      3         0         0     1
3600    3602      3         0         1     0
3600    3601      2         1         0     0
3700    3702      1         0         1     1
3700    3703      1         0         0     1
3700    3701      2         1         0     0
…

So, for example, in row 1, alter 3403 of social circle 1, did not provide social or work support but provided care support for ego 3400.

My question for you all is: how can I cross tabulate the variable circle with each of the support variables (social, work, and care) and then calculate the averages with ego?

Below is the resulting cross tabulation with totals and percentages, but I need the averages taking into account each ego.

Crosstab result

score 1 · Answer 1 · answered Feb 13 '22 at 23:59

First, reproducible data using dput():

social <- structure(list(ego = c(3400L, 3400L, 3400L, 3500L, 3500L, 3500L, 
3500L, 3600L, 3600L, 3600L, 3600L, 3700L, 3700L, 3700L), alter = c(3403L, 
3402L, 3401L, 3504L, 3503L, 3502L, 3501L, 3604L, 3603L, 3602L, 
3601L, 3702L, 3703L, 3701L), circle = c(1L, 1L, 1L, 1L, 1L, 1L, 
2L, 1L, 3L, 3L, 2L, 1L, 1L, 2L), social = c(0L, 0L, 1L, 0L, 0L, 
0L, 1L, 0L, 0L, 0L, 1L, 0L, 0L, 1L), work = c(0L, 1L, 0L, 0L, 
0L, 1L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 0L), care = c(1L, 0L, 0L, 
0L, 0L, 1L, 0L, 0L, 1L, 0L, 0L, 1L, 1L, 0L)), class = "data.frame", row.names = c(NA, 
-14L))

Now, counts,

(tbl.count <- aggregate(cbind(social, work, care)~circle, social, sum))
#   circle social work care
# 1      1      1    3    4
# 2      2      3    0    0
# 3      3      0    1    1

and means,

(tbl.mean <- aggregate(cbind(social, work, care)~circle, social, mean))
#   circle    social      work      care
# 1      1 0.1111111 0.3333333 0.4444444
# 2      2 1.0000000 0.0000000 0.0000000
# 3      3 0.0000000 0.5000000 0.5000000

and percentages,

(tbl.pct <- aggregate(cbind(social, work, care)~circle, social, function(x) mean(x)*100))
#   circle    social     work     care
# 1      1  11.11111 33.33333 44.44444
# 2      2 100.00000  0.00000  0.00000
# 3      3   0.00000 50.00000 50.00000

Cross tabulation of two variables with averages in R?

1 Answers1