In this lesson, you'll synthesize many of your data loading skills you learned to date in order to merge multiple datasets from various sources as part of an ETL process.

You will be able to:

• Describe the ETL process and the steps it consists of
• Perform an ETL process with multiple tables and create a single dataset

Recall, that ETL refers to the Extract, Transform, Load procedure for obtaining data that you can work with. Here, you'll reinforce your ETL skills by creating a single dataset from multiple data tables.

Start extracting data with the code cells below!

import sqlite3
import pandas as pd

# Create a connection
con = sqlite3.connect('data.sqlite')
# Create a cursor
cur = con.cursor()
# Select some data
cur.execute("""SELECT * FROM orders JOIN orderdetails USING(orderNumber);""")
df = pd.DataFrame(cur.fetchall())
df.columns = [i[0] for i in cur.description]
print(df.shape)
df.head()

(2996, 11)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

# Create a connection
con = sqlite3.connect('data.sqlite')
# Create a cursor
cur = con.cursor()
# Select some data
cur.execute("""SELECT * FROM products;""")
df = pd.DataFrame(cur.fetchall())
df.columns = [i[0] for i in cur.description]
print(df.shape)
df.head()

(110, 9)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

Recall, that you can also join data from multiple tables in SQL.

# Create a connection
con = sqlite3.connect('data.sqlite')
# Create a cursor
cur = con.cursor()
# Select some data
cur.execute("""SELECT * FROM products
                        JOIN orderdetails
                        USING (productCode);""")
df = pd.DataFrame(cur.fetchall())
df.columns = [i[0] for i in cur.description]
print(df.shape)
df.head()

(2996, 13)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

You can also merge data from a separate csv file. For example, say you take a separate data source regarding daily sales data for the various branches. You might first generate a view from our database:

# Create a connection
con = sqlite3.connect('data.sqlite')
# Create a cursor
cur = con.cursor()
# Select some data
cur.execute("""SELECT * FROM customers
                        JOIN orders
                        USING(customerNumber);""")
df = pd.DataFrame(cur.fetchall())
df.columns = [i[0] for i in cur.description]
print(df.shape)
df.head()

(326, 19)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

And then load the seperate datefile:

daily_sums = pd.read_csv('Daily_Sales_Summaries.csv')
daily_sums.head()

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

merged = pd.merge(df, daily_sums)

It's always good practice to check assumptions and preview transformed data views throughout your process. Let's take a look:

merged.head()

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

Pandas' merge() function conveniently uses common column names between the DataFrames as keys. You can always specify what columns to join on by using the on keyword as in pd.merge(df1, df2, on=[col1, col2]). Unfortunately, columns that are not identically named beforehand will not work with this method. Additionally, it is imperative to check the formatting of the join keys between the tables. A number formatted as a string can often ruin joins, and separate formatting conventions such as 'U.S.' versus 'USA' are also important preprocessing considerations before merging data files from various sources. In this case, everything worked smoothly, but it's good to keep in mind what problems may occur.

Finally, we can save our transformed dataset.

merged.to_csv('Merged_Dataset.csv', index=False)

Well done! In this lesson you went through an ETL process by performing merges. You also saw the potential pitfalls in merging datasets from different sources. In the next lab, you'll get some practice doing this as an initial step to a regression task.