<jupyter_start><jupyter_text>Udacity Data Engineering Capstone Project ProjectLoad, explore and clean the fact tables with Pandas.**Download the data** * Use data_utils.py functions* Find all the csv files in ../data/source folder  Libraries<jupyter_code>import os
import configparser
import pandas as pd
import numpy as np
from datetime import datetime
import glob<jupyter_output><empty_output><jupyter_text>```flights``` table<jupyter_code>input_data = "../data/source/flightlist_20201201_20201231.csv.gz"

df = pd.read_csv( input_data, compression='gzip')
df.info()
df.isna().sum()
df.head()
# process opensky data
df1 = df.copy()

# Remove rows where origin AND destination are unknown 
df1 = df1.dropna( how='all', subset=['origin', 'destination'])

col = 'destination'
df1.loc[ ~df1[col].astype(str).str.isalnum()]
df1.loc[ df1.duplicated( subset=['callsign', 'icao24', 'origin', 'destination'], keep=False)].sort_values( by=['callsign', 'icao24', 'day'])<jupyter_output><empty_output><jupyter_text>```covid_19``` table<jupyter_code>s3_input_data = 's3a://udacity-capstoneproject/source/covid-19-world-cases-deaths-testing/dataset/covid-19-world-cases-deaths-testing.csv'
input_data = '../data/source/covid-19_world_cases_deaths_testing.csv'


covid19 = pd.read_csv( input_data)
covid19
print(covid19.iso_code.unique())
print("Total number of iso_code: ", len(covid19.iso_code.unique()))
countries = pd.read_csv('../data/source/countries.csv')
print(countries.code.unique())
print("Total number of code in countries.csv: ", len(countries.code.unique()))
print(covid19.location.unique())
print("Total number of locations: ", len(covid19.location.unique()))
covid19_countries = covid19[['iso_code', 'continent', 'location']].drop_duplicates()
covid19_countries
covid19_countries.loc[covid19_countries.iso_code.str.contains('OWID')]
joined_countries = countries.join( covid19_countries.set_index('location'),
               on='name', how='left',
               lsuffix='_cts', rsuffix='_cvd')
joined_countries
joined_countries.isna().sum()
joined_countries.loc[ joined_countries.code.isna()]
cases = covid19[['iso_code', 'continent', 'location', 'date', 'total_cases', 'new_cases',
       'new_cases_smoothed', 'total_deaths', 'new_deaths',
       'new_deaths_smoothed', 'total_cases_per_million',
       'new_cases_per_million', 'new_cases_smoothed_per_million',
       'total_deaths_per_million', 'new_deaths_per_million',
       'new_deaths_smoothed_per_million']]
cases.isna().sum()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15079365079365079, 'fraction_numerical': 0.025, 'mean_word_length': 6.685975609756097, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '47479', 'license': 'MIT', 'n_tokens_mistral': 938, 'n_tokens_neox': 883, 'n_words': 151, 'path': 'notebooks/process_fact_tables.ipynb', 'repo_name': 'romaintoub/udacity-flights_covid19', 'size': '2520'}