<jupyter_start><jupyter_text>Week 10 Assignment  Task1 Firstly, I create a csv file includinggiven information. Then I upload the file to Github.<jupyter_code>#set up the basic package
import numpy as np
import pandas as pd
#check the data set and loaded the required file from own repository.
url="https://raw.githubusercontent.com/steinszzh/DAV-5400/master/week10/Tidying%20and%20Transforming%20Data.csv"
flight = pd.read_csv(url)
#general check for the data
flight.head()<jupyter_output><empty_output><jupyter_text>Task2 Q:how would you define a “single observation” for the data shown in the graphic?; How many key values are associated with each data value?;How many columns should your long format structure contain based on the information provided in thegraphic shown above?; What would the column headings for the long structure be?; etc. Based on the requirement, I would like to get rid of these none values firstly. Then I will rename the category to get airline and Ontime or delay column.<jupyter_code>flight.dropna(how='all',inplace=True)
#check the result
flight

# rename the columns 
flight.rename( columns={'Unnamed: 0':'Airline','Unnamed: 1':'Ontime or delay'}, inplace=True )
flight<jupyter_output><empty_output><jupyter_text>After renaming the table, melt function is used to change it to long format.<jupyter_code># Melting from wide to long format, first 2 columns are the id
ids = ["Airline","Ontime or delay"]
# the rest are values
values = flight.columns[2:]
flight = pd.melt(frame=flight,id_vars=ids, value_vars=values,var_name='City', value_name='Value')
#check data
flight.head()


#NaN value of delayed was filled by using on time value
flight['Airline'].fillna(method='ffill', inplace = True)
#check data
flight<jupyter_output><empty_output><jupyter_text>As the result shows, there is ordered dataset now.   Task3 Using your reshaped/transformed data, perform analysis to compare the arrival delays for the two airlines. Some questions you might choose to answer: For each city, which airline had the best on time performance?; Which airline had the best overall on time performance?, etc. Firstly, I need to extract data from previous data set. To get on time performance, I would like to get on time flight of each airline and the total values of flight amount to get percentage.<jupyter_code># Extract total number of flights for ontime 
flight_ontime = flight[flight['Ontime or delay']=='on time'][['Airline','City','Value']]
flight_ontime
# create a frame to contain the total number of flights
flight_ontimetotal = flight.groupby(['Airline','City']).sum()['Value'].to_frame()
flight_ontimetotal<jupyter_output><empty_output><jupyter_text>Then use merge function to combine both two data sets.<jupyter_code>#merge two d
merged_flight = pd.merge(flight_ontime, flight_ontimetotal, on=['Airline','City'],suffixes=(' ontime',' total'))
merged_flight<jupyter_output><empty_output><jupyter_text>After getting these two values, we can start to compute the percentage of ontime.<jupyter_code>merged_flight['ontime_percentage'] = ((merged_flight['Value ontime'] * 100) / merged_flight['Value total'])
merged_flight.head()<jupyter_output><empty_output><jupyter_text>As the data showed, the percentage column is generated and the sequence just required the reorder process.<jupyter_code># sorted by the city 
sorted_flight = merged_flight.sort_values('City',ascending=True)
sorted_flight<jupyter_output><empty_output><jupyter_text>For each city, ALASKA airline has better performance than AM WEST. To compare the overall performance, we need to use get total on time flight and total flight for each airlines. Then compute to get each airline's overall performance.<jupyter_code>#get total ontime number of flights for 'ALASKA'
Alaska_ontime = merged_flight[merged_flight['Airline']=='ALASKA'].sum()['Value ontime']

#get total number of flights for 'ALASKA'
Alaska_total = merged_flight[merged_flight['Airline']=='ALASKA'].sum()['Value total']


#get total ontime number of flights for 'Amwest'
Amwest_ontime = merged_flight[merged_flight['Airline']=='AM WEST'].sum()['Value ontime']

#get total number of flights for 'Amwest'
Amwest_total = merged_flight[merged_flight['Airline']=='AM WEST'].sum()['Value total']

#get overall percentage of ontime flights for 'ALASKA'
Alaska_perf=Alaska_ontime/Alaska_total

Alaska_perf
#get overall percentage of ontime flights for 'AM WEST'

Amwest_perf=Amwest_ontime/Amwest_total
Amwest_perf<jupyter_output><empty_output><jupyter_text>Interestingly, AM WEST has a better overall ontime performance even though AM WEST lose on every individual city. Also, Am West is 2% more higher than ALASKA overall performance.  Task4 Finally, given your “tidy” long format structure, consider what, if any, changes you would make to the visual presentation of the data if you were then asked to transform your “long” data back into a “wide” format: would you mimic the structure of the graphic shown above? If not, how might you transform your “long” data to “wide” format to make its “wide” presentation easier to understand and work with? Provide an example of your recommendation. Only when I want to analyze the performance of a specific city, I may change it back. I would like to use pivot function to transform 'long' to 'wide' format. Then use unstack function to make it look nice.<jupyter_code># create a pivot table by mimic the origianl format 
flight_pivot= pd.pivot_table(flight,values='Value',index=['Airline','Ontime or delay'],columns=['City'])
flight_pivot

#use unstack function to unstack wide

flight_wide = pd.DataFrame(flight_pivot.unstack())
#get wide
flight_wide<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup