<jupyter_start><jupyter_text>Project 1: Weather Data and Flights Data GROUP: <NAME>, <NAME>  Flights dataIn this project, we were given two data sets: flights and weather2. The first data set: flights.csv, provides us with information about airline flights in and out of all of the New York airports between January 1, 2013 and December 31, 2013. In this data set, each row is an observation about a single flight, but there are a number of derived columns that need to be cleaned up in order to transform this data set into tidy form. For the transformation process, we only kept the month, day, carrier, flight number, departure hour (originally called hour), departure minute (originally called minute), origin and destination airports, departure delay time, arrival delay time, distance and air time. The other columns such as the actual departure time and actual arrival time could all be derived from the information kept in the table. For easy exporting purposes, we set the index column to 'month'.<jupyter_code>import pandas as pd
flights = pd.read_csv("flights.csv", index_col = 'month') # import flights data set, and set month to be the index col
weather = pd.read_csv("weather2.csv")                     # import weather data set
flights_tidy = flights.loc[:,['day','carrier','flight','hour','minute','origin','dest','dep_delay','arr_delay','distance','air_time']]     
# filter out necessary columns
flights_tidy.rename(columns={flights_tidy.columns[3]: "dep_hour", flights_tidy.columns[4]: "dep_minute"},inplace=True) 
# rename the column hour and minute     
flights_tidy.head()
flights_tidy.to_csv('flights_tidy.csv')<jupyter_output><empty_output><jupyter_text>Weather dataThe second data set: weather2.csv, is data obtained from Weather Underground for weather metrics in Boston, MA over the time period ranging from January 1, 2013 and December 31, 2013, same time period as in the flights data. This data does not conform to our Tidy data constraints.By examining the original weather2 data set, we can see that for every month, the data frame is composed of columns including year, month, measure, and each day of the month. If we look closer at the matrix composed of the 19 measures and corresponding data for each day of the month, we can see that in order to make it conform to our tidy data constraints, we can simply transform the matrix and transform the events column into four boolean columns by each weather event.To transform the matrices month by month, we constructed a for loop to first select the matrix we need using iloc (indices of columns are scaled according to the number of days in each month, and indices of rows are selected for each 19 rows interval). After we transform the matrix, we now have days as a column. However, all of the days entries have an X attached to the front, so we applied a lambda function to remove the "X" to each entry in the days column. We then insert a "month" column using the value "iterator + 1" to each matrix, and concatenate the current matrix to the last transformed matrix. The codes for the above steps are shown below. (Note: the transformation of the events column is described in the next markdown cell)<jupyter_code>month_num_of_days = [31,28,31,30,31,30,31,31,30,31,30,31]    # construct a list of number of days in each month
for i in range(12):                                          # iterate through all 12 months
    weather_current = weather.iloc[19*i:19*(i+1), 3: month_num_of_days[i]+4]   # select the matrix for current month
    weather_current.index = weather_current.iloc[:,0]        # set measure to be the index column
    del weather_current['measure']                           # delete the original measure column
    weather_current = weather_current.transpose()            # transform the matrix so we have days as the index col
    weather_current.index.name = 'day'                       # change the name of index col to day
    weather_current.reset_index(inplace = True)              # reset index to pull "day" out as a single column
    weather_current.day = weather_current.day.apply(lambda n : n[1:])  # remove "X" in front of each entry in day
    weather_current.insert(0, 'month', i+1)                  # insert a month column using the value "i+1"
    weather_current = weather_current.set_index('month')     # set the created month column to be the new index col
    if i == 0:
        weather_almost_tidy = weather_current                # set final data frame to be the current one for Jan
    else:                                                    # for months Feb to Dec
        weather_almost_tidy = pd.concat([weather_almost_tidy,weather_current])
        # concatenate current data frame to the final one
