In this post, I'd like to focus on data munging, e.g. the process of acquiring and arranging data (typically in a tidy manner) prior to data analysis. It's common knowledge that data scientists spend an enormous amount of time munging data, but data analysis, modeling, and visualization get most of the attention at presentations, on blogs and in the popular imagination. In my current role as a &quot;data scientist,&quot; I spend far more time doing data munging than I do modeling. So let's show some respect for this process by dedicating a post to it!The data come from a website on which consumers can rate their perceptions of different products in the consumer beverage category. Consumers select a specific beverage, give it an overall rating, and can indicate their perceptions of the drink (specifically, the presence or absence of different characteristics).Our end goal, which I will discuss in my next post, is to use the ratings to perform a product segmentation, which will give us insight into how consumers perceive products within the beverage category. In order to reach that goal, we have to distill the information about the different beverages and the information from the consumer reviews into a single matrix that will allow us to derive these answers (with the right type of analysis).As the past couple of posts focused on data analysis using Python, in this exercise I'll switch back to R. The Data
As is often the case, the data we need do not exist in a single tidy dataset. There are two different tables that contain different pieces of information about the beverages and the consumer evaluations.The Review Data The first dataset contains basic information on the reviews. These data contain one line per consumer review, and have a column describing the review id number, the drink name, the drink category (there are three different categories of beverages in these data), and the overall rating score. Here's a sample of what the data look like:
The Perceptions Data The second dataset contains information on the consumer perceptions. Consumers can indicate their perceptions of each drink on a number of different dimensions. In this post, I'll focus on the feelings induced by each beverage, characterized by sensations such as &quot;happy&quot; and &quot;energetic.&quot; The dataset contains one line per perception, and has columns indicating the perception &quot;Group&quot; (here we're focusing on feelings, but other perceptions such as flavor are also present in the data), and the specific sensation within the feeling group (e.g. happy, energetic, etc.). This dataset can contain multiple lines per consumer review, if consumers indicated the presence of multiple characteristics for a given beverage. The id number in the perceptions data corresponds to the id number in the review data (though, as is often the case with real-world data, they do not share the same variable name). The head of the second dataset looks like this:
Step 1: Aggregate the Perceptions Data to the Review Level
We first need to aggregate the perceptions data to the review level, in order to merge them in with the review dataset.We have a number of different &quot;Groups&quot; of perceptions (e.g. feeling, flavor, etc.), and within each different group, we can have different sensations (e.g. happy, energetic, etc.). We can therefore see the combination of group and sensation to be the sensible unit of aggregation; the combination of both variables provides the best descriptor of the information provided by the consumers.The perceptions dataset is called &quot;perceptions_reviewid&quot;. We can simply concatenate the group and sensation columns using base R:# combine group and sensation into one variable called &quot;grp_sens&quot;perceptions_reviewid$grp_sens % summarize(n = n()) %__% ungroup() %__% spread(grp_sens, n, fill=0)
Essentially, we first group the data by id and grp_sens and count the frequency of each combination (as each person can only choose each combination once, the only value this can take will be 1). We then ungroup the data and spread out the grp_sens variable into the columns, filling each column with the number of observations per review (which, as we note above, will only take the value of 1). We then fill missing values (which occur when a given level of grp_sens did not occur in a review) with 0. Voilà!
Step 2: Merge the Review Data and Aggregated Perceptions Data Now we need to merge the two review-level datasets. Our review dataset is simply called &quot;reviews&quot; and our wide-format perceptions dataset is called &quot;perceptions_review_level.&quot;Before merging datasets, I always like to do some basic descriptive statistics on the key variables used in the merge. This type of checking can save lots of trouble down the road in the analysis pipeline, and is one of the &quot;insider&quot; tips and tricks that isn't mentioned enough when discussing data munging.We will be merging on the id variable which is present in both datasets (albeit with a different name in each dataset). Let's see how many id's overlap with each other, and ensure that there are no duplicate id's in either dataset. (Duplicate id's will mess up many merges/joins, causing a dataset to increase dramatically in length, depending on the extent of the duplication). # number of perceptions id's that correspond to an id in the review data sum(reviews$id_reviews %in% perceptions_review_level$id_perceptions)# 105324# number of review id's that correspond to an id in the perception data sum(perceptions_review_level$id_perceptions %in% reviews$id_reviews )# 105324# are there duplicate ids for the review data?sum(duplicated(reviews$id_reviews))# 0# are there duplicate ids for the perceptions data?sum(duplicated(perceptions_review_level$id_perceptions))# 0
We have the same number of matching id's in both datasets: 105,324, and no duplicate id's in either dataset, which means that we can proceed with our merge.We will use dplyr to merge the datasets, specifying the id variables as the &quot;key&quot; in the merge. We'll use an inner join, because we can only analyze beverages with information from both sources. We will immediately check to see whether our merged dataset has the correct number of rows (105,324, the number of matching id's in both datasets).# merge the review and perception data# inner join because we only want those with values in both datasets# it does us no good to have information in only one # (e.g. drink name but no perceptions, perceptions but no drink name)review_level_omnibus % summarise_each_(funs(mean), names(review_level_omnibus[,c(4:ncol(review_level_omnibus))]) )
This aggregation yields a dataset whose head looks like this:
The second aggregation concerns the Category (there are 3 different beverage categories in our data) and the number of reviews for each beverage. Category is constant across each beverage (e.g. each beverage only has one category), while the number of reviews can vary across beverages. We aggregate the data to the beverage level using dplyr:# get the category and number of reviews per beverage drink_level_rest % group_by(Name) %__% summarize(Category = Category[1],Num_Obs = n())
Which yields a dataset whose head looks like this:
(Note- I didn't think it was possible to combine these two different aggregations in a single dplyr chain. If I'm wrong, please let me know in the comments!)Step 4: Merge the Beverage-Level Dataset We will now merge these two aggregated datasets together on the beverage name (&quot;Name&quot;). (I've done the type of checks I describe above on the merging variable, but am not showing them here for the sake of brevity).# merge drink-level datasets togetherdrink_level_omnibus