How to create a function for multiple columns of a Data Frame in R

Question

I am trying to create a function for making tables using columns of data frame:

Freq_table=function(x){
   x<-factor(x)
   T<-table(STI_IPD$Q19_1,x,exclude = NULL)

   T<- data.frame(T)
   library(reshape2)
   T_x<-dcast(T, Var1~Var2)
   T_x<-T_x%>%select(-starts_with("NA"),-ends_with("NA"))

}

here STI_IPD is my Dataframe, and x should be any column which I'm using to create tables with another column Q19_1

This is throwing error:

Error in FUN(X[[i]], ...) : object 'Var2' not found

   Data.frame(T) output is:

                Var1           Var2 Freq
1              Consumer Goods    1    1
2               Life Sciences    1    0
3                   Chemicals    1    0
4         Other Manufacturing    1    0
5                   High Tech    1    0
6                      Energy    1    0
7             Mining & Metals    1    0
8          Retail & Wholesale    1    0
9  Banking/Financial Services    1    0
10      Insurance/Reinsurance    1    0
11   Services (Non-Financial)    1    0
12                  Logistics    1    0
13    Other Non-Manufacturing    1    0
14             Consumer Goods <NA>    1
15              Life Sciences <NA>    1
16                  Chemicals <NA>    1
17        Other Manufacturing <NA>    4
18                  High Tech <NA>    1
19                     Energy <NA>    5
20            Mining & Metals <NA>    0
21         Retail & Wholesale <NA>    1
22 Banking/Financial Services <NA>    5
23      Insurance/Reinsurance <NA>    3
24   Services (Non-Financial) <NA>    5
25                  Logistics <NA>    2
26    Other Non-Manufacturing <NA>    3

output of dput(head(STI_IPD, 30)) is below:

