Name: US 2023 Civil Flights, Delays, Meteo And Aircrafts
Creator: Kaggle
License: https://creativecommons.org/publicdomain/zero/1.0/

About this Dataset

US 2023 Civil Flights, Delays, Meteo And Aircrafts

I offer you a complete dataset of US civil aviation 2023 containing :

departure and arrival delays for each flight
descriptions and delay times
weather with rain, snow, pressure, temperature min/max/avg, wind speed and direction for each airport
associated airlines
information for each plane with manufacturer, model and age.

The information on flights comes from the BTS (Bureau of Transportation Statistics) website and I used the Meteostat Python library to scrape the information. I also developed a correspondence table for the airports, their identifiers and geolocation coordinates.

TABLES AND JUNCTIONS DESCRIPTION

US_flights_2023.csv

Main table containing all flight information except weather. Cancelled or diverted flights have been extracted in a dedicated table to avoid biasing delay statistics and analysis.

weather_meteo_by_airport.csv

Table containing weather data (temperatures, air pressure, snow cover, precipitation, wind strength and direction) for each airport and day of the year. This table can be joined to the main table, using either the departure or arrival airport ID, or both, to study in-flight variations.

Cancelled_Diverted_2023.csv

Table des annulations et vols déviés pour une analyse dédiée. Il est possible de créer une jointure avec la table météo pour identifier les causes probables.

Here's my dashboard associated with the analysis of civil aviation delays in the United States in 2023. You'll find some interesting indicators and an observation on the various correlations between delays. This dashboard was designed on a student version of Power Bi, with pre-processing in Python and Pandas. Thanks for your feedback.

CIVIL AVIATION US CONTEXT

COMPAGNIES DELAYS

LOCALIZING DELAYS

ANALYSIS OF CORRELATIONS

CAPACITY TO ABSORB DEPARTURE DELAYS

Thank you for your feedback

Tables

Airports Geolocation

@kaggle.bordanova_2023_us_civil_flights_delay_meteo_and_aircraft.airports_geolocation

24.91 kB
364 rows
7 columns

CREATE TABLE airports_geolocation (
  "iata_code" VARCHAR,
  "airport" VARCHAR,
  "city" VARCHAR,
  "state" VARCHAR,
  "country" VARCHAR,
  "latitude" DOUBLE,
  "longitude" DOUBLE
);

Cancelled Diverted 2023

@kaggle.bordanova_2023_us_civil_flights_delay_meteo_and_aircraft.cancelled_diverted_2023

907.95 kB
104,488 rows
23 columns

CREATE TABLE cancelled_diverted_2023 (
  "flightdate" TIMESTAMP,
  "day_of_week" BIGINT,
  "airline" VARCHAR,
  "tail_number" VARCHAR,
  "cancelled" DOUBLE,
  "diverted" DOUBLE,
  "dep_airport" VARCHAR,
  "dep_cityname" VARCHAR,
  "deptime_label" VARCHAR,
  "dep_delay" DOUBLE,
  "dep_delay_tag" BIGINT,
  "dep_delay_type" VARCHAR,
  "arr_airport" VARCHAR,
  "arr_cityname" VARCHAR,
  "arr_delay" DOUBLE,
  "arr_delay_type" VARCHAR,
  "flight_duration" DOUBLE,
  "distance_type" VARCHAR,
  "delay_carrier" DOUBLE,
  "delay_weather" DOUBLE,
  "delay_nas" DOUBLE,
  "delay_security" DOUBLE,
  "delay_lastaircraft" DOUBLE
);

Maj Us Flight January 2024

@kaggle.bordanova_2023_us_civil_flights_delay_meteo_and_aircraft.maj_us_flight_january_2024

5.44 MB
527,197 rows
24 columns

CREATE TABLE maj_us_flight_january_2024 (
  "flightdate" TIMESTAMP,
  "day_of_week" BIGINT,
  "airline" VARCHAR,
  "tail_number" VARCHAR,
  "dep_airport" VARCHAR,
  "dep_cityname" VARCHAR,
  "deptime_label" VARCHAR,
  "dep_delay" BIGINT,
  "dep_delay_tag" BIGINT,
  "dep_delay_type" VARCHAR,
  "arr_airport" VARCHAR,
  "arr_cityname" VARCHAR,
  "arr_delay" BIGINT,
  "arr_delay_type" VARCHAR,
  "flight_duration" BIGINT,
  "distance_type" VARCHAR,
  "delay_carrier" BIGINT,
  "delay_weather" BIGINT,
  "delay_nas" BIGINT,
  "delay_security" BIGINT,
  "delay_lastaircraft" BIGINT,
  "manufacturer" VARCHAR,
  "model" VARCHAR,
  "aicraft_age" BIGINT
);

Us Flights 2023

@kaggle.bordanova_2023_us_civil_flights_delay_meteo_and_aircraft.us_flights_2023

67.79 MB
6,743,404 rows
24 columns

CREATE TABLE us_flights_2023 (
  "flightdate" TIMESTAMP,
  "day_of_week" BIGINT,
  "airline" VARCHAR,
  "tail_number" VARCHAR,
  "dep_airport" VARCHAR,
  "dep_cityname" VARCHAR,
  "deptime_label" VARCHAR,
  "dep_delay" BIGINT,
  "dep_delay_tag" BIGINT,
  "dep_delay_type" VARCHAR,
  "arr_airport" VARCHAR,
  "arr_cityname" VARCHAR,
  "arr_delay" BIGINT,
  "arr_delay_type" VARCHAR,
  "flight_duration" BIGINT,
  "distance_type" VARCHAR,
  "delay_carrier" BIGINT,
  "delay_weather" BIGINT,
  "delay_nas" BIGINT,
  "delay_security" BIGINT,
  "delay_lastaircraft" BIGINT,
  "manufacturer" VARCHAR,
  "model" VARCHAR,
  "aicraft_age" BIGINT
);

Weather Meteo By Airport

@kaggle.bordanova_2023_us_civil_flights_delay_meteo_and_aircraft.weather_meteo_by_airport

1.16 MB
132,860 rows
10 columns

CREATE TABLE weather_meteo_by_airport (
  "time" TIMESTAMP,
  "tavg" DOUBLE,
  "tmin" DOUBLE,
  "tmax" DOUBLE,
  "prcp" DOUBLE,
  "snow" DOUBLE,
  "wdir" DOUBLE,
  "wspd" DOUBLE,
  "pres" DOUBLE,
  "airport_id" VARCHAR
);