Crosstab to Flat File in R with melt()

Most of the time when using R your data frames will be structured as a cross tabulation. This means each row corresponds to a single observation and each column corresponds to a single variable. For example, let us create a data frame consisting of height and weight data for 4 imaginary people in the form of a cross tab.

# generating data 
name = c('Nick','Josh','James','Kelly')
weight = c(72,85,62,50)
height = c(180,162,170,175)

# forming a cross tab
crossTab.df = data.frame(name,weight,height)

# viewing
   name weight height
1  Nick     72    180
2  Josh     85    162
3 James     62    170
4 Kelly     50    175

Sometimes you may want your data frame to be re-structured as a flat file, particularly if you are using the data visualization and dash-boarding software Tableau to display output from R. The solution lies in the melt() function found in the package reshape2. Lets transform our example data into a flat file:

# loading the required library

# forming a flat file
flatFile.df = melt(data=crossTab.df,id='name') 

# viewing
   name variable value
1  Nick   weight    72
2  Josh   weight    85
3 James   weight    62
4 Kelly   weight    50
5  Nick   height   180
6  Josh   height   162
7 James   height   170
8 Kelly   height   175

Of course, to reverse the process you have the function recast() at your disposal…

# back to cross tab
original.df = recast(flatFile.df,name~variable)

# viewing
   name weight height
1 James     62    170
2  Josh     85    162
3 Kelly     50    175
4  Nick     72    180

Note the order of the observations has changed but the data is as it was originally.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s