counting occurrence of strings across multiple columns in R

Question

I have a dataset in R which looks like the following (only relevant columns shown). It has sex disaggregated data on what crops respondents wanted more information about and how much of a priority this crop for them.

sex     wantcropinfo1    priority1  wantcropinfo2     priority2 
m       wheat            high       eggplant          medium
m       rice             low        cabbage           high
m       rice             high
f       eggplant         medium
f       cotton           low
...    

I want to be able to (a) count the total occurrences of each crop across all the wantcropinfoX columns; and (b) get the same count but sort them by priority; and (c) do the same thing but disaggregated by sex.

(a) output should look like this:

 crop     count
 wheat    1
 eggplant 2
 rice     2
 ...

(b) output should look like this:

 crop     countm   countf
 wheat    1        0
 eggplant 1        1
 rice     2        0
 ...

(c) should look like this:

 crop     high_m   med_m  low_m  high_f   med_f   low_f
 wheat    1        0      0      0        0       0
 eggplant 0        1      0      0        1       0
 rice     1        0      1      0        0       0
 ...

I'm a bit of an R newbie and the manuals are slightly bewildering. I've googled a lot but couldn't find anything that was quite like this even though it seems like a fairly common thing one might want to do. Similar questions on stackoverflow seemed to be asking something a bit different.

Answer 1

We can use melt from data.table to convert from 'wide' to 'long' format. It can take multiple measure columns.

library(data.table)
dM <- melt(setDT(df1), measure = patterns("^want", "priority"),
       value.name = c("crop", "priority"))[crop!='']

In the 'long' format, we get the 3 expected results by either grouping by 'crop' and get the number of rows or convert to 'wide' with dcast specifying the fun.aggregate as length.

dM[,.(count= .N) , crop]
#       crop count
#1:    wheat     1
#2:     rice     2
#3: eggplant     2
#4:   cotton     1
#5:  cabbage     1

dcast(dM, crop~sex, value.var='sex', length)
#       crop f m
#1:  cabbage 0 1
#2:   cotton 1 0
#3: eggplant 1 1
#4:     rice 0 2
#5:    wheat 0 1

dcast(dM, crop~priority+sex, value.var='priority', length)
#       crop high_m low_f low_m medium_f medium_m
#1:  cabbage      1     0     0        0        0
#2:   cotton      0     1     0        0        0
#3: eggplant      0     0     0        1        1
#4:     rice      1     0     1        0        0
#5:    wheat      1     0     0        0        0

Answer 2

Use ddply function in the plyr package.

The structure of how you use this function is the following:

ddply(dataframe,.(var1,var2,...), summarize, function)

In this case you might want to do the follow for:

1. a) ddply(df,.(wantcropinfo1),summarize,count=length(wantcropinfo1))
2. b)ddply(df,.(wantcropinfo1,priority),summarize,count=length(wantcropinfo1))
3. c) ddply(df,.(wantcropinfo1,priority,sex),summarize,count=length(wantcropinfo1))

Note that the output will not have the same structure you mention in your question but the information will be the same. For the mentioned structure use the table function