<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Question a
Step1:  Grab the &lt;li&gt; tags
From inspecting the source code, we can see that every item is within &lt;li&gt; tags - in some cases several tags for some days like September 11, 2001.
Step2:  Handling special dates with multiple accidents
However, there are some elements in our list that contain another list of dates, which look something like this
Step3:  But then there are also separate entries for each &lt;li&gt; in the list inside
Step4:  Extracting data from the HTML
Let's take a look at one of the entries in our results containing only a list element defined by &lt;li&gt; in the page to see how we can select each piece of data we want to save. We'll store these items in lists. 
Here we show how to extract pieces of data from the html
Step5:  Iterating over all &lt;li&gt; elements and extract information
We need to write a function that will handle the fact that the date separator changes during the page.
Step6:  Let's write a function that will take each &lt;li&gt; and extract the crash details (month, day, link, and text description) from each element. We should also make sure that we handle the cases where there are several crashes per &lt;li&gt;.
Step7:  Sanity check the lengths of each list
Step8:  Looks good! Time to make the DataFrame, which we can do by passing a Python dict
Step9:  Sanity check again
Step10:  Let's check that we did everything right for the weird cases by checking one of the bullets that had multiple crashes
Step11:  This looks like exactly what we expected. Now let's proceed with clicking the links so we can add in the year. We have to click those links anyway to extract the additional crash details, so let's just grab the years from there.
Step12:  Question b
Step13:  Extracting elements from the summary page
We will write a function to extract elements from a list of table rows (defined by &lt;tr&gt;).
Step15:  Upon inspection of the text of each summary, we can see that there are some cases where in addition to (or instead of) an integer, there is some extraneous text or just a string like "all", "unknown", or "none". Let's handle these special cases and extract numbers in a function
Step16:  Scraping each page
Now let's use these functions to scrape each link.
Step17:  Let's sanity check the lengths of these lists.
Step18:  Clean up dates and format them as datetimes
The formatting of the dates is not so great, so let's just clean that up.
Step19:  Let's remove commas.
Step20:  Some dates have month first. Some dates have date first. Let's make them consistent while also getting rid of extraneous information appended to the end of the date (like links to references). We'll write our own function to parse dates because we like to do fun and cool things like that.
Step21:  We can see now that our dates are nicely formatted and can create them as datetime objects
Step22:  Optional
Step23:  Let's test with the two URLs from earlier
Step24:  Looks like we can correctly extract a summary table with multiple aircraft in it. 
Now let's try on a page that has multiple crashes in it on different days.
Step25:  Question c
Step26:  So the top 5 crashes, the number of fatalities and the flight origin was
Step27:  Let's see the description
Step28:  Question d
Step29:  Without de-duplication, Bergen Airport, Ninoy Aquino International Airport, and Domodedovo International Airport in Moscow had the highest number of aviation incidents.
Out of curiosity, let's do this for the entire dataset
Step30:  London Heathrow and LAX (entered twice as two slightly different strings) come out on top, which is not unexpected given the number of flights these airports have. 
Note that one way we could proceed with de-duplication would be to use the fact that the summary tables actually contain links to their corresponding wikipedia pages. We could link together strings that correspond to the same airport using their common link. 
Question e
<ASSISTANT_TASK:>
Python Code:
base_url = "https://en.wikipedia.org"
index_ref = "/wiki/List_of_accidents_and_incidents_involving_commercial_aircraft"
index_html = urlopen(base_url + index_ref)
index = BeautifulSoup(index_html, "lxml")
Explanation: Question a: Setting up the dataframe
End of explanation
result = index.find_all('li')
Explanation: Grab the &lt;li&gt; tags
From inspecting the source code, we can see that every item is within &lt;li&gt; tags - in some cases several tags for some days like September 11, 2001.
End of explanation
result[829]
Explanation: Handling special dates with multiple accidents
However, there are some elements in our list that contain another list of dates, which look something like this:
`<li><b><a href="/wiki/1950_Air_France_multiple_Douglas_DC-4_accidents" title="1950 Air France multiple Douglas DC-4 accidents">1950 Air France multiple Douglas DC-4 accidents</a></b>:
 
<ul>
 <li>June 12 – An Air France Douglas DC-4 (F-BBDE) on a flight from Saigon to Paris crashes in the Arabian Sea while on approach to Bahrain Airport, killing 46 of 52 on board.</li>
 <li>June 14 – An Air France Douglas DC-4, F-BBDM, crashes in the Arabian Sea while on approach to Bahrain Airport, killing 40 of 53 on board. This aircraft was operating on the same flight route as F-BBDE.</li>
 </ul>
</li>`
We'll need to make sure we can handle these cases. Additionally, since there are &lt;li&gt; tags within this block, the &lt;li&gt;June 12...&lt;/li&gt; and &lt;li&gt;June 14...&lt;/li&gt; will appear again in our ResultSet. We should get rid of these duplicates.  
Looking at the HTML above, the duplicated &lt;li&gt; entries won't have both links and date separators in them, so that's one way we can drop them. Then we'll save rows from the interior &lt;li&gt; tags, supplementing link information from the parent if necessary.
For example, the entry for September 11 is:
End of explanation
result[830:834]
Explanation: But then there are also separate entries for each &lt;li&gt; in the list inside:
End of explanation
result[0].find('a').get('href')
result[0].text
result[0].text.split(' – ')
result[0].text.split(' – ')[0]
result[0].text.split(' – ')[1]
Explanation: Extracting data from the HTML
Let's take a look at one of the entries in our results containing only a list element defined by &lt;li&gt; in the page to see how we can select each piece of data we want to save. We'll store these items in lists. 
Here we show how to extract pieces of data from the html:
End of explanation
def get_date_separator(html_fragment):
    # Date separator changes throughout the document, so let's handle both
    if ' – ' in html_fragment.text:
        return '–'
    elif ' - ' in html_fragment.text:
        return '-'
    else:
        return None
