Multidimensional Crosstable (median values)

user_7707288 0
Viewed 51

I have the following sample data:

samplesize=100
df <- data.frame(sex = sample(c("M", "F"), size = samplesize, replace = TRUE),
                 agegrp = sample(c("old", "middle", "young"), size = samplesize, replace = TRUE),
                 duration1 = runif(samplesize, min = 1, max = 100),
                 duration2 = runif(samplesize, min = 1, max = 100),
                 country = sample(c("USA", "CAN"), size = samplesize, replace = TRUE))
df

My goal is to plot a table like this that displays the median values [median(na.rm = TRUE) as there might be missing values]

            USA                                    CAN
            total  old  middle  young    M    F    total    old  middle  young   M   F
duration1   10.2   12.2    13.1  10.2    13.0 13.9   ...    ...     ...    ...   ... ...
duration2   10.4   13.2    13.2  10.0    13.1 14.0   ...    ...     ...    ...   ... ...

The way I would usually calculate such a table is to calculate the median values columnwise:

df %>% 
  group_by(country, agegrp) %>% 
  summarise(dur1 = median(duration1, na.rm = TRUE), 
            dur2 = median(duration, na.rm = TRUE)

And finally I put all the columns together. Unfortunately, as the number of combinations gets bigger, this methods becomes very cumbersome. So my question is:

Is there any function like table() that let's me calculate means or medians (instead of frequencies) using specific combinations of variables?

It would also be fine if it was just a two-dimensional table with multi-dimensional variable names like:

          USA_total USA_old USA_middle USA_young  USA_m  USA_f   CAN_total ...
duration1
duration2
tidyverse r
1 Answers
library(dplyr)
library(tidyr)
df %>% 
  pivot_longer(cols = -c(sex, agegrp, country), names_to = "parameters") %>% 
  group_by(agegrp, country, parameters) %>% 
    summarise(mean = mean(value, na.rm=TRUE)) %>% 
  pivot_wider(names_from = c(country, agegrp), values_from = mean)

Returns:

  parameters CAN_middle USA_middle CAN_old USA_old CAN_young USA_young
  <chr>           <dbl>      <dbl>   <dbl>   <dbl>     <dbl>     <dbl>
1 duration1        48.6       62.6    31.5    40.0      43.0      50.5
2 duration2        60.9       54.0    53.1    58.9      45.1      55.6

Edit

Including M and F:

library(dplyr)
library(tidyr)
df %>% 
  pivot_longer(cols = c(sex, agegrp), names_to = "groupings_names", values_to="groupings") %>% 
  select(-groupings_names) %>% 
  pivot_longer(cols = -c(groupings, country), names_to = "parameters") %>% 
  group_by(groupings, country, parameters) %>% 
  summarise(mean = mean(value, na.rm=TRUE)) %>% 
  pivot_wider(names_from = c(country, groupings), values_from = mean)

  parameters CAN_F USA_F CAN_M USA_M CAN_middle USA_middle CAN_old USA_old CAN_young USA_young
      <chr>      <dbl> <dbl> <dbl> <dbl>      <dbl>      <dbl>   <dbl>   <dbl>     <dbl>     <dbl>
1 duration1   63.3  59.0  50.9  47.7       57.9       46.1    56.8    60.6      59.5      49.1
2 duration2   60.6  59.0  54.9  48.3       65.0       45.6    48.5    49.5      55.8      62.4
user_53555670
Related