structure(list(Q18_1 = c(NA_integer_, NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
NA_integer_, NA_integer_), Q19_1 = structure(c(9L, 13L, 1L, 9L, 
2L, 6L, 4L, 13L, 9L, 11L, 12L, 4L, 10L, 10L, 11L, 1L, 13L, 11L, 
3L, 6L, 5L, 6L, 6L, 8L, 11L, 12L, 4L, 11L, 4L, 10L), .Label = c("Consumer Goods", 
"Life Sciences", "Chemicals", "Other Manufacturing", "High Tech", 
"Energy", "Mining & Metals", "Retail & Wholesale", "Banking/Financial Services", 
"Insurance/Reinsurance", "Services (Non-Financial)", "Logistics", 
"Other Non-Manufacturing"), class = "factor"), Q46_21_4 = c(NA, 
NA, NA, NA, NA, 1L, NA, NA, NA, NA, NA, NA, 1L, NA, 1L, 1L, NA, 
NA, NA, NA, NA, NA, 1L, 1L, NA, NA, NA, NA, NA, NA), Q46_21_5 = c(NA, 
NA, 1L, NA, NA, 1L, NA, NA, NA, NA, NA, NA, 1L, NA, 1L, 1L, 1L, 
NA, NA, NA, NA, NA, 1L, 1L, NA, 1L, NA, NA, NA, 1L), Q46_21_6 = c(NA, 
NA, 1L, NA, NA, 1L, NA, NA, NA, NA, NA, NA, 1L, NA, 1L, 1L, NA, 
NA, NA, NA, NA, NA, NA, 1L, NA, 1L, NA, NA, NA, 1L), Q46_21_7 = c(NA, 
NA, NA, NA, NA, 1L, NA, NA, NA, NA, NA, NA, 1L, NA, 1L, NA, 1L, 
NA, NA, NA, NA, NA, NA, 1L, NA, NA, NA, NA, NA, 1L), Q46_22_4 = c(NA, 
NA, 1L, NA, 1L, NA, 1L, NA, NA, NA, NA, 1L, 1L, NA, 1L, 1L, NA, 
NA, NA, NA, 1L, NA, NA, 1L, NA, NA, 1L, NA, NA, NA), Q46_22_5 = c(1L, 
1L, 1L, NA, 1L, NA, 1L, NA, NA, 1L, NA, 1L, 1L, 1L, 1L, 1L, NA, 
NA, NA, NA, 1L, NA, NA, 1L, NA, 1L, NA, NA, NA, 1L), Q46_22_6 = c(NA, 
NA, 1L, NA, 1L, NA, NA, NA, NA, 1L, NA, 1L, 1L, 1L, 1L, 1L, NA, 
NA, NA, NA, 1L, NA, NA, 1L, NA, 1L, NA, NA, NA, 1L), Q46_22_7 = c(NA, 
NA, NA, NA, 1L, NA, NA, NA, NA, NA, NA, 1L, 1L, 1L, 1L, NA, NA, 
NA, NA, NA, 1L, NA, NA, 1L, NA, NA, NA, NA, NA, 1L), Q46_23_4 = c(NA, 
NA, 1L, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, NA, 1L, 1L, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA), Q46_23_5 = c(NA, 
NA, 1L, NA, NA, NA, NA, NA, NA, NA, NA, 1L, 1L, NA, 1L, 1L, NA, 
NA, NA, NA, 1L, NA, NA, 1L, NA, 1L, NA, NA, 1L, 1L), Q46_23_6 = c(NA, 
NA, 1L, NA, NA, NA, NA, NA, NA, NA, NA, 1L, 1L, NA, 1L, 1L, NA, 
NA, NA, NA, 1L, NA, NA, 1L, NA, 1L, NA, NA, 1L, 1L), Q46_23_7 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, 1L, NA, NA, NA, NA, 
NA, NA, NA, 1L, NA, NA, 1L, NA, NA, NA, NA, 1L, 1L), Q46_24_4 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, 1L, NA, 
1L, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), Q46_24_5 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, 1L, NA, 
1L, NA, NA, 1L, NA, NA, 1L, NA, 1L, NA, NA, NA, 1L), Q46_24_6 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, 1L, NA, 
1L, NA, NA, 1L, NA, NA, 1L, NA, 1L, NA, NA, NA, 1L), Q46_24_7 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, NA, 
1L, NA, NA, 1L, NA, NA, 1L, NA, NA, NA, NA, NA, 1L), Q46_25_4 = c(1L, 
1L, 1L, NA, NA, NA, NA, 1L, NA, 1L, NA, NA, 1L, 1L, 1L, 1L, NA, 
NA, NA, NA, NA, NA, NA, 1L, 1L, 1L, NA, NA, NA, NA), Q46_25_5 = c(1L, 
1L, 1L, NA, NA, NA, NA, 1L, NA, 1L, NA, NA, 1L, 1L, 1L, 1L, NA, 
1L, NA, NA, NA, NA, NA, 1L, 1L, 1L, NA, NA, NA, 1L), Q46_25_6 = c(1L, 
NA, 1L, NA, NA, NA, NA, 1L, NA, 1L, NA, NA, 1L, 1L, 1L, 1L, NA, 
1L, NA, NA, NA, NA, NA, 1L, 1L, 1L, NA, NA, NA, 1L), Q46_25_7 = c(1L, 
NA, NA, NA, NA, NA, NA, 1L, NA, NA, NA, NA, 1L, 1L, 1L, NA, NA, 
NA, NA, NA, NA, NA, NA, 1L, 1L, NA, NA, NA, NA, 1L), Q46_26_4 = c(1L, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, 1L, NA, 1L, 1L, 
1L, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), Q46_26_5 = c(1L, 
NA, 1L, NA, NA, NA, NA, NA, NA, NA, NA, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, NA, NA, NA, NA, 1L, NA, 1L, NA, NA, NA, 1L), Q46_26_6 = c(1L, 
NA, 1L, NA, NA, NA, NA, NA, NA, NA, NA, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, NA, NA, NA, NA, 1L, NA, 1L, NA, NA, NA, 1L), Q46_26_7 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, 1L, 1L, 1L, NA, 1L, 
1L, NA, NA, NA, NA, NA, 1L, NA, NA, NA, NA, NA, 1L), Q46_27_4 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, NA, 1L, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L), Q46_27_5 = c(NA, 
1L, 1L, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, 1L, 1L, NA, 
NA, 1L, NA, NA, NA, NA, 1L, NA, 1L, NA, NA, NA, 1L), Q46_27_6 = c(NA, 
NA, 1L, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, 1L, 1L, 1L, NA, 
NA, 1L, NA, NA, NA, NA, 1L, NA, 1L, NA, NA, NA, 1L), Q46_27_7 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, 1L, 1L, NA, NA, 
NA, 1L, NA, NA, NA, NA, 1L, NA, NA, NA, NA, NA, 1L), Q46_28_4 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, 
NA, NA, NA, NA, NA, NA, 1L, NA, NA, NA, 1L, NA, NA), Q46_28_5 = c(NA, 
1L, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, 1L, NA, 
NA, 1L, NA, NA, NA, NA, 1L, NA, 1L, NA, 1L, NA, 1L), Q46_28_6 = c(NA, 
NA, 1L, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, 1L, 1L, NA, 
NA, 1L, NA, NA, NA, NA, 1L, NA, 1L, NA, 1L, NA, 1L), Q46_28_7 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, 1L, NA, NA, 
NA, 1L, NA, NA, NA, NA, 1L, NA, NA, NA, 1L, NA, 1L), Q46_29_4 = c(NA, 
NA, NA, 1L, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, 1L, 1L, NA, 
NA, 1L, 1L, NA, 1L, NA, NA, NA, NA, NA, NA, NA, NA), Q46_29_5 = c(NA, 
1L, 1L, 1L, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, 1L, 1L, NA, 
NA, 1L, 1L, NA, 1L, NA, 1L, NA, 1L, NA, NA, NA, 1L), Q46_29_6 = c(NA, 
NA, 1L, 1L, NA, NA, NA, NA, NA, NA, NA, NA, 1L, 1L, 1L, 1L, NA, 
NA, 1L, 1L, NA, 1L, NA, 1L, NA, 1L, NA, NA, NA, 1L), Q46_29_7 = c(NA, 
NA, NA, 1L, NA, NA, NA, NA, NA, NA, NA, NA, 1L, 1L, 1L, NA, NA, 
NA, 1L, 1L, NA, 1L, NA, 1L, NA, NA, NA, NA, NA, 1L), Q46_30_4 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, 
NA, NA, NA, NA, 1L, NA, NA, NA, NA, NA, NA, NA, NA), Q46_30_5 = c(NA, 
NA, 1L, NA, NA, NA, NA, NA, NA, 1L, NA, NA, 1L, NA, 1L, 1L, NA, 
1L, 1L, NA, NA, 1L, NA, 1L, NA, 1L, NA, NA, NA, 1L), Q46_30_6 = c(NA, 
NA, 1L, NA, NA, NA, NA, NA, NA, 1L, NA, NA, 1L, NA, 1L, 1L, NA, 
1L, 1L, NA, NA, 1L, NA, 1L, NA, 1L, NA, NA, NA, 1L), Q46_30_7 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, 1L, NA, NA, 
NA, 1L, NA, NA, 1L, NA, 1L, NA, NA, NA, NA, NA, 1L), Q46_31_4 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, 
NA, NA, NA, NA, 1L, NA, NA, NA, NA, NA, NA, NA, NA), Q46_31_5 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, NA, NA, NA, 1L, 1L, NA, 
NA, 1L, NA, NA, 1L, NA, 1L, NA, 1L, NA, NA, NA, 1L), Q46_31_6 = c(NA, 
NA, 1L, NA, NA, NA, NA, NA, NA, 1L, NA, NA, NA, NA, 1L, 1L, NA, 
NA, 1L, 1L, NA, 1L, NA, 1L, NA, 1L, NA, NA, NA, 1L), Q46_31_7 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, NA, 
NA, 1L, 1L, NA, 1L, NA, 1L, NA, NA, NA, NA, NA, 1L), Q46_32_4 = c(NA, 
1L, NA, NA, NA, NA, NA, NA, 1L, NA, 1L, NA, 1L, NA, NA, 1L, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), Q46_32_5 = c(NA, 
1L, 1L, NA, NA, NA, NA, NA, 1L, 1L, 1L, NA, 1L, 1L, 1L, 1L, NA, 
1L, NA, NA, NA, NA, NA, 1L, NA, 1L, NA, NA, NA, 1L), Q46_32_6 = c(NA, 
NA, 1L, NA, NA, NA, NA, NA, 1L, 1L, 1L, NA, 1L, 1L, 1L, 1L, NA, 
1L, NA, NA, NA, NA, NA, 1L, NA, 1L, NA, NA, NA, 1L), Q46_32_7 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, 1L, NA, 1L, NA, 1L, 1L, 1L, NA, NA, 
NA, NA, NA, NA, NA, NA, 1L, NA, NA, NA, NA, NA, 1L), Q46_33_4 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), Q46_33_5 = c(NA, 
1L, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, 1L, 1L, NA, 
1L, NA, NA, NA, NA, NA, 1L, NA, 1L, NA, NA, NA, NA), Q46_33_6 = c(NA, 
NA, 1L, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, 1L, 1L, NA, 
1L, NA, NA, NA, NA, NA, 1L, NA, 1L, NA, NA, NA, NA), Q46_33_7 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, 1L, NA, NA, 
NA, NA, NA, NA, NA, NA, 1L, NA, NA, NA, NA, NA, NA), Q46_34_4 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), Q46_34_5 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, NA, NA, NA), Q46_34_6 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, NA, NA, NA), Q46_34_7 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA)), .Names = c("Q18_1", 
"Q19_1", "Q46_21_4", "Q46_21_5", "Q46_21_6", "Q46_21_7", "Q46_22_4", 
"Q46_22_5", "Q46_22_6", "Q46_22_7", "Q46_23_4", "Q46_23_5", "Q46_23_6", 
"Q46_23_7", "Q46_24_4", "Q46_24_5", "Q46_24_6", "Q46_24_7", "Q46_25_4", 
"Q46_25_5", "Q46_25_6", "Q46_25_7", "Q46_26_4", "Q46_26_5", "Q46_26_6", 
"Q46_26_7", "Q46_27_4", "Q46_27_5", "Q46_27_6", "Q46_27_7", "Q46_28_4", 
"Q46_28_5", "Q46_28_6", "Q46_28_7", "Q46_29_4", "Q46_29_5", "Q46_29_6", 
"Q46_29_7", "Q46_30_4", "Q46_30_5", "Q46_30_6", "Q46_30_7", "Q46_31_4", 
"Q46_31_5", "Q46_31_6", "Q46_31_7", "Q46_32_4", "Q46_32_5", "Q46_32_6", 
"Q46_32_7", "Q46_33_4", "Q46_33_5", "Q46_33_6", "Q46_33_7", "Q46_34_4", 
"Q46_34_5", "Q46_34_6", "Q46_34_7"), class = c("data.table", 
"data.frame"), row.names = c(NA, -30L), .internal.selfref = <pointer: 0x0000000000090788>)

