How to loop over a list of variables in a dataframe, and for each variable apply a filter and get a weighted frequency table?

user_14277257 0
Viewed 155

I have the following example survey dataset: 

df <- data.frame(sex = c(1, 1, 2, 2, 1, 2, 2, 2, 1, 2),
                 age = c(15, 40, 97, 25, 99, 65, 20, 99, 39, 48), 
                 nationality= c(1, 3, 1, 2, 4, 97, 2, 2, 2, 99),
                 employment = c(2, 1, 99, 1, 1, 1, 1, 1, 2, 2),
                 income = c(-1, 2500, 999997, 10000, 65000, 999998, 999999, 15000, -1, -1),
                 weight = c(100, 20, 400, 300, 50, 50, 80, 250, 100, 100))

The following list contains selected variables that I want to use in a for loop:

list <- list(age = df$age, employment = df$employment, income = df$income)

I want to loop over the list of selected variables in the dataframe, and for each variable in the list apply a filter (condition) and get a weighted frequency table from the filtered data. In pseudocode this is what I want to do:

for i in list {
       filter(i >= 1 & i <= max(i)-2 %>%
       weighted frequency of var i based on 'weight' 
        }

I have tried many ways to do this in R but I still can’t figure out how. The last time I used this: 

library(dplyr)
library(expss)
for (i in list){
     filter(i > 1 & i < max(i))-2 %>%
     fre(i, weight = df$weight)
}

But I get this error message: 

Error in UseMethod("filter_") : 
  no applicable method for 'filter_' applied to an object of class "logical"

I need to figure out how to do it because I need to loop over a list of 256 variables.

The results must be: 

library(dplyr)
library(expss)

age: <br />

F <- df %>% filter(age >= 1 & age < 97)
fre(F$age, weight = F$weight)

 |  F$age | Count | Valid percent | Percent | Responses, % | Cumulative responses, % |
 | ------ | ----- | ------------- | ------- | ------------ | ----------------------- |
 |     15 |   100 |          13.3 |    13.3 |         13.3 |                    13.3 |
 |     20 |    80 |          10.7 |    10.7 |         10.7 |                    24.0 |
 |     25 |   300 |          40.0 |    40.0 |         40.0 |                    64.0 |
 |     39 |   100 |          13.3 |    13.3 |         13.3 |                    77.3 |
 |     40 |    20 |           2.7 |     2.7 |          2.7 |                    80.0 |
 |     48 |   100 |          13.3 |    13.3 |         13.3 |                    93.3 |
 |     65 |    50 |           6.7 |     6.7 |          6.7 |                   100.0 |
 | #Total |   750 |         100.0 |   100.0 |        100.0 |                         |
 |   <NA> |     0 |               |     0.0 |              |                         |

employment: <br />
F <- df %>% filter(employment >= 1 & employment < 97)
fre(F$employment, weight = F$weight)

 | F$employment | Count | Valid percent | Percent | Responses, % | Cumulative responses, % |
 | ------------ | ----- | ------------- | ------- | ------------ | ----------------------- |
 |            1 |   750 |          71.4 |    71.4 |         71.4 |                    71.4 |
 |            2 |   300 |          28.6 |    28.6 |         28.6 |                   100.0 |
 |       #Total |  1050 |         100.0 |   100.0 |        100.0 |                         |
 |         <NA> |     0 |               |     0.0 |              |                         |

income: <br />
F <- df %>% filter(income >= 1 & income < 999997)
fre(F$income, weight = F$weight)

| F$income | Count | Valid percent | Percent | Responses, % | Cumulative responses, % |
 | -------- | ----- | ------------- | ------- | ------------ | ----------------------- |
 |     2500 |    20 |           3.2 |     3.2 |          3.2 |                     3.2 |
 |    10000 |   300 |          48.4 |    48.4 |         48.4 |                    51.6 |
 |    15000 |   250 |          40.3 |    40.3 |         40.3 |                    91.9 |
 |    65000 |    50 |           8.1 |     8.1 |          8.1 |                   100.0 |
 |   #Total |   620 |         100.0 |   100.0 |        100.0 |                         |
 |     <NA> |     0 |               |     0.0 |              |                         |
dataframe r
2 Answers

Let's see here. You'll need to update your for loop syntax. I added parentheses and converted the index to a number. You should also initialize the object you want the loop to fill up.

Next, you'll need to convert the columns to their names and filter using rlang syntax that converts 'age' to age (standard evaluation to non-standard evaluation).

library(dplyr)
library(rlang)

df <- data.frame(sex = c(1, 1, 2, 2, 1, 2, 2, 2, 1, 2),
                 age = c(15, 40, 97, 25, 99, 65, 20, 99, 39, 48), 
                 nationality= c(1, 3, 1, 2, 4, 97, 2, 2, 2, 99),
                 employment = c(2, 1, 99, 1, 1, 1, 1, 1, 2, 2),
                 income = c(-1, 2500, 999997, 10000, 65000, 999998, 999999, 15000, -1, -1),
                 weight = c(100, 20, 400, 300, 50, 50, 80, 250, 100, 100))

## just list the names in a vector
loop_over <- c('age' ,'employment', 'income')

## initialize the object you want the loop to fill
final <- list()

for (i in 1:length(loop_over)) {
  ## !!sym() coverts the column name to non-standard evaluation
  temp <- df %>%
   filter( !!sym(loop_over[i]) >= 1 & !!sym(loop_over[i]) <= max(!!sym(loop_over[i])) - 2 )
  
  avg <-  fre( temp[[ loop_over[i] ]], weight = temp$weight )

  final[i] <- list(avg)
}
user_73314630

The lapply version.

library(dplyr)
library(expss)

vars_to_run <- c('age' , 'employment', 'income')
 
lapply(setNames(vars_to_run, vars_to_run), function(x){
  
  z <- sym(x)
  
  df_filter <- df %>%
    filter(!!z >= 1 & !!z <= max(!!z) - 2 ) 
  
  fre(df_filter[, x], weight = df_filter[, 'weight'])
  
  })

Or using base R and [.

lapply(setNames(vars_to_run, vars_to_run), function(x){
  
  df_filter <- df[df[x] >= 1 & df[x] <= max(df[x]) - 2, ]
  
  expss::fre(df_filter[x], weight = df_filter[ , 'weight'])
  
})

Both answers return a named list

$age
                                                                                            
 | df_filter[x] | Count | Valid percent | Percent | Responses, % | Cumulative responses, % |
 | ------------ | ----- | ------------- | ------- | ------------ | ----------------------- |
 |           15 |   100 |           8.7 |     8.7 |          8.7 |                     8.7 |
 |           20 |    80 |           7.0 |     7.0 |          7.0 |                    15.7 |
 |           25 |   300 |          26.1 |    26.1 |         26.1 |                    41.7 |
 |           39 |   100 |           8.7 |     8.7 |          8.7 |                    50.4 |
 |           40 |    20 |           1.7 |     1.7 |          1.7 |                    52.2 |
 |           48 |   100 |           8.7 |     8.7 |          8.7 |                    60.9 |
 |           65 |    50 |           4.3 |     4.3 |          4.3 |                    65.2 |
 |           97 |   400 |          34.8 |    34.8 |         34.8 |                   100.0 |
 |       #Total |  1150 |         100.0 |   100.0 |        100.0 |                         |
 |         <NA> |     0 |               |     0.0 |              |                         |

$employment
                                                                                            
 | df_filter[x] | Count | Valid percent | Percent | Responses, % | Cumulative responses, % |
 | ------------ | ----- | ------------- | ------- | ------------ | ----------------------- |
 |            1 |   750 |          71.4 |    71.4 |         71.4 |                    71.4 |
 |            2 |   300 |          28.6 |    28.6 |         28.6 |                   100.0 |
 |       #Total |  1050 |         100.0 |   100.0 |        100.0 |                         |
 |         <NA> |     0 |               |     0.0 |              |                         |

$income
                                                                                            
 | df_filter[x] | Count | Valid percent | Percent | Responses, % | Cumulative responses, % |
 | ------------ | ----- | ------------- | ------- | ------------ | ----------------------- |
 |         2500 |    20 |           2.0 |     2.0 |          2.0 |                     2.0 |
 |        10000 |   300 |          29.4 |    29.4 |         29.4 |                    31.4 |
 |        15000 |   250 |          24.5 |    24.5 |         24.5 |                    55.9 |
 |        65000 |    50 |           4.9 |     4.9 |          4.9 |                    60.8 |
 |       999997 |   400 |          39.2 |    39.2 |         39.2 |                   100.0 |
 |       #Total |  1020 |         100.0 |   100.0 |        100.0 |                         |
 |         <NA> |     0 |               |     0.0 |              |                         |
user_124003850
Related