Cross Validated is a question and answer site for people interested in statistics, machine learning, data analysis, data mining, and data visualization. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am a research assistant for a lab (volunteer). I and a small group have been tasked with the data analysis for a set of data pulled from a large study. Unfortunately the data were gathered with an online app of some sort, and it was not programmed to output the data in the most usable form.

The pictures below illustrates the basic problem. I was told that this is called a "Reshape" or "Restructure".

Question: What is the best process for going from Picture 1 to Picture 2 with a large data set with over 10k entries?

This is a sample, the actual file has over 10k entries. I've color coded to help with clarity

This is what it all needs to be converted into.

share|improve this question
Please split this into two separate questions and provide sample data (you can also omit your background unless relevant). Roughly, on (1) you'll want to find a mapping and then merge to attach numbers. (2) depends highly on the type of manipulations you'll want to do; popular R packages for this type of thing include data.table, dplyr, plyr, and reshape2 - I recommend avoiding Excel and pivot tables if possible. – Max Ghenis Jun 23 '14 at 2:54
I'm guessing your data cleaning problems are more extensive than can be covered in the kinds of general questions your asking. You might want to look at A few videos and a download might help you a lot with this part of your analysis. – John Jun 23 '14 at 2:58
This question appears to be off-topic because it is about rudimentary data cleaning and organization, not statistics. – Nick Stauner Jun 23 '14 at 3:48
I'd say it isn't off-topic because cleaning your data, as "rudimentary" as the process might be, is essential to using it. It's part of a larger issue. – ssdecontrol Jun 23 '14 at 4:36
@NickStauner, IIRC I voted to close as 'unclear / needs more info', not as off-topic. It seems to me that data cleaning is within the scope of statistics writ large, & although I recognize good people can disagree, I think such questions can be on-topic. Consider that we have a data-cleaning tag, & these CV threads: 1, 2, 3, & 4. – gung Jun 23 '14 at 16:36

As I noted in my comment, there isn't enough detail in the question for a real answer to be formulated. Since you need help even finding the right terms and formulating your question, I can speak briefly in generalities.

The term you are looking for is data cleaning. This is the process of taking raw, poorly formatted (dirty) data and getting it into shape for analyses. Changing and regularizing formats ("two" $\rightarrow 2$) and reorganizing rows and columns are typical data cleaning tasks.

In some sense, data cleaning can be done in any software and can be done with Excel or with R. There will be pros and cons to both choices:

  • Excel: Excel is almost certainly the most common choice for data cleaning (see R fortunes #59 pdf). It is also considered a poor choice by statisticians. The primary reason is that it is hard to ensure that you have caught everything, or that you have treated everything identically, and there is no record of the changes that you have made, so you can't revisit those changes later. The upside of using Excel is that it will be easier to see what you are doing, and you don't have to know much to make changes. (Statisticians will consider the latter an additional con.)
  • R: R will require a steep learning curve. If you aren't very familiar with R or programming, things that can be done quite quickly and easily in Excel will be frustrating to attempt in R. On the other hand, if you ever have to do this again, that learning will have been time well spent. In addition, the ability to write and save your code for cleaning the data in R will alleviate the cons listed above. The following are some links that will help you get started with these tasks in R:

    You can get a lot of good information on Stack Overflow:

    Quick-R is also a valuable resource:

    Getting numbers into numerical mode:

    Another invaluable source for learning about R is UCLA's stats help website:

    Lastly, you can always find a lot of information with good old Google:

    • This search: data cleaning in r, brings up a number of tutorials (none of which I've worked through, FTR).

Update: This is a common issue regarding the structure of your dataset when you have multiple measurements per 'study unit' (in your case, a person). If you have one row for every person, your data are said to be in 'wide' form, but then you will necessarily have multiple columns for your response variable, for example. On the other hand, you can have just one column for your response variable (but have multiple rows per person, as a result), in which case your data are said to be in 'long' form. Moving between these two formats is often called 'reshaping' your data, especially in the R world.

  • The standard R function for this is ?reshape. There is a guide to using reshape() on UCLA's stats help website.
  • Many people think reshape is hard to work with. Hadley Wickham has contributed a package called reshape2, which is intended to simplify the process. Hadley's personal website for reshape2 is here, the Quick-R overview is here, and there is a nice-looking tutorial here.
  • There are very many questions on SO about how to reshape data. Most of them are about going from wide to long, because that is typically what data analysts are faced with. Your question is about going from long to wide, which is much less common, but there are still many threads about that, you can look through them with this search.
  • If your heart is set on trying to do this with Excel, there is a thread about writing a VBA macro for Excel to replicate the reshape functionality here: melt / rehshape in Excel using VBA?
share|improve this answer
This question, and this answer, is a great illustration of why programming literacy is increasingly necessary for pretty much any kind of research. I'm going to write up a fuller answer to this question that I'm also going to post on my blog, but I wanted to get this comment in before I forgot. – ssdecontrol Jun 23 '14 at 4:37
I agree to some extent but "necessary" and "any kind of research" is going a bit far. A lot of disciplines don't often deal with dirty data and many leading researchers have never needed to touch a programming language. – Behacad Jun 23 '14 at 17:05
Wow, thank you all. It will take me a bit to process and integrate all of this information which is very new to me. A bit of a meta tangent it seems I spurred with my question... Imagine yourself dropped into a foreign land with no knowledge of its language/culture, this is me and stats. I appreciate the time you have all taken in your responding. – user48538 Jun 24 '14 at 5:34
You're welcome, @user48538. If this post helped you, you may consider upvoting it by clicking the upwards facing normal distribution to its left, or accepting it by clicking the check mark beneath the vote total. – gung Jun 24 '14 at 13:56
I've added an edit in hopes of clarifying my initial question, though the question looks to be closed. – user48538 Jun 30 '14 at 2:28

Try following using R:

> ddf
   sess_id user_id     quest  response
1        1       a       age        29
2        1       a satisfied  st_agree
3        1       a    gender      male
4        1       a     phone    iphone
5        2       a       age        29
6        2       a satisfied not_agree
7        2       a    gender    female
8        2       a     phone    iphone
9        3       b       age        29
10       3       b satisfied     agree
11       3       b    gender      male
12       3       b     phone   android
> library(reshape2)
> dcast(ddf, sess_id+user_id ~ quest, value.var='response')
  sess_id user_id age gender   phone satisfied
1       1       a  29   male  iphone  st_agree
2       2       a  29 female  iphone not_agree
3       3       b  29   male android     agree
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.