<jupyter_start><jupyter_text>Read source data from CSV files into Panda data frames<jupyter_code>import pandas
pandas.__version__<jupyter_output><empty_output><jupyter_text>--> Data courtesy of <NAME> from https://github.com/krlawrence/graph/raw/master/sample-data/<jupyter_code>dfp_nodes=pandas.read_csv('https://github.com/krlawrence/graph/raw/master/sample-data/air-routes-latest-nodes.csv')
dfp_edges=pandas.read_csv('https://github.com/krlawrence/graph/raw/master/sample-data/air-routes-latest-edges.csv')
print('Size of nodes dataframe: {}'.format(dfp_nodes.shape))
print('Size of edges dataframe: {}'.format(dfp_edges.shape))<jupyter_output>Size of nodes dataframe: (3742, 16)
Size of edges dataframe: (57574, 5)<jupyter_text>Create DFP with airports only<jupyter_code>dfp_nodes.dtypes
dfp_nodes.groupby('~label').size()
dfp_ports=(
           dfp_nodes[dfp_nodes['~label'].isin(['airport'])]
           .drop(['~label','type:string','author:string','date:string'], axis=1)
           .convert_dtypes()
          )
dfp_ports.columns=(dfp_ports.columns
                   .str.replace('~','')
                   .str.replace(':.*','')
                   .str.upper()
                  )
dfp_ports.dtypes<jupyter_output><empty_output><jupyter_text>Create DFP with routes only<jupyter_code>dfp_edges.dtypes
dfp_edges.groupby('~label').size()
dfp_routes=dfp_edges[dfp_edges['~label'].isin(['route'])].drop(['~label'], axis=1).copy()
dfp_routes.columns=dfp_routes.columns.str.replace('~','').str.replace(':.*','').str.upper()
dfp_routes.dtypes<jupyter_output><empty_output><jupyter_text>Persist in SAP HANA Cloud<jupyter_code>import hana_ml
hana_ml.__version__
hana_cloud_endpoint="8e1a286a-21d7-404d-8d7a-8c77d2a77050.hana.trial-eu10.hanacloud.ondemand.com:443"
hana_cloud_host, hana_cloud_port=hana_cloud_endpoint.split(":")

cchc=hana_ml.dataframe.ConnectionContext(port=hana_cloud_port,
                                         address=hana_cloud_host,
                                         user='HANAML',
                                         password='<PASSWORD>!',
                                         encrypt=True
                                        )
print(cchc.sql("SELECT SCHEMA_NAME, TABLE_NAME FROM TABLES WHERE SCHEMA_NAME='{schema_name}'"
                 .format(schema_name=cchc.get_current_schema()))
        .collect()
       )
dfh_ports=hana_ml.dataframe.create_dataframe_from_pandas(cchc, 
                                                         dfp_ports, "PORTS", 
                                                         force=True
                                                        )
dfh_routes=hana_ml.dataframe.create_dataframe_from_pandas(cchc, 
                                                          dfp_routes, 'ROUTES',
                                                          force=True)
print(cchc.sql("SELECT SCHEMA_NAME, TABLE_NAME FROM TABLES WHERE SCHEMA_NAME='{schema_name}'"
                 .format(schema_name=cchc.get_current_schema()))
        .collect()
       )
print(dfh_ports.collect())
display(dfh_ports.head(5).collect())
dfh_ports
dfh_ports.select_statement
dfh_ports.head(5)
dfh_ports.head(5).select_statement<jupyter_output><empty_output><jupyter_text>Data exploration<jupyter_code>print(dfh_ports.columns)
(
    dfh_ports
    .select("CODE", "DESC", "LONGEST", "COUNTRY", "CITY")
    .sort("LONGEST", desc=True)
    .head(3).collect()
)
(
    dfh_ports
    .select("CODE", "DESC", "LONGEST", "COUNTRY", "CITY")
    .sort("LONGEST", desc=True)
    .head(3)
    .select_statement
)
(
    dfh_ports
    .select(
        "CODE", "DESC", "LONGEST", "COUNTRY", "CITY", "LAT", 
        ('ABS("LAT")', "ABSLAT")
    )
    .sort("ABSLAT", desc=True).head(3)
    .collect()
)
(
    dfh_ports
    .agg(
        agg_list=[("max", "RUNWAYS", "MAXRUNWAYS")], 
        group_by="COUNTRY"
    )
    .sort("MAXRUNWAYS", desc=True)
    .head(7).collect()
)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1441601216113504, 'fraction_numerical': 0.012161135039270332, 'mean_word_length': 2.6454293628808863, 'pattern_counts': {'":': 1, '<': 26, '<?xml version=': 0, '>': 27, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 19, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '321761', 'license': 'Apache-2.0', 'n_tokens_mistral': 1377, 'n_tokens_neox': 1338, 'n_words': 153, 'path': 'Python-API/usecase-examples/multimodel-analysis-airroutes/01 Dataframes.ipynb', 'repo_name': 'pbaumann76/hana-ml-samples', 'size': '3957'}