Data Janitor Example: Baseball Matches

Baseball Matches

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.

The successor to csvjson is here: an AI agent that can convert your data to and from any format, securely.

Baseball Matches