Explanation: Iterating over all &lt;li&gt; elements and extract information
We need to write a function that will handle the fact that the date separator changes during the page.
End of explanation
def extract_details(html_fragment):
    # these lists may have one or more elements when returned
    bdates, blinks, bdescrips = [], [], []
    
    if html_fragment.find_all('li') == []:
        # Then there is only one crash for this bullet
        separator = get_date_separator(html_fragment)    
        blinks.append(html_fragment.find('a').get('href'))
        bdates.append(html_fragment.text.split(separator)[0].strip())
        bdescrips.append(html_fragment.text.split(separator)[1].strip())
    else:
        # Then there are multiple crashes for this bullet
        for bullet in html_fragment.find_all('li'):
            # Dates might appear in current or parent <li>
            separator = get_date_separator(bullet)
            if separator != None:
                bdates.append(bullet.text.split(separator)[0].strip())
                bdescrips.append(bullet.text.split(separator)[1].strip())
            else:
                parent_separator = get_date_separator(html_fragment)
                bdates.append(html_fragment.text.split(parent_separator)[0].strip())
                bdescrips.append(bullet.text.strip())                
            # Relevant link might appear in current or parent <li>
            if bullet.find('a') == None:
                blinks.append(html_fragment.find('a').get('href'))
            else:
                blinks.append(bullet.find('a').get('href'))
                
    return bdates, blinks, bdescrips
dates_month_day, links, descriptions = [], [], []
for each_li in result:
    if (' – ' in each_li.text or ' - ' in each_li.text) and each_li.find('a') != None:
        lis_dates, lis_links, lis_descrips = extract_details(each_li)
        dates_month_day += lis_dates
        links += lis_links
        descriptions += lis_descrips
    else:
        # If neither condition is true, then we hit duplicate or extra links
        # elsewhere in the page so we can skip these and throw them away
        continue