weather_almost_tidy.head()<jupyter_output><empty_output><jupyter_text>Now we have arrived to a much better data frame in terms of tidiness. The only problem is that the "Events" column contains many variables (Rain, Snow, Fog and Thunderstorm), which makes the data frame still not tidy. In order to separate each variable out to have its own column, we performed the following operations. We first select out the "Events" column as a new data frame, and iterate through every row of this new data frame to split it by "-" into a list of variables. The key idea here is to evaluate the list of variables for each day (row) and create a list of boolean values in the order of "Rain, Fog, Snow, Thunderstorm", and append each row list to a larger list to form a list of lists for us to construct a new data frame, with index being days and column labels being each weather event. After we obtain the new data frame (df_events) with four boolean event columns, we then concatenate the almost tidy data frame with this new one, and delete the original "Events" column to arrive to our final data frame (weather_tidy). This final data frame now conforms to our tidy data constraints.<jupyter_code>weather_events = weather_almost_tidy[['Events']]        # take 'Events' column to construct a new data frame
df_lol = []                                             # create an empty list of lists
label = [1]*31 + [2]*28 + [3]*31 + [4]*30 + [5]*31 + [6]*30 + [7]*31 + [8]*31 + [9]*30 + [10]*31 + [11]*30 + [12]*31 # create a list of days for the entire year
col = ['Rain','Fog','Snow','Thunderstorm']              # create column labels
for lab,row in weather_events.iterrows():               # iterate through every row of the new data frame
    events = str(row[-1]).split('-')                    # split each row by "-" to give a list of event variables
    rowlist = []                                        # create an empty row list
    rowlist.append('Rain' in events)                    # append boolean values to row list
    rowlist.append('Fog' in events)
    rowlist.append('Snow' in events)
    rowlist.append('Thunderstorm' in events)
    df_lol.append(rowlist)                              # append row list to the list of lists
df_events = pd.DataFrame(df_lol, columns = col, index = label)      # create a new dataframe using lol

weather_tidy = pd.concat([weather_almost_tidy,df_events],axis=1)    # concatenate the almost tidy df with the new df
del weather_tidy['Events']                              # delete the original non-tidy 'Events' column
weather_tidy.head()
weather_tidy.to_csv('weather_tidy.csv')<jupyter_output><empty_output><jupyter_text>Weather + flights data for BOS airportIn order to investigate the correlation between weather and flights delay time, we extracted the information including day, departure delay and arrival delay for the flights that have Boston as their destination using a loc function. Since there are many observations of delay times per day, we performed a groupby operation to calculate the mean of the delay times for each day, so that we have one observation of departure delay and arrival delay per day.<jupyter_code>import numpy as np
flights_bos_by_day = flights_tidy.loc[flights_tidy.dest == 'BOS',['day','dep_delay','arr_delay']] # select useful rows and cols
flights_bos_by_day.reset_index(inplace=True)           # reset index to pull the column "month" out
flights_bos_by_day = flights_bos_by_day.groupby(by = ["month","day"]).agg(np.mean) # groupby month and day, take mean of delay times
flights_bos_by_day.reset_index(inplace=True)           # reset index to pull month and day out 
flights_bos_by_day.index = flights_bos_by_day["month"] # set month to be the index for exporting purpose
del flights_bos_by_day["month"]                        # delete original month column
flights_bos_by_day.head()
writer = pd.ExcelWriter('BOS.xlsx', engine='xlsxwriter')
weather_tidy.to_excel(writer, sheet_name='Weather')
flights_bos_by_day.to_excel(writer, sheet_name='Flights_BOS')
writer.save()      # write two sheets weather_tidy and flights_bos_by_day to an excel file<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07061733948049027, 'fraction_numerical': 0.013043967165186101, 'mean_word_length': 3.6934036939313986, 'pattern_counts': {'":': 0, '<': 19, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '391974', 'license': 'Apache-2.0', 'n_tokens_mistral': 2430, 'n_tokens_neox': 2295, 'n_words': 1115, 'path': 'Project1.ipynb', 'repo_name': 'WanyiHuo/DA301', 'size': '8898'}