public Dictionary <string, int> FlightsPerMonthForLGA() { var context = new Nycflights13DBContext(); return(context.Flights.Where(f => !string.IsNullOrEmpty(f.Origin) && f.Origin.Equals("LGA")).Select(f => f.Month).ToList() .GroupBy(m => m).OrderBy(g => g.Key).ToDictionary(g => monthsByNumber[g.Key], g => g.Count())); }
public Dictionary <string?, int> FlightsToTopTenDestinations() { var context = new Nycflights13DBContext(); return(context.Flights.Select(f => f.Dest).ToList().GroupBy(d => d).ToDictionary(g => g.Key, g => g.Count()) .OrderByDescending(val => val.Value).Take(10).ToDictionary(g => g.Key, g => g.Value)); }
public Dictionary <string, int> FlightsManufacturersMoreThanTwoHundredPlanes() { var context = new Nycflights13DBContext(); Dictionary <string, int> flightsManufacturersMoreThanTwoHundredPlanes = new Dictionary <string, int>(); Dictionary <string, int> manufacturersMoreThanTwoHundredPlanes = ManufacturersMoreThanTwoHundredPlanes(); Dictionary <string, int> countByTailNum = context.Flights.Select(f => f.Tailnum).ToList().GroupBy(m => m).ToDictionary(g => g.Key, g => g.Count()); foreach (KeyValuePair <string, int> pair in manufacturersMoreThanTwoHundredPlanes) { List <string> tailNums = context.Planes.Where(p => p.Manufacturer.Equals(pair.Key)).Select(p => p.Tailnum).ToList(); int flights = 0; int flightsForTailNum = 0; foreach (string tailNum in tailNums) { countByTailNum.TryGetValue(tailNum, out flightsForTailNum); flights += flightsForTailNum; } flightsManufacturersMoreThanTwoHundredPlanes.Add(pair.Key, flights); } return(flightsManufacturersMoreThanTwoHundredPlanes); }
public Dictionary <string, string> MeanDepartureAndArrivalDelayForLGA() { var context = new Nycflights13DBContext(); double?averageDepDelayLGA = context.Flights.Where(f => !string.IsNullOrEmpty(f.Origin) && f.Origin.Equals("LGA")).Select(f => f.Dep_delay).ToList().Average(); double?averageArrDelayLGA = context.Flights.Where(f => !string.IsNullOrEmpty(f.Origin) && f.Origin.Equals("LGA")).Select(f => f.Arr_delay).ToList().Average(); TimeSpan tavgDepDelayLGA = new TimeSpan(); TimeSpan tavgArrDelayLGA = new TimeSpan(); if (averageDepDelayLGA != null) { tavgDepDelayLGA = TimeSpan.FromMinutes((double)averageDepDelayLGA); } if (averageArrDelayLGA != null) { tavgArrDelayLGA = TimeSpan.FromMinutes((double)averageArrDelayLGA); } return(new Dictionary <string, string>() { { tavgDepDelayLGA.TotalSeconds >= 0 ? tavgDepDelayLGA.ToString("hh\\:mm\\:ss") : "-" + tavgDepDelayLGA.ToString("hh\\:mm\\:ss"), tavgArrDelayLGA.TotalSeconds >= 0 ? tavgArrDelayLGA.ToString("hh\\:mm\\:ss") : "-" + tavgArrDelayLGA.ToString("hh\\:mm\\:ss") } }); }
public Dictionary <string, int> ManufacturersMoreThanTwoHundredPlanes() { var context = new Nycflights13DBContext(); return(context.Planes.Select(p => p.Manufacturer).ToList() .GroupBy(m => m).Where(m => m.Count() >= 200).ToDictionary(g => g.Key, g => g.Count())); }
public Dictionary <string, float> DailyMeanTempInCelsiusForLGA() { var context = new Nycflights13DBContext(); return(context.Weather.Where(w => !string.IsNullOrEmpty(w.Origin) && w.Origin.Equals("LGA")) .GroupBy(g => g.Time_hour.Date.ToShortDateString()).ToDictionary(p => p.Key, p => (p.Average(g => g.Temp) - 32) * 5 / 9)); }
public Dictionary <DateTime, float> TemperatureInCelsiusForJFK() { var context = new Nycflights13DBContext(); return(context.Weather.Where(w => !string.IsNullOrEmpty(w.Origin) && w.Origin.Equals("JFK")) .Select(w => new { w.Time_hour, w.Temp }).ToDictionary(g => g.Time_hour, g => (g.Temp - 32) * 5 / 9)); }
public Dictionary <string, string> MeanAirtimeForOrigins() { var context = new Nycflights13DBContext(); double?averageAirtimeJFK = context.Flights.Where(f => !string.IsNullOrEmpty(f.Origin) && f.Origin.Equals("JFK")).Select(f => f.Air_time).ToList().Average(); double?averageAirtimeEWR = context.Flights.Where(f => !string.IsNullOrEmpty(f.Origin) && f.Origin.Equals("EWR")).Select(f => f.Air_time).ToList().Average(); double?averageAirtimeLGA = context.Flights.Where(f => !string.IsNullOrEmpty(f.Origin) && f.Origin.Equals("LGA")).Select(f => f.Air_time).ToList().Average(); TimeSpan tavgAirtimeJFK = new TimeSpan(), tavgAirtimeEWR = new TimeSpan(), tavgAirtimeLGA = new TimeSpan(); if (averageAirtimeJFK != null) { tavgAirtimeJFK = TimeSpan.FromMinutes((double)averageAirtimeJFK); } if (averageAirtimeEWR != null) { tavgAirtimeEWR = TimeSpan.FromMinutes((double)averageAirtimeEWR); } if (averageAirtimeLGA != null) { tavgAirtimeLGA = TimeSpan.FromMinutes((double)averageAirtimeLGA); } return(new Dictionary <string, string>() { { "JFK", tavgAirtimeJFK.ToString("hh\\:mm\\:ss") }, { "EWR", tavgAirtimeEWR.ToString("hh\\:mm\\:ss") }, { "LGA", tavgAirtimeLGA.ToString("hh\\:mm\\:ss") } }); }
public Dictionary <string, int> PlanesforAirbus() { var context = new Nycflights13DBContext(); return(context.Planes.Where(p => !string.IsNullOrEmpty(p.Manufacturer) && p.Manufacturer.Contains("AIRBUS")) .Select(p => p.Manufacturer).GroupBy(g => g).ToDictionary(g => g.Key, g => context.Planes.Where (p => p.Manufacturer.Equals(g.Key)).Select(p => p.Tailnum).Count())); }
public Dictionary <string, int> FlightsToTopTenDestinationsFromEWR() { var context = new Nycflights13DBContext(); List <string?> topTenDestinations = context.Flights.Select(f => f.Dest).ToList().GroupBy(d => d).ToDictionary(g => g.Key, g => g.Count()) .OrderByDescending(val => val.Value).Take(10).ToDictionary(g => g.Key, g => g.Value).Keys.ToList(); Dictionary <string, int> flightsToTopTenDestinationsFromEWR = new Dictionary <string, int>(); foreach (string?dest in topTenDestinations) { flightsToTopTenDestinationsFromEWR.Add(dest, context.Flights.Where(f => !string.IsNullOrEmpty(f.Origin) && f.Origin.Equals("EWR") && !string.IsNullOrEmpty(f.Dest) && f.Dest.Equals(dest)).Count()); } return(flightsToTopTenDestinationsFromEWR); }
public Dictionary <string, int> FlightsPerMonth() { var context = new Nycflights13DBContext(); return(context.Flights.Select(f => f.Month).ToList().GroupBy(m => m).OrderBy(g => g.Key).ToDictionary(g => monthsByNumber[g.Key], g => g.Count())); }
public Dictionary <string?, int> WeatherObservationsForOrigins() { var context = new Nycflights13DBContext(); return(context.Weather.Select(w => w.Origin).ToList().GroupBy(o => o).ToDictionary(g => g.Key, g => g.Count())); }