<jupyter_start><jupyter_text>Network, Graph and Graph Database  Prepare the dataset<jupyter_code># https://zenodo.org/record/4670228

TEST_DATA_FOLDER = "test_data/flights"

!mkdir -p $TEST_DATA_FOLDER

# Uncomment the dataset to download
#!curl https://zenodo.org/record/4670228/files/flightlist_20191201_20191231.csv.gz -o {TEST_DATA_FOLDER}/flightlist_20191201_20191231.csv.gz
#!curl https://zenodo.org/record/4670228/files/flightlist_20210301_20210331.csv.gz -o {TEST_DATA_FOLDER}/flightlist_20210301_20210331.csv.gz 
!curl https://zenodo.org/record/4670228/files/flightlist_20201201_20201231.csv.gz -o {TEST_DATA_FOLDER}/flightlist_20201201_20201231.csv.gz

#!gunzip {TEST_DATA_FOLDER}/flightlist_20191201_20191231.csv.gz
#!gunzip {TEST_DATA_FOLDER}/flightlist_20210301_20210331.csv.gz 
!gunzip {TEST_DATA_FOLDER}/flightlist_20201201_20201231.csv.gz 

!ls -lh {TEST_DATA_FOLDER}<jupyter_output>% Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  126M  100  126M    0     0  2963k      0  0:00:43  0:00:43 --:--:-- 2579k  103M    0     0  3128k      0  0:00:41  0:00:33  0:00:08 2124k
total 2649216
-rw-r--r--  1 mengwangk  staff   515M May  2 12:11 flightlist_20191201_20191231.csv
-rw-r--r--  1 mengwangk  staff   361M May  2 22:09 flightlist_20201201_20201231.csv
-rw-r--r--  1 mengwangk  staff   394M May  2 12:12 flightlist_20210301_20210331.csv<jupyter_text>Data Cleansing<jupyter_code># Uncomment to install the dependency
# Refer below for more details
# - https://scitools.org.uk/cartopy/docs/latest/installing.html#installing
# - https://networkx.org/documentation/latest/install.html

# References for maps
# - https://rabernat.github.io/research_computing_2018/maps-with-cartopy.html
# - https://semba-blog.netlify.app/07/04/2020/mapping-with-cartopy-in-python/
# - https://ipython-books.github.io/142-drawing-flight-routes-with-networkx/

# !conda install -c conda-forge cartopy networkx -y
import math
import json
import numpy as np
import pandas as pd
import networkx as nx
import cartopy.crs as ccrs
import matplotlib.pyplot as plt
from IPython.display import Image
%matplotlib inline
FLIGHT_DEC_2019 = f"{TEST_DATA_FOLDER}/flightlist_20191201_20191231.csv"
FLIGHT_DEC_2020 = f"{TEST_DATA_FOLDER}/flightlist_20201201_20201231.csv"
FLIGHT_MAR_2021 = f"{TEST_DATA_FOLDER}/flightlist_20210301_20210331.csv"
#df = pd.read_csv(FLIGHT_DEC_2019, low_memory=False)
df = pd.read_csv(FLIGHT_MAR_2021, low_memory=False)
#df = pd.read_csv(FLIGHT_DEC_2020, low_memory=False)
len(df)
df.head(10)
df.columns
df.dropna(subset = ["origin", "destination"], inplace=True)
len(df)
df[['altitude_1', 'altitude_2']] = df[['altitude_1','altitude_2']].fillna(value=0)
df = df[["origin", "destination", "day", "latitude_1", "longitude_1", "altitude_1", "latitude_2", "longitude_2", "altitude_2"]]
df.head(10)<jupyter_output><empty_output><jupyter_text>Network Visualization<jupyter_code>edges = df[['origin', 'destination']].values
len(edges), edges
g = nx.from_edgelist(edges)
len(g.nodes()), len(g.edges())
#fig, ax = plt.subplots(1, 1, figsize=(6, 6))
#nx.draw_networkx(g, ax=ax, node_size=5,
#                 font_size=6, alpha=.5,
#                 width=.5)
#ax.set_axis_off()
sg = next(g.subgraph(c) for c in nx.connected_components(g))
#fig, ax = plt.subplots(1, 1, figsize=(6, 6))
#nx.draw_networkx(sg, ax=ax, with_labels=False,
#                  node_size=5, width=.5)
#ax.set_axis_off()
# Airport with latitude and longitude

airports = {}
altitudes = {}

for index, row in df.iterrows():
    if not row['origin'] in airports:
        airports[row['origin']] = (row['longitude_1'], row['latitude_1'])
        altitudes[row['origin']] = row['altitude_1']
    if not row['destination'] in airports:
        airports[row['destination']] = (row['longitude_2'], row['latitude_2'])
        altitudes[row['destination']] = row['altitude_2']
             
len(airports), len(altitudes)
deg = nx.degree(sg)
sizes = [5 * deg[code] for code in sg.nodes]
altitudes = [altitudes[code] for code in sg.nodes]
len(altitudes)
labels = {code: code if deg[code] >= 20 else '' for code in sg.nodes}
# Map projection
crs = ccrs.PlateCarree(central_longitude=0)
fig, ax = plt.subplots(
    1, 1, figsize=(20, 14),
    subplot_kw=dict(projection=crs))
ax.coastlines()
ax.set_global()
nx.draw_networkx(sg, ax=ax,
                 font_size=16,
                 alpha=.5,
                 width=.075,
                 node_size=sizes,
                 labels=labels,
                 pos=airports,
                 node_color=altitudes,
                 cmap=plt.cm.autumn)
sorted(g.degree, key=lambda x: x[1], reverse=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.134850400337126, 'fraction_numerical': 0.09587020648967552, 'mean_word_length': 4.071581196581197, 'pattern_counts': {'":': 0, '<': 12, '<?xml version=': 0, '>': 13, 'https://': 9, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '150762', 'license': 'MIT', 'n_tokens_mistral': 2107, 'n_tokens_neox': 1802, 'n_words': 327, 'path': 'nbs/10_network_and_graph.ipynb', 'repo_name': 'stordopoulos/jupyter-notebooks', 'size': '4746'}