<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Flight delays (capstone project)
Summary of data wrangling process
1. Original files (raw data)
1.1 - CSV with data of the flights
Step1:  Now, it is time to covert dates from 'object' to 'date' format
Step2:  Status of the flight
Step3:  The result, so far
Step4:  Some EDA (tests)
Step5:  2.2 - Merge Flights with local airports (list with all the ~600 brazilian public airports) and identify the local flights
The local airports dataset is not a sample. This CSV file contains all 589 brazilian public airports. There are other airports in Brazil but, in this analysis, only the public airports will be considered.
Step6:  --- Here I got stuck ---
Step7:  Moving on...
2.3 - The Weather data from https
<ASSISTANT_TASK:>
Python Code:
from datetime import datetime
# Pandas and NumPy
import pandas as pd
import numpy as np
# Read the CSV with flights records (separation = ";")
flights = pd.read_csv('data/arfsample-date.csv', sep = ';', dtype = str)
flights.head()
# Lambda function
# 1 - Used to adjust date columns to ISO format
# flights['column-with-date'] = flights[['column-with-date']].apply(lambda row: \
#                                                      row.str.replace("(?P<day>\d{2})/\
#                                                                       (?P<month>\d{2})/\
#                                                                       (?P<year>\d{4}) \
#                                                                       (?P<HOUR>\d{2}):\
#                                                                       (?P<MIN>\d{2})", \
#                                                                      "\g<year>/\g<month>/\g<day> \g<HOUR>:\g<MIN>:00"), axis=1)
#
# 2- Used to translate to english the 'flight-status': 
# - 'REALIZADO'-> 'ACCOMPLISHED'
# - 'CANCELADO'-> 'CANCELED'
# ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
flights['departure-est'] = flights[['departure-est']].apply(lambda row: row.str.replace("(?P<day>\d{2})/(?P<month>\d{2})/(?P<year>\d{4}) (?P<HOUR>\d{2}):(?P<MIN>\d{2})", "\g<year>/\g<month>/\g<day> \g<HOUR>:\g<MIN>:00"), axis=1)
flights['departure-real'] = flights[['departure-real']].apply(lambda row: row.str.replace("(?P<day>\d{2})/(?P<month>\d{2})/(?P<year>\d{4}) (?P<HOUR>\d{2}):(?P<MIN>\d{2})", "\g<year>/\g<month>/\g<day> \g<HOUR>:\g<MIN>:00"), axis=1)
flights['arrival-est'] = flights[['arrival-est']].apply(lambda row: row.str.replace("(?P<day>\d{2})/(?P<month>\d{2})/(?P<year>\d{4}) (?P<HOUR>\d{2}):(?P<MIN>\d{2})", "\g<year>/\g<month>/\g<day> \g<HOUR>:\g<MIN>:00"), axis=1)
flights['arrival-real'] = flights[['arrival-real']].apply(lambda row: row.str.replace("(?P<day>\d{2})/(?P<month>\d{2})/(?P<year>\d{4}) (?P<HOUR>\d{2}):(?P<MIN>\d{2})", "\g<year>/\g<month>/\g<day> \g<HOUR>:\g<MIN>:00"), axis=1)
flights.dtypes
Explanation: Flight delays (capstone project)
Summary of data wrangling process
1. Original files (raw data)
1.1 - CSV with data of the flights: monthly-basis departures and arrivals
These raw data files are available in CSV format at website of brazilian Federal Agency for Civil Aviation (ANAC):
http://www.anac.gov.br/assuntos/dados-e-estatisticas/historico-de-voos. Last access: nov, 2017.
These files has theses information:
1.2 - Local airports (list with all the ~600 brazilian public airports)
A XLSX spreadsheet is available, also, at ANAC's website:
Link: http://www.anac.gov.br/assuntos/setor-regulado/aerodromos/cadastro-de-aerodromos/aerodromos-cadastrados/aerodromospublicos-12.xls. Last access on nov, 15th, 2017 ("aerodromospublicos-12.xls")
I opened this file with MS Excel and saved it as CSV. The columns of this file:
1.3 - Weather data (historical data)
I could not find any local dataset with easy-to-retrieve and open format weather historical data. So I grabbed the weather historical data from Weather Underground website. 
A example with 2016 weather data from Campinas, SP, Brazil (my city):
https://www.wunderground.com/history/airport/SBKP/2016/1/1/CustomHistory.html?dayend=31&monthend=12&yearend=2016&req_city=&req_state=&req_statename=&reqdb.zip=&reqdb.magic=&reqdb.wmo=
I Ctrl-C the data section and Ctrl-V on a Google spreadsheet. I moved the last column that shows the "Weather Events" (Rain, Thunderstorms etc.)  and these data are presented like this:
1.4 - List of codes (two letters) used when there was a flight delay (departure)
A XLS file with two-letters justification code that is used by the aviation crew to "explain" the delay of a flight:
Link from ANAC's website: http://www.anac.gov.br/assuntos/dados-e-estatisticas/vra/glossario_de_justificativas.xls. Last access: nov, 20th, 2017.
These codes are used in the CSV with data of the flights (see section 1.1, column "Delay Code").
2. Adjusting some data
In order to start the analysis it was necessary to adjust some data from these files. 
- In the flights dataset, for instance, we'd found date/time in a not ISO format (yyyy/mm/dd HH:MM:SS);
- The analysis will consider only the local flights: airport_A and airport_B in Brazil. It is necessary, then, to identify which airports (A or B) are in Brazil. A new column 'local_flight' will be added to flights dataset with values '1' (local flight) or '0' (A or B are overseas). This information will come as a result of a merge of the flight  dataset with brazilian_airport dataset: if both 'airpot_A' and 'airport_B' are in brazilian_airport, 'local_flight' = '1', otherwise '0'.
- There was relevant info in portuguese, so, for clarification of this analysis, I tried to translate some of these information
- I translated to english the file header (columns' names)
2.1 - Flights
Adjust the date format to ISO, status of the flight, identify local (brazilian) airports
In order to demonstrate this I create a sample file from original dataset with 20 records: "arfsample-date.csv".
End of explanation
# Departure and Arrival columns: from 'object' to 'date' format
flights['departure-est'] = pd.to_datetime(flights['departure-est'], errors='ignore')
flights['departure-real'] = pd.to_datetime(flights['departure-real'], errors='ignore')
flights['arrival-est'] = pd.to_datetime(flights['arrival-est'], errors='ignore')
flights['arrival-real'] = pd.to_datetime(flights['arrival-real'], errors='ignore')
flights.dtypes
Explanation: Now, it is time to covert dates from 'object' to 'date' format
End of explanation
# translate the flight status from portuguese to english
flights['flight-status'] = flights[['flight-status']].apply(lambda row: row.str.replace("REALIZADO", "ACCOMPLISHED"), axis=1)
flights['flight-status'] = flights[['flight-status']].apply(lambda row: row.str.replace("CANCELADO", "CANCELED"), axis=1)
Explanation: Status of the flight: The content of 'status_flight' will be translated to english:
End of explanation
flights[['departure-est', 'departure-real', 'arrival-est', 'arrival-real', 'flight-status']].head()
Explanation: The result, so far:
End of explanation
flights.groupby(['airport-A']).size().reset_index(name='number_departures')
Explanation: Some EDA (tests)
End of explanation
# Airports dataset: all brazilian public airports (updated until october, 2017)
airports = pd.read_csv('data/brazilianPublicAirports-out2017.csv', sep = ';')
airports.head()
# Merge "flights" dataset with "airports" in order to identify 
#   local flights (origin and destination are in Brazil)
flights = pd.merge(flights, airports, left_on="airport-A", right_on="airport", how='left')
flights = pd.merge(flights, airports, left_on="airport-B", right_on="airport", how='left')
flights.head()
Explanation: 2.2 - Merge Flights with local airports (list with all the ~600 brazilian public airports) and identify the local flights
The local airports dataset is not a sample. This CSV file contains all 589 brazilian public airports. There are other airports in Brazil but, in this analysis, only the public airports will be considered.
End of explanation
# Create a new column 'local_flight' with '0' value as default
flights["local_flight"] = 0
flights.airport_x = flights.airport_x.fillna('')
flights.airport_y = flights.airport_y.fillna('')
flights[["airport_x", "airport_y", "local_flight"]]
for index,row in flights.iterrows():
    if ((row['airport_x'] != '') & (row['airport_y'] != '')):
        flights['local_flight'] = 1
flights[['airport_x', 'airport_y', 'local_flight']]
Explanation: --- Here I got stuck ---:
1) After the merge of 'flights' and 'airports' datasets,
2) I created a new columun: 'local_flight';
3) I had tried to define a value (int or boolean) to this column, based on the content of columns 'airport_x' (departure) and 'airport_y' (arrival): if there is information in both columns, it means that we have a local flight record (A and B are brazilian airports)
End of explanation
# Weather sample: load the CSV with weather historical data (from Campinas, SP, Brazil, 2016)
weather = pd.read_csv('data/DataScience-Intensive-weatherAtCampinasAirport-2016-Campinas_Airport_2016Weather.csv', \
                      sep = ',', dtype = str)
weather.head()
weather.dtypes
weather["date"] = weather["year"].map(str) + "-" + weather["month"].map(str) + "-" + weather["day"].map(str) 
weather["date"] = pd.to_datetime(weather['date'],errors='ignore')
weather.dtypes
Explanation: Moving on...
2.3 - The Weather data from https://www.wunderground.com/history
From this website I captured a sample data from local airport (Campinas, SP, Brazil) during the year of 2016.
The website presents data like this (see https://goo.gl/SqKfmP):
I performed the following steps in Google Spreadsheet:
I copied (Ctr+C) this data and I created a Google spreadsheet (Ctr+V) with this data (see section 1.3 above);
I "moved" the column that shows weather special events (thunderstorms, rain etc.) and I positioned just before de "Temperature" column;
In order to handle the date information I add the correct year and month to each row.
The result is this CSV (see https://docs.google.com/spreadsheets/d/1-AYWwt4coeJHhq4RSKwYTIXdov4MHxraQb7-ayPCaK4/edit?usp=sharing):
End of explanation
<END_TASK>