Very useful link: How to take data from MySQL and output GeoJSON using Python.
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.
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:
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
Some useful MySQL commands:
How to find rows with null data and delete them:
delete from corridor where wb_id is null;
Note that this WON’T work:
delete from corridor where wb_id = null;
How to update a blank field:
update corridor set corr='MC' where corr is null;