Data Janitor Example
Let's say we have a table of game matches copied and pasted from Excel. This is the input table.
Match | Date | Result |
---|---|---|
Twins vs Yankees | 17-10-03 | 4 - 8 |
Rockies vs Diamondbacks | 17-10-04 | 8 - 11 |
Red Sox vs Astros | 17-10-05 | 2 - 8 |
We would like to split up the teams and match results. We also want to format the date differently. To something like this:
Home | Away | Date | Home Score | Away Score | Winner |
---|---|---|---|---|---|
Twins | Yankees | 3 Oct 2017 | 4 | 8 | Yankees |
Rockies | Diamondbacks | 4 Oct 2017 | 8 | 11 | Diamondbacks |
Red Sox | Astros | 5 Oct 2017 | 2 | 8 | Astros |
Function call process(input, columns)
will be passed:
input
[ { "Match": "Twins vs Yankees", "Date": "17-10-03", "Result": "4 - 8" }, { "Match": "Rockies vs Diamondbacks", "Date": "17-10-04", "Result": "8 - 11" }, { "Match": "Red Sox vs Astros", "Date": "17-10-05", "Result": "2 - 8" } ]
columns
["Match", "Date", "Result"]
We could write the process
function as such:
function process(input, columns) { var output = []; input.forEach(function(inRow, r) { var outRow = {}; var teams = inRow.Match.split('vs'); outRow.Home = teams[0].trim(); outRow.Away = teams[1].trim(); var date = moment(inRow.Date, 'YY-MM-DD'); outRow.Date = date.format('MMM Do YYYY'); var scores = inRow.Result.split('-'); outRow['Home Score'] = parseInt(scores[0].trim(), 10); outRow['Away Score'] = parseInt(scores[1].trim(), 10); outRow.Winner = outRow['Home Score'] > outRow['Away Score'] ? outRow.Home : outRow.Away; if (outRow['Home Score'] == outRow['Away Score']) outRow.Winner = 'Tie'; output.push(outRow); }); return output; }
The output will be an array of rows; a list of hash objects like this:
[ { "Home": "Twins", "Away": "Yankees", "Date": "Oct 3rd 2017", "Home Score": 4, "Away Score": 8, "Winner": "Yankees" }, { "Home": "Rockies", "Away": "Diamondbacks", "Date": "Oct 4th 2017", "Home Score": 8, "Away Score": 11, "Winner": "Diamondbacks" }, { "Home": "Red Sox", "Away": "Astros", "Date": "Oct 5th 2017", "Home Score": 2, "Away Score": 8, "Winner": "Astros" } ]
The Output table will contain a tabular representation of this data. You can copy/paste it back to Excel or download it as CSV.