Question about crosstabs with groups

[gdemin]

Question about crosstabs with groups

[gdemin]

Hi, @robertogilsaura
I can't get the same result: sum(data$liters)/length(unique(data$hh)) gives me 5.92. What I am missing?
Generally speaking there is a function tab_stat_fun_df which can apply arbitrary function on several columns simultaneously.
We can construct function which will compute means on the base of unique the id:

mean_on_id = function(dfs){
    # dfs - data.frame
    # first column - value
    # all other columns - object id, it will be hh in our case
    # we should reference data.frame column by number because at runtime it will be unknown labels of the variables
    setNames(
        sum(dfs[[1]], na.rm = TRUE)/nrow(unique(dfs[,-1, drop = FALSE])),
        colnames(dfs)[1] # here we set name on the result
    )
}

Than we provide this function to the tab_stat_fun_df:

data%>%
    tab_cols (total (), gender) %>%
    tab_cells (data.frame(liters, hh))%>% # note the data.frame with two variables
    tab_stat_fun_df(mean_on_id) %>% 
    tab_pivot ()

Which give me:

 |        | #Total | gender |     |
 |        |        |      1 |   2 |
 | ------ | ------ | ------ | --- |
 | liters |    5.9 |    3.9 | 4.7 |

[robertogilsaura]

Hi @gdemin ... You are right, muy compute is incorrect. I should have made a mistake in the computing. I will work on your solution to obtain other habitual cases. These cases of crossed tables are very typical in my work with two different criteria to apply statistically in the group and between groups. The most common case is this in which the liters of men and women are added separately. But there is another more atypical and more complex that adds up the liters of the whole household if in that home at least there is an individual of the variable category of columns. That is, if a man and a woman live in a household, the liters of that household would be added to the column of men and the column of women. I attach an EXCEL with compute for helping in my explanation. Data for computing sum by household is the same: set.seed (311265) hh <- sample (1:75, 100, replace = TRUE) gender <- sample (1: 2, 100, replace = TRUE) liters <- sample (1: 5, 100, replace = TRUE) sc <- sample (1:10, 100, replace = TRUE) data <- data.frame (hh, gender, liters, sc) Again, thanks in advance for all your help. regards Rober El mié., 16 ene. 2019 a las 19:19, Rober Gil (<[email protected]>) escribió:

…

Hi @gdemin <https://github.com/gdemin> This is not an issue, it is a question. I have the following dataframe: library (expss) set.seed (311265) hh <- sample (1:75, 100, replace = TRUE) gender <- sample (1: 2, 100, replace = TRUE) liters <- sample (1: 5, 100, replace = TRUE) sc <- sample (1:10, 100, replace = TRUE) data <- data.frame (hh, gender, liters, sc) and I want to compute in the expss table format, average of liters of water consumed in households (hh) where there are men and the average liters of water consumed in households where there are women, as well as the average liters of water consumed in homes. For this I need to calculate the sum of liters in each household and averaging. Is it possible in a single step, or should an intermediate dataframe be created? the structure of the table would be this, but the cells should contain the indicated measure. data%>% tab_cols (total (), gender)%>% tab_cells (liters)%>% # but liters, should be the sum of the liters of each household tab_stat_mean ()%>% tab_pivot () In the dataframe there are 55 households and there are 38 households in which there is at least one man and 38 households in which there is at least one woman. My result in EXCEL is |Sum of liters| TOTAL_ | 1 | 2| |Mean| 5.33| 2.49| 2.84| thanks in advance — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#27>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AKxoP-QKm2DKBS5xsaat0omh0anwZE6cks5vD21LgaJpZM4aDqrx> .

[gdemin]

@robertogilsaura Excel attachment is missed

[robertogilsaura]

Sorry I attached from gmail ..
Attach here the file ...
data.xlsx

Thanks

[gdemin]

As for your second case.
We create to column, one for households with males, other for hh with females. And then we use these columns as multiple response:

library(expss)

set.seed (311265) 
hh <- sample (1:75, 100, replace = TRUE) 
gender <- sample (1: 2, 100, replace = TRUE) 
liters <- sample (1: 5, 100, replace = TRUE) 
sc <- sample (1:10, 100, replace = TRUE) 
data <- data.frame (hh, gender, liters, sc) 



mean_on_id = function(dfs){
    # dfs - data.frame
    # first column - value
    # all other columns - object id, it will be hh in our case
    # we should reference data.frame column by number because at runtime it will be unknown labels of the variables
    setNames(
        sum(dfs[[1]], na.rm = TRUE)/nrow(unique(dfs[,-1, drop = FALSE])),
        colnames(dfs)[1] # here we set name on the result
    )
}

data%>%
    compute({
       # `ave` - function from base R - see ?ave. It is similar to window functions from SQL 
        gen_1 = ave(gender, hh, FUN = function(x) ifelse(any(x==1), 1, NA)) # hh has males
        gen_2 = ave(gender, hh, FUN = function(x) ifelse(any(x==2), 2, NA)) # hh has females
        var_lab(gen_1) = "Household type"
        val_lab(gen_1) = num_lab("
                                 1 Households with males
                                 2 Households with females
                                 ")
    }) %>% 
    tab_cols (total (), mrset(gen_1, gen_2)) %>%
    tab_cells (data.frame(liters, hh))%>% # note the data.frame with two variables
    tab_stat_fun_df(mean_on_id) %>% 
    tab_pivot ()

# |        | #Total |        Household type |                         |
# |        |        | Households with males | Households with females |
# | ------ | ------ | --------------------- | ----------------------- |
# | liters |  5.923 |                 6.853 |                   6.526 |

[robertogilsaura]

Thanks @gdemin

Code runs properly. I will work with this pattern, because I have some combination for measures intragroup / between group. For instance ...

• maxim intra / mean between
• minim intra / mean between
• sum intra / sum between
• max intra / sum between
• ...etc

.... among others

Thanks for your kindness and quick answers.

regards

Rober