How can i check that how many times each element of splitted string occurs in each column?

user_13709085 0
Viewed 57

Lets say i have a string which I split.

x<- 'A,B,C,D'
x<-unlist(strsplit(x, ","))

And lets say I have this data which I store in a variable called y.

I want to count each element of x in each column of y. For example, I would count the number of A in the first column, the number of B in the first column, the number of C in the first column, number of D in the first column. I would do this for each column. How is that possible? I am a very beginner to R so I am having issues figuring this one out. This is something I want to use in a shiny app. The user inputs a string which is then split and then it is checked that how many times each element of splitted string occurs in each column.

r
2 Answers

If we want to count the number of letters 'A', 'B', 'C', 'D' in all the column, loop over the columns, get the frequency count with table on a factor converted column with levels specified as the 'A', 'B', 'C', 'D' so that if some elements are missing, it returns 0 as count and rbind the output to create a single data

out <- do.call(rbind, lapply(df1, function(x) 
      table(factor(x, levels = c('A', 'B', 'C', 'D')))))

-output

out
     A  B  C  D
V1  14 17  4  8
V2  21  5  6 14
V3   2 36  6  4
V4   3  9 18  7
V5   7 33  5  3
V6   8  3 26 10
V7  13  3 15 17
V8   3  5 38  0
V9   4 15 15 10
V10  9 10 11 17
V11  6 29  7  5
V12 29  5  3  9
V13  4 28  6  9
V14  4  8 24 11
V15  2 10  9 25
V16 32  6  3  6
V17  7 29  3  0
V18 28  4  3  3
V19 11 12 15  1
V20 21  1  1 15
V21 18  1 15  2
V22  7 19  4  6
V23  0  0 34  2
V24 26  4  2  0
V25  4  8 23  4
V26  9 10 12 12
V27  4 20  8  9
V28 24  6  9  4
V29  8  9  8 11
V30 11  9 10 12
V31  3  8 26  8
V32 11 11 15  5
V33  4 14 10 11
V34 13  8  7 18
V35  8  3 30  2
V36 12  7 13 12
V37  3 16  7 12
V38  6 23  9  2
V39  2 17  8 17
V40  9 16 12  6
V41  7 17  8 12
V42 19  8  8  6
V43  9  7 11  7
V44 12  9 13 10
V45 10  8  9  8
V46 22  3  7 13
V47  2  1  2 40
V48  5 31  3  5
V49  4  7 11 19
V50  6  7 10 12
V51 18  9  1  9
V52 22  6  8 10
V53 20 17  5  3
V54  6 10 17  3
V55  7 19 18  3
V56  4  2 31  8
V57 28  6 12  2
V58  5 10  9 18
V59 12 10 10 14
V60 10 13 12  2
V61  6 12 11 15
V62 32  2  7  4
V63  4 23  9  6
V64 15  3  8 15
V65  5 20 16  6
V66  8 13  7 20
V67  3 22  3 19
V68 32  3  7  5
V69 12  2 32  2
V70  2  3  2 40
V71  9 10 22  5
V72  8 10 19  6
V73  8  8 12 15
V74  7  8 12 16
V75  1  6 34  5
V76  9  3  1 33
V77  1 26  6  8
V78  7  8 25  4
V79 13  2  4 22
V80  4  6 27  1

Another option is to apply table once after unlisting along with the colum index

table(c(col(df1)), unlist(df1))

-output

      A  B  C  D
  1  14 17  4  8
  2  21  5  6 14
  3   2 36  6  4
  4   3  9 18  7
  5   7 33  5  3
  6   8  3 26 10
  7  13  3 15 17
  8   3  5 38  0
  9   4 15 15 10
  10  9 10 11 17
  11  6 29  7  5
  12 29  5  3  9
  13  4 28  6  9
  14  4  8 24 11
  15  2 10  9 25
  16 32  6  3  6
  17  7 29  3  0
  18 28  4  3  3
  19 11 12 15  1
  20 21  1  1 15
  21 18  1 15  2
  22  7 19  4  6
  23  0  0 34  2
  24 26  4  2  0
  25  4  8 23  4
  26  9 10 12 12
  27  4 20  8  9
  28 24  6  9  4
  29  8  9  8 11
  30 11  9 10 12
  31  3  8 26  8
  32 11 11 15  5
  33  4 14 10 11
  34 13  8  7 18
  35  8  3 30  2
  36 12  7 13 12
  37  3 16  7 12
  38  6 23  9  2
  39  2 17  8 17
  40  9 16 12  6
  41  7 17  8 12
  42 19  8  8  6
  43  9  7 11  7
  44 12  9 13 10
  45 10  8  9  8
  46 22  3  7 13
  47  2  1  2 40
  48  5 31  3  5
  49  4  7 11 19
  50  6  7 10 12
  51 18  9  1  9
  52 22  6  8 10
  53 20 17  5  3
  54  6 10 17  3
  55  7 19 18  3
  56  4  2 31  8
  57 28  6 12  2
  58  5 10  9 18
  59 12 10 10 14
  60 10 13 12  2
  61  6 12 11 15
  62 32  2  7  4
  63  4 23  9  6
  64 15  3  8 15
  65  5 20 16  6
  66  8 13  7 20
  67  3 22  3 19
  68 32  3  7  5
  69 12  2 32  2
  70  2  3  2 40
  71  9 10 22  5
  72  8 10 19  6
  73  8  8 12 15
  74  7  8 12 16
  75  1  6 34  5
  76  9  3  1 33
  77  1 26  6  8
  78  7  8 25  4
  79 13  2  4 22
  80  4  6 27  1

data

df1<- read.csv(file, header = FALSE, na.strings = "")
user_37322710

Next time use the dput() function to paste your R Object into Stackoverflow. I think you want to use the table function but it is a little unclear of your data structure.
First you are going to want convert whatever your y variable is to a data.frame. This is necessary if you want to convert to columns.

dfy<-as.data.frame(y)

###use the apply + table to loop through columns and count frequency ##

data<-apply(dfy,2, table)

###This will give you a list  with the count of each unique variable in each column ##

###You can unlist it to a df

as.data.frame(unlist(data, recursive = TRUE, use.names = TRUE ))-> tt

Someone might have a neater way to unlist but that should get you your answer

user_120439460
Related