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()));
}