Explanation: Let's write a function that will take each &lt;li&gt; and extract the crash details (month, day, link, and text description) from each element. We should also make sure that we handle the cases where there are several crashes per &lt;li&gt;.
End of explanation
len(dates_month_day), len(links), len(descriptions)
Explanation: Sanity check the lengths of each list:
End of explanation
df = pd.DataFrame({'date': dates_month_day, 'link': links, 'description': descriptions})
Explanation: Looks good! Time to make the DataFrame, which we can do by passing a Python dict:
End of explanation
df.head()
Explanation: Sanity check again:
End of explanation
df[df.date == 'September 11']
Explanation: Let's check that we did everything right for the weird cases by checking one of the bullets that had multiple crashes:
End of explanation
df.to_csv('crashes_question_starter.csv')
df[['description', 'link']].to_csv('crashes_no_extra_credit.csv')
Explanation: This looks like exactly what we expected. Now let's proceed with clicking the links so we can add in the year. We have to click those links anyway to extract the additional crash details, so let's just grab the years from there.
End of explanation
def try_request(url):
    html = urlopen(url)
    time.sleep(1)
    return BeautifulSoup(html, "lxml")
Explanation: Question b: Completing the dataframe with details
I'll rate limit my requests by using the function below:
End of explanation
def extract_summary(trs):
    date_w_year, passengers, crew, fatalities, survivors = '', 0, 0, 0, 0
    registration, origins, destination = 'No data', 'No data', 'No data'
    for each_tr in trs:
        if each_tr.find('th', text = re.compile('Destination')) != None:
            try:
                destination = each_tr.td.text
            except:
                pass
        elif each_tr.find('th', text = re.compile('Date')) != None:
            try:
                date_w_year = each_tr.td.text   
            except:
                pass
        elif each_tr.find('th', text = re.compile('Passengers')) != None:
            try:
                passengers = extract_numbers(each_tr.td.text, passengers)
            except:
                pass
        elif each_tr.find('th', text = re.compile('Crew')) != None:
            try:
                crew = extract_numbers(each_tr.td.text, passengers)
            except:
                pass
        elif each_tr.find('th', text = re.compile('Fatalities')) != None:
            try:
                fatalities = extract_numbers(each_tr.td.text, passengers)
            except:
                pass
        elif each_tr.find('th', text = re.compile('Survivors')) != None:
            try:
                survivors = extract_numbers(each_tr.td.text, passengers)
            except:
                pass
        elif each_tr.find('th', text = re.compile('Flight origin')) != None:
            try:
                origins = each_tr.td.text
            except:
                pass
        elif each_tr.find('th', text = re.compile('Registration')) != None:
            try:
                registration = each_tr.td.text
            except:
                pass
        else:
            pass
    
    return {'destination': destination,
            'date': date_w_year,
            'passengers': passengers,
            'crew': crew,
            'fatalities': fatalities,
            'survivors': survivors,
            'origins': origins,
            'registration': registration}
Explanation: Extracting elements from the summary page
We will write a function to extract elements from a list of table rows (defined by &lt;tr&gt;).
End of explanation
def extract_numbers(td_text, passengers):
    
    Function that handles table data rows to extract numbers.
    Handles special cases where there are strings like all, none, etc. in the text
    
    number_regex = re.compile('\d+')
    all_regex = re.compile('ll')
    none_regex = re.compile('one')
    unknown_regex = re.compile('nknown')
    
    try:
        data_element = int(number_regex.findall(td_text)[0])
    except:
        if len(all_regex.findall(td_text)) >= 1:
            data_element = passengers
        elif len(none_regex.findall(td_text)) >= 1:
            data_element = 0
        elif len(unknown_regex.findall(td_text)) >= 1:
            data_element = 0
        else:
            data_element = 0
    
    return data_element
Explanation: Upon inspection of the text of each summary, we can see that there are some cases where in addition to (or instead of) an integer, there is some extraneous text or just a string like "all", "unknown", or "none". Let's handle these special cases and extract numbers in a function:
End of explanation
# Define lists we use to store our results
dates_w_year, passengers, crew, fatalities, survivors = [], [], [], [], []
registration, origins, destination = [], [], []
for row in links:
    # Get HTML of detail page
    summary_html = try_request(base_url + row)
    trs = summary_html.find_all('tr')
    # Extract data from summary HTML
    summary = extract_summary(trs)
    
    # Save the data for this page in our lists
    dates_w_year.append(summary['date'])   
    passengers.append(summary['passengers'])
    crew.append(summary['crew'])
    fatalities.append(summary['fatalities'])
    survivors.append(summary['survivors'])
    origins.append(summary['origins'])
    registration.append(summary['registration'])
    destination.append(summary['destination'])    
