Aggregating and mapping observations from an open questionnaire
<p><strong>Summary</strong></p>

<p>I want to create a boxplot like this one displaying the most frequent perceived problems in every neighborhood of a city. <a href="https://i.stack.imgur.com/6MPui.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6MPui.png" alt="Sample boxplot with real data"></a></p>

<p>Unfortunately, the boxplot is useless as it is, since the data I am using comes from an open questionnaire and it has two main problems: </p>

<ol>
<li>There are a lot of irrelevant answers (by irrelevant I refer to those which are used by only one or few people)</li>
<li>There are problems that refer to the same concept but have been rephrased differently and thus are counted as something different.</li>
</ol>

<p>In order to make it more useful I would like to aggregate irrelevant answers in a single group "eg: <code>other problems</code> and rename the problems that mean the same so they are worded exactly and thus can be displayed properly in the barplot. Unfortunately I didn't succeed in doing so.</p>

<p><strong>Detailed explanation</strong></p>

<p>Let's take a look at a sample code (The names on the dataframe are just examples: I have modified them for the sake of clarity so it makes it easier to understand that two or more problems are related, but the real terms can't always be deduced from a regular expression):</p>

<pre><code>library(plyr)
library(dplyr)
library(tidyr)

df= read.csv("http://pastebin.com/raw/bUxANQw6")

problems = df %&gt;%
  select(Problems) %&gt;%
  gather(variable, value) %&gt;%
  group_by(value) %&gt;%
  summarise(Total = n()) %&gt;%
  arrange(desc(Total))
</code></pre>

<p>Which result in the following dataframe:</p>

<pre><code>&gt; problems
Source: local data frame [27 x 2]

          value Total
1     Problem 1   282
2     Problem 3   268
3     Problem 2   186
4   No problems   160
5     Problem 4    76
6     Problem 5    68
7     Problem 6     6
8     Problem 7     5
9  Doesn't know     4
10    Problem 8     2
..          ...   ...
&gt; 
</code></pre>

<p>As can be seen we have 27 problems, and looking at them more carefuly we could stablish some groups:</p>

<ol>
<li>Relevant data: Problems 1 to 7 + <code>No Problems</code> and <code>Doesn't know</code></li>
<li>Synomyms: we have <code>Problem 9</code>, <code>Problem 9'</code>, <code>Problem 9''</code> or <code>Problem 9'''</code> (amongst others)</li>
<li>Irrelevant data, which should be grouped under a single label, like "Other Problems": Problems 12 to 18</li>
</ol>

<p><strong>My suggested approach</strong></p>

<p>That's what I thought I could do in order to overcome these two problems:</p>

<p>In order to deal with <strong>synonyms</strong>, I thought of renaming the synonym values into a single one, possibly using <code>revalue</code> command, something like this:</p>

<pre><code>df$Problems = revalue(df$Problems, c('Problem 9’' = 'Problem 9',
                                     'Problem 9’’' = 'Problem 9',
                                     'Problem 9’’’' = 'Problem 9'))
</code></pre>

<p>However, as a R newbie (and newbie to programming languages, as well) I think there should be a faster way to achieve that, since the task of maintaining a "synonyms' dictionary" will be very tedious and will be growing when getting more replies. </p>

<p>In order to deal with <strong>irrelevant answers</strong>, I could take a similar approach, and revalue them as <code>Other problems</code>, but I would like to do it in an automated way, since the list of irrelevant terms will be growing as the questionnaire has not yet finished and I cannot map all of them manually (eg: map all values which have been voted by less than 5 people <code>Total &lt; 5</code>). I guess I should create a function and use a control structure (<code>for ... in</code>) but I have not yet succeeded on that. </p>

<p>Since I need to display a boxplot of the answers grouped by neighborhoods, I'm afraid I can't use the <code>problems</code> dataframe as it is. So although it is useful to calculate total number of votes per problem, I do not know what to do with it other than use it as informative data. On the other hand, I cannot determine if an answer is irrelevant based only on the replies received in each neighborhood, as it would bias the results, since it is expected that different neighborhoods may have different problems. </p>

<p>Any help with these two problems will be really appreciated. Thanks</p>