Building ETL Pipelines in Python: Part 1
Tags: python, programming, datascience
Published: 2021-08-21 03:17:01

Data engineering refers to the development of software that performs three tasks:

1. Extract raw data from various sources.
2. Transform and clean up the data.
3. Load the data into a data warehouse, i.e., a database management system like, Google BigQuery or Amazon Redshift.
Alternatively,load the data into MySQL, a relational database.


The process is complex involving numerous software tools and advanced technical expertise. For one thing is can be challenging and overwhelming to identify the starting point and resources from which to acquire the basic knowledge. No wonder the demand for data engineers in the technology sector has risen by an order of magnitude in the past years. As the need for data generation grows, the thirst for data engineers will continue to intensify.

This post, part 1 of a three-part tutorial shows you how to build a simple ETL[extract,transform,and load] pipeline with CSV files in Python, describes the extraction steps.

**Software Requirements**

You will need the following software:

* `glob`, a package that is preinstalled that searches for files in relative paths.
For details, see this [section](https://docs.python.org/3/library/glob.html) for documentation.

* `pandas`, a package used for data manipulation. To install, type on the command line, `pip install pandas`. 
For details, see the [pandas](https://pandas.pydata.org/) site.

* `virtualenvwrapper`, a package that sets up virtual environments for Python. For details, see the related [documentation](https://virtualenvwrapper.readthedocs.io/en/latest).

* This [Kaggle dataset](https://www.kaggle.com/antfarol/car-sale-advertisements) for the CSV data.

Here's an example of CSV data on car sales:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7rxf6hzyk454gnr47hwr.png)

The Procedure:

1. Create a project called `etl_car_sales` with PyCharm.

2. Create a virtual environment with the command line `mkvirtualenv etl_car_sales`.

3. Install `pandas` and `virtualenwrapper`.

4. Extract the zip file and move the CSV files for `car_sales` to your `etl_car_sales` directory, like this:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p2s0uux2s9tqk710qp73.png)

5. Create an `extract_from_csv` function to read the CSV file
in your main directory and then return a Dataframe with the `pandas` function `read_csv`.
  
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mjavo6yl33v9tj7xrirz.png)

6. Create an `extract` function to read the data from the extracted csv file and append the data to the Dataframe that 
is being return the `extract_from_csv` function.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xxyevo26ncf6ke0cchri.png)

**Tip**: To rename the columns, do the following:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kyclgsqjdl9ybm6vkiwk.png)

7. Print out the `extracted_data` variable and the result would read like this:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iszr5dgkb41e2fmsw4v2.png)

Part 2 will describe how to transform data in the ETL pipeline. Stay Tuned.