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.
Route | 2015 | 2016 | 2017 | 2018 |
---|---|---|---|---|
North | 12 | 14 | 18 | 24 |
South | 23 | 21 | 19 | 17 |
East | 19 | 24 | 12 | 10 |
West | 45 | 58 | 57 | 59 |
Desert | 23 | 10 | 3 | 1 |
Plains | 23 | 23 | 23 | 23 |
Scenic | 49 | 48 | 37 | 38 |
Bad Lands | 12 | 12 | 8 | 8 |
Roundabout | 3 | 3 | 3 | 6 |
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