Data Extraction & Integrity Constraints

data = {'State': ['Ohio', 'Ohio', 'Ohio', 'Nevada', 'Nevada', 'Nevada'], 
          'Year': [2000, 2001, 2002, 2001, 2002, 2003],
          'Population': [1.5, 1.7, 3.6, 2.4, 2.9, 3.2]}
df = pd.DataFrame(data)
df        
        

data = [['Ohio', 2000, 1.5], ['Ohio', 2001, 1.7], ['Ohio', 2002, 3.6],
        ['Nevada', 2001, 2.4],['Nevada', 2002, 2.9], ['Nevada', 2003, 3.2]]
cols = ['State', 'Year', 'Population']
df = pd.DataFrame(data, columns = cols)
df        
        

print("number of columns = ", len(df.columns))
print("number of rows = ", len(df))

df = pd.read_csv( filepath_or_buffer = 'sample_data/california_housing_train.csv', 
    delimiter = ',', doublequote  = True,
    quotechar  = '"',na_values = ['na', '-', '.', ''],
    quoting =  csv.QUOTE_ALL, encoding = "ISO-8859-1")
df

You may also use your own dataset or the movies dataset that can be downloaded from this LINK.

df.info()    # index & data types
n = 4
dfh = df.head(n)            # get first n rows 
dft = df.tail(n)             # get last n rows
top_left_corner_df = df.iloc[:5, :5]
        

col_set = df.iloc[:, 5:9]
col_set    

This code extracts columns 6, 7, 8, 9 assuming the first column is numbered 1 not as the python index 0.

For columns that are not in the same range, we separate the columns indexes with a comma.

df.loc[(df['total_rooms'] > 5000).values, 
        ['longitude', 'latitude', 'total_rooms',  'median_house_value']]

Another way:

df.loc[(df['total_rooms'] > 5000).values, [0, 1, 3, 8]]

The following function receives the name of the database file as input and returns a connector to the database.

def create_connection( db_file):
    """ create a database connection to the SQLite database specified by the db_file 
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
    except Error as e:
        print(e)
    return conn

Sending a query to the database and receiving the resulting relation can be done through the following function, which receives a connector and a query and returns the results after running the query.

 def run_query(conn, query):
    """
    Query all rows in the teams table
    :param conn: the Connection object
    :return: the results of executing the query
    """
     # Create a cursor 
    cur = conn.cursor()    
    # Send the query to the database
    cur.execute(query)
    # Extract the results of the query
    results = cur.fetchall()
    # Return the results 
    return results
        

The following function receives a connector and table name, then it sends two queries to get the names of the columns and the data. After receiving the query output, it creates a Pandas dataframe and returns it.

 def convert_db_table_to_DF(conn, table):):
    # get the names of the attributes in the database table
    header_query = "SELECT name FROM pragma_table_info('" + table + "') ORDER BY cid;"
    cols_init = run_query(conn, header_query)
    cols = [cols_init[i][0] for i in range(len(cols_init))]
    # get the records of the table
    content_query = "Select * FROM " + table
    data = run_query(conn, content_query)
    df = pd.DataFrame(data, columns = cols)
    return df 

Querying the database to get the names of the tables in the database.

database = "sample_data/football.db"
# create a database connection
conn = create_connection(database)    
with conn:
    query = 'SELECT name FROM sqlite_schema WHERE type =  "table"'
    data = run_query(conn, query)
    print(data) 

Write your own query

'''
TODO: Write the code to read the data in the game table, store it in 
a DataFrame and display the contents of the DataFrame.
'''  

TODO: read the contents of the files california_housing_train.csv and california_housing_test.csv and store them in df_train and df_test. Find the union the two dataframes and store the results in a DataFrame `df_cal_housing.

TODO: connect to the football database and read the data in the goals and game relations. Store their contents in two DataFrames df_goal and df_game and join them into df_goal_game dataframe.