Part 7: Processing spreadsheets

End result of this part:

run the following Queries

o FlightsQuery.java

We have provided a comma separated values (CSV) file (flights-tiny.csv2) that contains spreadsheet data. Each line in the file represents one airline flight. A line contains several fields, which are integer and string values separated by commas. Each field is like a column in a spreadsheet.

The names of the fields in the csv are

year

month

day of month

airline

flight number

origin city

dest city

cancelled

time

origin city dest city cancelled time: year month day of month airline flight number

In FlightsQueries.java, you'll find a partially written query, where the Iterator called records returns elements which are FlightRecords, a simple object with the fields above.

Your job is to finish the query so that it computes "the number of flights that occurred in the year 2015".

Code

File: FlightsQuery.java

package queries;

import iterators.Apply;

import iterators.ApplyFunction;

import iterators.Reduce;

import iterators.Filter;

import iterators.Predicate;

import readers.LineFileReader;

import java.io.IOException;

import java.util.Iterator;

// return the number of flights that occurred in the year 2015

public class FlightsQuery {

public static void main(String[] args) throws IOException {

//Iterator lines = new LineFileReader("../flights-small.csv"); // expects answer: 520718

Iterator lines = new LineFileReader("../flights-tiny.csv"); // expects answer: 5

Iterator recordsGeneric = new Apply<>(new ParseCSVLine(), lines);

Iterator records = new Apply<>(new ConvertToRecord(), recordsGeneric);

// add more iterators to complete the query

while (/*lastIterator*/.hasNext()) {

System.out.println(/*lastIterator*/.next());

}

}

// define classes you will need for the query here

// object for storing one line in the flights data

private static class FlightRecord {

public final int year;

public final int month;

public final int dayOfMonth;

public final String airline;

public final int flightNumber;

public final String originCity;

public final String destCity;

public final int cancelled; // 0=not cancelled, 1=cancelled

public final int time; // flight time in minutes

public FlightRecord(int year, int month, int dayOfMonth, String airline, int flightNumber, String originCity, String destCity, int cancelled, int time) {

this.year = year;

this.month = month;

this.dayOfMonth = dayOfMonth;

this.airline = airline;

this.flightNumber = flightNumber;

this.originCity = originCity;

this.destCity = destCity;

this.cancelled = cancelled;

this.time = time;

}

@Override

public String toString() {

return "FlightRecord{" + "year=" + year + ", month=" + month + ", dayOfMonth=" + dayOfMonth + ", airline=" + airline + ", flightNumber=" + flightNumber + ", originCity=" + originCity + ", destCity=" + destCity + ", cancelled=" + cancelled + ", time=" + time + '}';

}

}

// Converts a CSV record from an Object[] to a FlightRecord

private static class ConvertToRecord implements ApplyFunction {

@Override

public FlightRecord apply(Object[] r) {

return new FlightRecord((int)r[0],

(int)r[1],

(int)r[2],

(String)r[3],

(int)r[4],

(String)r[5],

(String)r[6],

(int)r[7],

(int)r[8]);

}

}

// takes a string that is a comma separated list of values and returns an array of Strings and Integers.

//

// Examples

// "1,2,3,4"

// [1,2,3,4]

//

// "10,cat,dog,22"

// [10, "cat", "dog", 22]

private static class ParseCSVLine implements ApplyFunction {

@Override

public Object[] apply(String x) {

String[] fields = x.split(",");

Object[] r = new Object[fields.length];

for (int i=0; i

// try to convert to integer

try {

r[i] = Integer.parseInt(fields[i]);

} catch (NumberFormatException ex) {

// if it fails, then leave a string

r[i] = fields[i];

}

}

return r;

}

}

}

Bonus Practice Points

Create an additional file named ExtraCreditFlightsQuery2.java where the main method runs the following query.

o Return the percentage (as a decimal number < 1) of flights between Los Angeles CA and New York NY that were cancelled.

Create an additional file named ExtraCreditFlightsQuery3.java where the main method runs the following query.