Unpivoting a Table

I have a pivot table with Routes going down and years going across, but I need to normalize it – have a column for the route and a column for the year and then a value for each route, column.

Route2015201620172018
North12141824
South23211917
East19241210
West45585759
Desert231031
Plains23232323
Scenic49483738
Bad Lands121288
Roundabout3336

Oracle and MS SQL databases have a function called UNPIVOT.

MySQL does not have a similar function yet, so you have to do a SELECT, UNION ALL type command.

In R Project, the function is called “Melt”

Doing this in R Project:

1. Install the reshape library if you don’t have it.
2. Program looks like so:


library(reshape)
rte_vol=read.csv("/users/sarod/documents/route_volume.csv")
rte_vol_melted<-melt(rte_vol, id=c("Route")) rte_vol_melted

Line 1 runs the reshape library
Line 2 reads the data in
Line 3 "Melts" the data
Line 4 Shows the output