Explanation: Scraping each page
Now let's use these functions to scrape each link.
End of explanation
len(destination), len(origins), len(registration), len(dates_w_year), len(passengers), len(crew), len(fatalities), len(survivors)
df_full = pd.DataFrame({'date': dates_w_year, 'link': links, 'description': descriptions, 'passengers': passengers,
                       'crew': crew, 'fatalities': fatalities, 'survivors': survivors,
                       'registration': registration, 'flight origin': origins, 'destination': destination})
# save all this scraped stuff!
df_full.to_csv('all_data_rescraped.csv')
df_full = pd.read_csv('all_data_rescraped.csv')
dates_w_year = df_full['date']
df_full.columns
df_full.drop(['Unnamed: 0'], axis=1, inplace=True)
Explanation: Let's sanity check the lengths of these lists.
End of explanation
dates_w_year[0:10]
Explanation: Clean up dates and format them as datetimes
The formatting of the dates is not so great, so let's just clean that up.
End of explanation
cleaned_dates = [str(d).replace(',', '') for d in dates_w_year]
Explanation: Let's remove commas.
End of explanation
import calendar
months = list(calendar.month_name)
days = list(calendar.day_name)
dates = [str(d) for d in list(range(1, 32))]
years = [str(y) for y in list(range(1900, 2017))]
def parse_date_strings(text):
    split_row = text.split()
    month, day, year, date = '', '', '', ''
    for each in split_row[0:4]:
        if each in months:
            month = each
        elif each in days:
            day = each
        elif each in years:
            year = each
        elif each in dates:
            date = each
        else:
            pass
    return {'month': month,
           'day': day,
           'year': year,
           'date': date}
def fix_dates(datecol):
    correctedcol = []
    for row in datecol:
        parsed_date = parse_date_strings(row)
        correctedcol.append('{} {} {}'.format(parsed_date['date'],
                                              parsed_date['month'],
                                              parsed_date['year']))
    return correctedcol
datescol = fix_dates(cleaned_dates)
datescol[0:5]
Explanation: Some dates have month first. Some dates have date first. Let's make them consistent while also getting rid of extraneous information appended to the end of the date (like links to references). We'll write our own function to parse dates because we like to do fun and cool things like that.
End of explanation
dates_datetime = pd.to_datetime(datescol, format='%d %B %Y', errors='coerce')
df_full['date'] = dates_datetime
df_full.head()
df_full = pd.DataFrame({'date': dates_datetime, 'link': links, 'description': descriptions, 'passengers': passengers,
                       'crew': crew, 'fatalities': fatalities, 'survivors': survivors,
                       'registration': registration, 'flight origin': origins, 'destination': destination})
# save all this scraped stuff!
df_full.to_csv('final_dataframe.csv')
Explanation: We can see now that our dates are nicely formatted and can create them as datetime objects:
End of explanation
%pdb
def extract_summaries(tables, relevant_date):
    if len(tables) == 1:
        result = extract_single_table_summary(tables[0])
    else:
        result = extract_relevant_table_summary(tables, relevant_date)
    
    return {'destination': result['destination'],
            'date': result['date'],
            'passengers': result['passengers'],
            'crew': result['crew'],
            'fatalities': result['fatalities'],
            'survivors': result['survivors'],
            'origins': result['origins'],
            'registration': result['registration']}
def pick_out_table(tables, relevant_date):
        
    for table in tables:
        trs = table.find_all('tr')
        for each_tr in trs:
            if each_tr.find('th', text = re.compile('Date')) != None:
                
                # Clean and parse date
                date = each_tr.td.text.replace(',', '')
                parsed_date = parse_date_strings(date)
                
                if (parsed_date['month'] == relevant_date.split()[0] 
                    and parsed_date['date'] == relevant_date.split()[1]):
                    return table
    return tables[0]
