Mongo

Loading data from csv into Mongo

• save file as Windows separated csv - regular csv will result in `fields cannot be identical: ' ' and ' ' error

from command line (not mongo shell) mongoimport --db live_test --collection students --type csv --headerline --file data/ed-index-students.csv mongoimport --db live_test --collection outcomes --type csv --headerline --file data/ed-index-outcomes.csv mongoimport --db live_test --collection schools --type csv --headerline --file data/ed-index-schools.csv mongoimport --db live_test --collection students --type csv --headerline --file data/ed-index-students.csv mongoimport --db live_test --collection students --type csv --headerline --file data/ed-index-students.csv

to nest year data: mongo ./database_setup/nest_year_data.js

db.states_schools.find({}, { opeidstring: 1, school: 1 }).forEach(function(doc){ db.combined.insert(doc); });

db.final.aggregate([{ $lookup: { from:"states_schools", localField:"opeidstring", foreignField:"opeidstring", as:"schools" }},{$project:{_id:0}},{ $unwind:"$schools" },{ $out: "final"}]); db.final.aggregate([{ $lookup: { from:"students", localField:"opeidstring", foreignField:"opeidstring", as:"students" }},{$project:{_id:0}},{ $unwind:"$students" },{ $out: "final"}]); db.final.aggregate([{ $lookup: { from:"loans", localField:"opeidstring", foreignField:"opeidstring", as:"loans" }},{$project:{_id:0}},{ $unwind:"$loans" },{ $out: "final"}]); db.final.aggregate([{ $lookup: { from:"grants", localField:"opeidstring", foreignField:"opeidstring", as:"grants" }},{$project:{_id:0}},{ $unwind:"$grants" },{ $out: "final"}]); db.final.aggregate([{ $lookup: { from:"outcomes", localField:"opeidstring", foreignField:"opeidstring", as:"outcomes" }},{$project:{_id:0}},{ $unwind:"$outcomes" },{ $out: "final"}]);

mongodump --db live_test mongorestore -h ds151909.mlab.com:51909 -d heroku_2l5qrfnd -u 'kjackson' -p '' dump/live_test

States

Start Mongo Server: mongod

Import CSV Data: mongoimport --db febp --collection states_students --type csv --headerline --file data/states/ed-index-students-states.csv mongoimport --db febp --collection states_outcomes --type csv --headerline --file data/states/ed-index-outcomes-states.csv mongoimport --db febp --collection states_schools --type csv --headerline --file data/states/ed-index-schools-states.csv mongoimport --db febp --collection states_grants --type csv --headerline --file data/states/ed-index-grants-states.csv mongoimport --db febp --collection states_loans --type csv --headerline --file data/states/ed-index-loans-states.csv

Nest year data: mongo ./database_setup/nest_year_data.js

Add entry for each state to combined collection

use febp db.states_schools.find({}, { id: 1, state: 1 }).forEach(function(doc){ db.states_combined.insert(doc); });

Aggregate collections into combined collection

db.states_combined.aggregate([{ $lookup: { from:"states_schools", localField:"id", foreignField:"id", as:"schools" }},{$project:{_id:0}},{ $unwind:"$schools" },{ $out: "states_combined"}]);
db.states_combined.aggregate([{ $lookup: { from:"states_students", localField:"id", foreignField:"id", as:"students" }},{$project:{_id:0}},{ $unwind:"$students" },{ $out: "states_combined"}]);
db.states_combined.aggregate([{ $lookup: { from:"states_loans", localField:"id", foreignField:"id", as:"loans" }},{$project:{_id:0}},{ $unwind:"$loans" },{ $out: "states_combined"}]);
db.states_combined.aggregate([{ $lookup: { from:"states_grants", localField:"id", foreignField:"id", as:"grants" }},{$project:{_id:0}},{ $unwind:"$grants" },{ $out: "states_combined"}]);
db.states_combined.aggregate([{ $lookup: { from:"states_outcomes", localField:"id", foreignField:"id", as:"outcomes" }},{$project:{_id:0}},{ $unwind:"$outcomes" },{ $out: "states_combined"}]);

Get Full State Names

mongo ./database_setup/get_full_state_names.js

add path keys

mongo ./database_setup/add_path_keys.js

Institutions

Start Mongo Server: mongod

Import CSV Data: mongoimport --db febp --collection inst_students --type csv --headerline --file data/inst/ed-index-students-inst.csv mongoimport --db febp --collection inst_outcomes --type csv --headerline --file data/inst/ed-index-outcomes-inst.csv mongoimport --db febp --collection inst_schools --type csv --headerline --file data/inst/ed-index-schools-inst.csv mongoimport --db febp --collection inst_grants --type csv --headerline --file data/inst/ed-index-grants-inst.csv mongoimport --db febp --collection inst_loans --type csv --headerline --file data/inst/ed-index-loans-inst.csv

Nest year data: mongo ./database_setup/nest_year_data.js

Add entry for each state to combined collection

use febp db.inst_grants.find({}, { unitid: 1, school: 1 }).forEach(function(doc){ db.inst_combined.insert(doc); });

Aggregate collections into combined collection

db.inst_combined.aggregate([{ $lookup: { from:"inst_schools", localField:"unitid", foreignField:"unitid", as:"schools" }},{$project:{_unitid:0}},{ $unwind:"$schools" },{ $out: "inst_combined"}]);
db.inst_combined.aggregate([{ $lookup: { from:"inst_students", localField:"unitid", foreignField:"unitid", as:"students" }},{$project:{_unitid:0}},{ $unwind:"$students" },{ $out: "inst_combined"}]);
db.inst_combined.aggregate([{ $lookup: { from:"inst_loans", localField:"unitid", foreignField:"unitid", as:"loans" }},{$project:{_unitid:0}},{ $unwind:"$loans" },{ $out: "inst_combined"}]);
db.inst_combined.aggregate([{ $lookup: { from:"inst_grants", localField:"unitid", foreignField:"unitid", as:"grants" }},{$project:{_unitid:0}},{ $unwind:"$grants" },{ $out: "inst_combined"}]);
db.inst_combined.aggregate([{ $lookup: { from:"inst_outcomes", localField:"unitid", foreignField:"unitid", as:"outcomes" }},{$project:{_unitid:0}},{ $unwind:"$outcomes" },{ $out: "inst_combined"}]);

Rename "school" field to "name" and add path keys

mongo ./database_setup/add_path_keys.js

Uploading to Production Database

mongodump --db febp