I have 13 columns for which I am repeating the same activity: — Sandhya Ghildiyal, Nov 03 '17 at 12:46
Can you post the output of `dput(head(STI_IPD, 30))` in your question? In the mean time, what are the names of the columns of `data.frame(T)`? — Rui Barradas, Nov 03 '17 at 12:58
Hi, I have added the data frame gimpse and the Data.frame(T)output.Thanks — Sandhya Ghildiyal, Nov 03 '17 at 13:43
Look, I have asked for the output of `dput(...)` because with it we can simply copy&paste into an R session, it will recreate your data *exactly* as you have it. Thanks for the edit but it's much better to post what I've asked for. Also, how do you call your function? What do you pass as its argument `x`? — Rui Barradas, Nov 03 '17 at 13:48
Hi, I have added dput(head(STI_IPD, 30)) , This function has error. I want column names to be passed as x . I am doing this to generate crosstabs/freq tables of columns with another fix column Q19_1 — Sandhya Ghildiyal, Nov 05 '17 at 10:23

Rui Barradas · Answer 1 · 2017-11-06T10:02:41.623

0

Maybe something like the following.

library(reshape2)
library(tidyverse)

Freq_table <- function(x){
    dat <- data.frame(Q19_1 = STI_IPD$Q19_1, STI_IPD[[x]])
    names(dat)[2] <- x
    m <- melt(dat, id.vars = "Q19_1")
    result <- tryCatch(dcast(m, Q19_1 ~ variable), error = function(e) message(e))
    result <- result %>% select(-starts_with("NA"),-ends_with("NA"))
    result
}

Freq_table("Q46_22_5")
Freq_table("Q46_34_4")

Note that you pass to the function the names of the columns you want, not the columns themselves.

EDIT.
To answer to a request of the OP in a comment, the following code will apply the function above to all but the two first columns of the input dataframe STI_IPD and then merge all the results into one df. The Reduce/mergecode is the answer by Hong Ooi to this question.

lst <- lapply(names(STI_IPD[-(1:2)]), Freq_table)
lst <- lst[!sapply(lst, is.null)]

merge.all <- function(x, y) {
    merge(x, y, all = TRUE, by = "Q19_1")
}

output <- Reduce(merge.all, lst)

edited Nov 06 '17 at 10:02

answered Nov 05 '17 at 10:46

Rui Barradas

70,273
8
34
66

Thank you so much, this solved my problem. I have one more question. I am making tables using 13 columns with this function and in a later stage I want to join these tables by Q19_1. But for one table there is only one column. So how should I join all tables where this column is taken all o or Na? – Sandhya Ghildiyal Nov 05 '17 at 16:21
@SandhyaGhildiyal Maybe with `?merge`, it's difficult to tell without seeing the result data. – Rui Barradas Nov 05 '17 at 16:24
Hi @Rui Barradas, This function giving wrong result, ay be its counting NAs also, that's what I figured out after comparison – Sandhya Ghildiyal Nov 06 '17 at 07:25
if we use dcast(m, Q19_1 ~ value), we will a separate column for NA. – Sandhya Ghildiyal Nov 06 '17 at 07:42
@SandhyaGhildiyal See my edit. Maybe it is what you want. – Rui Barradas Nov 06 '17 at 08:06
Hi, this edited solution is also giving the same output, and NAs are not saparated. Actually problem is for Q46_34_4. this column has all NA counts. Do know how to handle this function – Sandhya Ghildiyal Nov 06 '17 at 09:36
See this is what I want Freq_table <- function(x){ dat <- data.frame(Q19_1 = Q46_exc$Q19_1, Q46_exc[[x]]) names(dat)[2] <- x m <- melt(dat) T<-tryCatch(dcast(m, Q19_1 ~ value),error = function(e) message(e)) T<-T%>%select(-starts_with("NA"),-ends_with("NA")) T }, but this can not handle Q46_34_4 – Sandhya Ghildiyal Nov 06 '17 at 09:37
@SandhyaGhildiyal Just edited the code again. Now it handles `Q46_34_4`. – Rui Barradas Nov 06 '17 at 10:06
Hi, dcast(m, Q19_1 ~ variable) is not giving right answer. dcast(m, Q19_1 ~ value) is giving right output. and finally this one is working: newfreq <- function(x){ STI_IPD[[x]]<-factor(STI_IPD[[x]]) T<-table(STI_IPD$Q19_1,STI_IPD[[x]],exclude = NULL) dat <- data.frame(T) library(reshape2) T1<-dcast(dat, Var1~Var2) T1<-T1%>%select(-starts_with("NA"),-ends_with("NA")) names(T1)<-c("Industry",paste("new",x,sep = "_")) T1 } – Sandhya Ghildiyal Nov 07 '17 at 12:12

How to create a function for multiple columns of a Data Frame in R

1 Answers1