def extract_relevant_table_summary(tables, relevant_date):
    date_w_year, passengers, crew, fatalities, survivors = '', 0, 0, 0, 0
    registration, origins, destination = '', '', ''
    table = pick_out_table(tables, relevant_date)
    
    trs = table.find_all('tr')
        
    for each_tr in trs:
        if each_tr.find('th', text = re.compile('Destination')) != None:
            try:
                destination = each_tr.td.text
            except:
                pass
        elif each_tr.find('th', text = re.compile('Date')) != None:
            try:
                date_w_year = each_tr.td.text   
            except:
                pass
        elif each_tr.find('th', text = re.compile('Passengers')) != None:
            try:
                passengers = extract_numbers(each_tr.td.text, passengers)
            except:
                pass
        elif each_tr.find('th', text = re.compile('Crew')) != None:
            try:
                crew = extract_numbers(each_tr.td.text, passengers)
            except:
                pass
        elif each_tr.find('th', text = re.compile('Fatalities')) != None:
            try:
                fatalities = extract_numbers(each_tr.td.text, passengers)
            except:
                pass
        elif each_tr.find('th', text = re.compile('Survivors')) != None:
            try:
                survivors = extract_numbers(each_tr.td.text, passengers)
            except:
                pass
        elif each_tr.find('th', text = re.compile('Flight origin')) != None:
            try:
                origins = each_tr.td.text
            except:
                pass
        elif each_tr.find('th', text = re.compile('Registration')) != None:
            try:
                registration = each_tr.td.text
            except:
                pass
        else:
            continue
        
    return {'destination': destination.strip(),
            'date': date_w_year,
            'passengers': passengers,
            'crew': crew,
            'fatalities': fatalities,
            'survivors': survivors,
            'origins': origins.strip(),
            'registration': registration.strip()}
def extract_single_table_summary(table):
    date_w_year, passengers, crew, fatalities, survivors = '', 0, 0, 0, 0
    registration, origins, destination = '', '', ''
    trs = table.find_all('tr')
        
    for each_tr in trs:
        if each_tr.find('th', text = re.compile('Destination')) != None:
            try:
                destination += '    ' + each_tr.td.text
            except:
                pass
        elif each_tr.find('th', text = re.compile('Date')) != None:
            try:
                date_w_year = each_tr.td.text   
            except:
                pass
        elif each_tr.find('th', text = re.compile('Passengers')) != None:
            try:
                passengers += extract_numbers(each_tr.td.text, passengers)
            except:
                pass
        elif each_tr.find('th', text = re.compile('Crew')) != None:
            try:
                crew += extract_numbers(each_tr.td.text, passengers)
            except:
                pass
        elif each_tr.find('th', text = re.compile('Fatalities')) != None:
            try:
                fatalities += extract_numbers(each_tr.td.text, passengers)
            except:
                pass
        elif each_tr.find('th', text = re.compile('Survivors')) != None:
            try:
                survivors += extract_numbers(each_tr.td.text, passengers)
            except:
                pass
        elif each_tr.find('th', text = re.compile('Flight origin')) != None:
            try:
                origins += '    ' + each_tr.td.text
            except:
                pass
        elif each_tr.find('th', text = re.compile('Registration')) != None:
            try:
                registration += '    ' + each_tr.td.text
            except:
                pass
        else:
            continue
        
    return {'destination': destination.strip(),
            'date': date_w_year,
            'passengers': passengers,
            'crew': crew,
            'fatalities': fatalities,
            'survivors': survivors,
            'origins': origins.strip(),
            'registration': registration.strip()}
Explanation: Optional: Doing part b while handling the special cases where there are multiple summaries per page
I told you to ignore this for the sake of simplifying this assignment, but it is the case that there are multiple summaries on a couple pages, for example:
https://en.wikipedia.org/wiki/1950_Air_France_multiple_Douglas_DC-4_accidents
These we can separate using the dates. 
However, in addition to this, there are some cases where planes crash into one another. In these cases, the summaries are separated into two tables, one for each page, for example:
https://en.wikipedia.org/wiki/1922_Picardie_mid-air_collision
We could handle this in two ways: Sum the numbers for passengers, fatalities, etc. Or we could instead create two rows for these crashes. 
We could handle both these cases by doing the following (pseudocode):
if there are multiple tables
        get the summary details for the appropriate date
