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.

  • 1
    $\begingroup$ 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. $\endgroup$
    – Max Ghenis
    Commented Jun 23, 2014 at 2:54
  • $\begingroup$ 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 OpenRefine.org. A few videos and a download might help you a lot with this part of your analysis. $\endgroup$
    – John
    Commented Jun 23, 2014 at 2:58
  • 3
    $\begingroup$ This question appears to be off-topic because it is about rudimentary data cleaning and organization, not statistics. $\endgroup$ Commented Jun 23, 2014 at 3:48
  • 2
    $\begingroup$ 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. $\endgroup$ Commented Jun 23, 2014 at 4:36
  • 2
    $\begingroup$ @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. $\endgroup$ Commented Jun 23, 2014 at 16:36

3 Answers 3


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?
  • 2
    $\begingroup$ 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. $\endgroup$ Commented Jun 23, 2014 at 4:37
  • 2
    $\begingroup$ 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. $\endgroup$
    – Behacad
    Commented Jun 23, 2014 at 17:05
  • $\begingroup$ 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. $\endgroup$
    – Wilkoe
    Commented Jun 24, 2014 at 5:34
  • $\begingroup$ I've added an edit in hopes of clarifying my initial question, though the question looks to be closed. $\endgroup$
    – Wilkoe
    Commented Jun 30, 2014 at 2:28
  • $\begingroup$ The question is closed, @user48538, & this version is more clearly off-topic than the previous, unfortunately. If you have 10k rows, you definitely don't want to try to do this in Excel. You really have to use R or similar programming software. The name of the specific operation is to reshape your dataset from 'long' to 'wide', I will add a few more links for you. In addition, if you are an undergraduate volunteer, w/ little to no background in stats or programming, it was highly irresponsible of whoever to assign this task to you, IMHO. There is no way this should be your task. $\endgroup$ Commented Jun 30, 2014 at 2:45

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

In scala this is called an "explode" operation and can be done on a dataFrame. If your data is an rdd, you first convert to dataFrame via toDF command and then use the .explode method.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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