else if there is one table
        get the summary details for each summary table and sum them (this is a collision)
We will create a new function extract_summaries() that will implement this approach.
End of explanation
test_collision_url = 'https://en.wikipedia.org/wiki/1922_Picardie_mid-air_collision'
summary_html = try_request(test_collision_url)
tables = summary_html.find_all('table', {"class" : "infobox vcard vevent"})
result_updated = extract_summaries(tables)
result_updated
Explanation: Let's test with the two URLs from earlier:
End of explanation
test_multiple_dates_url = 'https://en.wikipedia.org/wiki/1950_Air_France_multiple_Douglas_DC-4_accidents'
summary_html = try_request(test_multiple_dates_url)
first_crash = 'June 12'
second_crash = 'June 14'
tables = summary_html.find_all('table', {"class" : "infobox vcard vevent"})
result_updated = extract_summaries(tables, first_crash)
result_updated
result_updated = extract_summaries(tables, second_crash)
result_updated
dates_w_year, passengers, crew, fatalities, survivors = [], [], [], [], []
registration, origins, destination = [], [], []
for num_row in range(len(links)):
    # Get HTML of detail page
    summary_html = try_request(base_url + links[num_row])
    
    # Get tables that are in these sidebars (mostly one, but sometimes multiple)
    tables = summary_html.find_all('table', {"class" : ["infobox", "vcard"]})
    # Extract data from summary HTML
    summary = extract_summaries(tables, dates_month_day[num_row])
    
    # Save the data for this page in our lists
    dates_w_year.append(summary['date'])   
    passengers.append(summary['passengers'])
    crew.append(summary['crew'])
    fatalities.append(summary['fatalities'])
    survivors.append(summary['survivors'])
    origins.append(summary['origins'])
    registration.append(summary['registration'])
    destination.append(summary['destination'])
# Clean dates
cleaned_dates = [str(d).replace(',', '') for d in dates_w_year]
datescol = fix_dates(cleaned_dates)
dates_datetime = pd.to_datetime(datescol, format='%d %B %Y', errors='coerce')
# Save!
df_summary = pd.DataFrame({'date': dates_datetime, 'link': links, 'description': descriptions, 'passengers': passengers,
                       'crew': crew, 'fatalities': fatalities, 'survivors': survivors,
                       'registration': registration, 'flight origin': origins, 'destination': destination})
# save all this scraped stuff!
df_summary.to_csv('final_dataframe_summary.csv')
Explanation: Looks like we can correctly extract a summary table with multiple aircraft in it. 
Now let's try on a page that has multiple crashes in it on different days.
End of explanation
top_5_crashes = df_full.sort_values('fatalities', ascending=False)[0:5]
Explanation: Question c: Which were the top 5 most deadly aviation incidents? Report the number of fatalities and the flight origin for each.
End of explanation
top_5_crashes[['fatalities', 'flight origin']]
Explanation: So the top 5 crashes, the number of fatalities and the flight origin was:
End of explanation
top_5_crashes['description']
Explanation: Let's see the description:
End of explanation
df_full.date[672]
recent_incidents = df_full[673:]
recent_incidents['flight origin'].value_counts()[0:5]
Explanation: Question d: Which flight origin has the highest number of aviation incidents in the last 25 years?
It's 2016, so let's take accidents from 1991 and later and see which is the most common flight origin.
This crash is the first one to occur in 1991:
End of explanation
df_full['flight origin'].value_counts()[0:10]
Explanation: Without de-duplication, Bergen Airport, Ninoy Aquino International Airport, and Domodedovo International Airport in Moscow had the highest number of aviation incidents.
Out of curiosity, let's do this for the entire dataset:
End of explanation
df_full.to_json('crashes.json')
Explanation: London Heathrow and LAX (entered twice as two slightly different strings) come out on top, which is not unexpected given the number of flights these airports have. 
Note that one way we could proceed with de-duplication would be to use the fact that the summary tables actually contain links to their corresponding wikipedia pages. We could link together strings that correspond to the same airport using their common link. 
Question e: Output as JSON
End of explanation
<END_TASK>