# Go Beyond CSV: Data Ingestion with Pandas

16 Apr 2022 - Mala Deep Upadhaya

**Mala Deep is a Business Intelligence Consultant, and community focused data citizen.**

# Go Beyond CSV: Data Ingestion with Pandas

## Data will not always come to you in CSV

Pandas read\_csv() method. Image by the author.

Ifyou are a complete beginner or think of yourself as a beginner to data engineering or data science, then you are totally familiar with the Jupyter notebook and the following command.

**#importing dataset**

df = pd.read\_csv(“path/filename.extension”)

Then you know how to acquire data in [CSV (Comma Separated Values)](https://docs.fileformat.com/spreadsheet/csv/). After all, before you can analyze data, you first have to acquire it.

From my experience and most of the tutorials/resources out there, they have always been written with working CSV files. So, when you go to work for a company, and someone gives you data in SQL and/or JSON, then you start to panic and create a self-image of _“oh, this is going to be tough.”_

But, if you follow this blog, you can easily work with any sort of data in a jupyter notebook.

> **Remember:** Before you can analyze data, you first have to acquire it.

Let’s get started. Data ingestion is the process of pulling data from a variety of sources into your system with the purpose of easily exploring, maintaining, and using it for your own use. Today, you will learn how to transfer data from various formats such as Excel, HTML, SQL, and JSON into a Pandas dataframe object using various Pandas methods (highlighted below as _read\_csv()_).

Pandas read\_csv() method. Image by the author.

## Step Involved

To transfer such data format, into a Python DataFrame object, the following steps are taken:

1. Knowing from where and how you will pull data.

Unless at until you have an idea of where your data is and in what format you will never going to bring it as DataFrame object. After that, if your data is residing on the server, then you will need the path, and URL to the file.

1. _Use Pandas ‘_ **_read\_x_** _’ method_

For loading and converting CSV data into a DataFrame object, use \_read\_csv().\_You need to replace **x** with another.

1. Validate the data using a DataFrame object.

Next, you need to verify whether the conversion was smooth.

## For Excel

[Excel sheets (spreadsheets)](https://docs.fileformat.com/spreadsheet/) are still widely used in a variety of domains. Excel is widely used, from developing budgeting strategies to tracking daily student attendance.

- method: _read\_excel()_
- extension/format: _.xlsx_ or _.xls_

For now, you will be using [Preliminary Data of National Population and Housing Census 2021, Nepal](https://opendatanepal.com/dataset/preliminary-data-of-national-population-and-housing-census-2021) dataset from [OpenDataNepal](https://opendatanepal.com/). I have saved this dataset as n\_epalcensusdata.xlsx\_

Snapshot of the Excel dataset. Image by the author. [Preliminary Data of National Population and Housing Census 2021, Nepal](https://opendatanepal.com/dataset/preliminary-data-of-national-population-and-housing-census-2021) dataset from [OpenDataNepal](https://opendatanepal.com/).

**#import the Pandas library**

**\*\*import pandas**#import excel data and store as data\_excel dataframe (no need to do df always)

**data\_excel = pd.read\_excel(“n\_epalcensusdata.xlsx\_”)**#view the dataset as pandas dataframe object

\*\*data\_excel.head()

Output:

Output obtained from data\_excel.head() as Pandas dataframe. Image by the author.

If you have more than one sheet in your workbook then an additional parameter called _sheet\_name_ is needed.

data\_excel = pd.read\_excel(“nepalcensusdata.xlsx”,sheet\_name = 0)

data\_excel.head()

Pandas read\_excel() method. Image by the author.

In our case, we are importing the first sheet, thus it is given 0. The sheet numbers start at 0.

## For HTML

If you are familiar with data scrapping (web scraping), then [Beautiful Soup](https://pypi.org/project/beautifulsoup4/), [Selenium](https://www.selenium.dev/), and [Scrapy](https://scrapy.org/) are familiar to you. You inspect the HTML structure of the website and pull down the data. If you feel it is sometimes time-consuming or hectic, you can easily do that using the _read\_html()_ method.

- method: _read\_html()_
- extension: _.html_

From Wikipedia, we will extract a table from the **_Money Heist_** (Spanish: **_Casa de Papel)_** series on [Netflix](https://www.netflix.com/np/Title/80192098) from the following url: [https://en.wikipedia.org/wiki/Money\_Heist](https://en.wikipedia.org/wiki/Money_Heist)

We will be extracting the awards and nominations tables.

Snapshot of Money Heist awards and nominations table from [Wikipedia](https://en.wikipedia.org/wiki/Money_Heist). Image by the author.

**#import the Pandas library**

import pandas **#import html data and store as data\_html dataframe**

**\*\*data\_html = pd.read\_html(“ [https://en.wikipedia.org/wiki/Money\_Heist](https://en.wikipedia.org/wiki/Money_Heist)”)\[1\]**#view the dataset as pandas dataframe object\*\*

data\_html.head()

Output:

Output obtained from data\_html.head() about awards and nominations of [Netflix series Money Heist](https://en.wikipedia.org/wiki/Money_Heist) as Pandas dataframe. Image by author.

Pandas read\_html() method. Image by the the author.

Here, the initial output from the _read\_html()_ method is a list, and to convert that list into a dataframe, you have to use \[0\] at the end. So, here you have used \[2\] because, on the wikipedia page, you wanted to extract the awards and nominations table. Before this table, there were two more tables. One is called “infobox,” and the next is “Episodes” and to extract the infobox, you need to use \[0\] and for the episode table \[1\].

Pandas read\_html() method for ingesting different tables. Image by the the author.

Remember that using _read\_html()_ is not a simple task.To get the exact table you are looking at, you need to play with your initial result without any frustration.

## For SQL

[Structured Query Language (SQL)](https://en.wikipedia.org/wiki/SQL) is a standard language for storing, manipulating, and retrieving data in databases. Reading data from a database is a two-step process. You first make a way to connect to a database, then query it with SQL and Pandas. In Python, to connect to a SQL server, you need to have either [SQLAlchemy connectabl](https://docs.sqlalchemy.org/en/14/core/connections.html) e or [sqlite3](https://sqlite.org/index.html).

- method: _read\_sql\_query()_
- extension: _.db_

Unlike other formats, for SQL you will have to write a few more lines of code. You first make a connection with the database. Then you will use Pandas’s _read\_sql\_query()_ function to convert the output to a dataframe and write a SQL query to import all columns from the database.

For now, the demo _my\_aquarium.db_ is created with a single table called “fish” with the following columns: _name, species, tank\_number._

**#import the required libraries**

**\*\*import sqlite3**

**import pandas**#prepare a connection object and Pass the Database name as a parameter

**connection = sqlite3.connect(“ _aquarium.db_”)**#use read\_sql\_query method and Pass SELECT query and connection object as parameter

**data\_Sql = pd.read\_sql\_query(“SELECT \* FROM fish”, connection)**# **\*\*view the dataset as pandas dataframe object**

**\*\*print(data\_Sql)**#close the connection object

\*\*connection.close()

Output:

Output obtained from `read_sql_query`() with fish table as Pandas dataframe. Image by the author.

Pandas read\_sql\_query() method. Image by the author.

Remember to close the database connection every time you open it.

# For JSON

[JavaScript Object Notation (JSON)](https://www.json.org/json-en.html) is the most common file format preferred by web developers and data engineers. It’s used widely across the internet for almost every single [API](https://aws.amazon.com/what-is/api/) that you will access.

- method: _read\_json()_
- extension: . _json_

You will use data from [One Piece Anime from Kaggle](https://www.kaggle.com/datasets/aditya2803/one-piece-anime) stored as _openPiece.json_

Snapshot of [One piece anime from Kaggle.](https://www.kaggle.com/datasets/aditya2803/one-piece-anime) Image by author.

**#import the Pandas library**

import pandas **#import html data and store as data\_json dataframe**

**\*\*data\_json = pd.read\_json(“opnePiece.json”)**#view the dataset as pandas dataframe object\*\*

data\_json.head()

Output:

Output obtained from `read_json`() as Pandas DataFrame. Image by the author.

Pandas read\_json() method. Image by the author.

In this manner, you can obtain data in a variety of formats.

> Remember: Acquiring data is just the beginning.

Of course, acquiring data is just the beginning. After this, you need to explore data and play with it before you get deep into a data science project. But if you need ideas on where to go next, go with [exploratory data analysis for begineers.](https://medium.com/analytics-vidhya/exploratory-data-analysis-for-beginner-7488d587f1ec)

PS: I was able to delve deeper into this because of the [Data Fellowship](/content/data-fellowship/index.html) opportunity provided by [Code For Nepal](/content/site-root.html).I’d like to express my gratitude to Code for Nepal for providing a fantastic opportunity to the Nepali community, as well as the [DataCamp](https://www.datacamp.com/) for believing in Code for Nepal.

_If you have any queries regarding the article or want to work together on your next data engineering or data science project, ping me on_ [_LinkedIn_](https://www.linkedin.com/in/maladeep/) _._

Originally published at [Mala’s Medium blog](https://towardsdatascience.com/go-beyond-csv-data-ingestion-with-pandas-4c1a0dd4f2e2)

Disqus Comments

We were unable to load Disqus. If you are a moderator please see our [troubleshooting guide](https://docs.disqus.com/help/83/).

G

Join the discussion…

Comment

###### Log in with

###### or sign up with Disqus  or pick a name

### Disqus is a discussion network

- Don't be a jerk or do anything illegal. Everything is easier that way.

[Read full terms and conditions](https://docs.disqus.com/kb/terms-and-policies/)

This comment platform is hosted by Disqus, Inc. I authorize Disqus and its affiliates to:

- Use, sell, and share my information to enable me to use its comment services and for marketing purposes, including cross-context behavioral advertising, as described in our [Terms of Service](https://help.disqus.com/customer/portal/articles/466260-terms-of-service) and [Privacy Policy](https://disqus.com/privacy-policy), including supplementing that information with other data about me, such as my browsing and location data.
- Contact me or enable others to contact me by email with offers for goods or services
- Process any sensitive personal information that I submit in a comment. See our [Privacy Policy](https://disqus.com/privacy-policy) for more information

Acknowledge I am 18 or older

- [1](https://disqus.com/embed/comments/?base=default&f=codefornepal-1&t_u=https%3A%2F%2Fcodefornepal.org%2F2022%2F04%2F16%2Fgo-beyond-csv-data-ingestion-with-pandas&t_d=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&t_t=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&s_o=default# "Favorite this discussion")

- ## Discussion Favorited!

Favoriting means this is a discussion worth sharing. It gets shared to your followers' Disqus feeds, and gives the creator kudos!

[Find More Discussions](https://disqus.com/home/?utm_source=disqus_embed&utm_content=recommend_btn)

[Share](https://disqus.com/embed/comments/?base=default&f=codefornepal-1&t_u=https%3A%2F%2Fcodefornepal.org%2F2022%2F04%2F16%2Fgo-beyond-csv-data-ingestion-with-pandas&t_d=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&t_t=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&s_o=default#)

- Tweet this discussion
  - Share this discussion on Facebook
  - Share this discussion via email
  - Copy link to discussion

- [Best](https://disqus.com/embed/comments/?base=default&f=codefornepal-1&t_u=https%3A%2F%2Fcodefornepal.org%2F2022%2F04%2F16%2Fgo-beyond-csv-data-ingestion-with-pandas&t_d=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&t_t=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&s_o=default#)
  - [Newest](https://disqus.com/embed/comments/?base=default&f=codefornepal-1&t_u=https%3A%2F%2Fcodefornepal.org%2F2022%2F04%2F16%2Fgo-beyond-csv-data-ingestion-with-pandas&t_d=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&t_t=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&s_o=default#)
  - [Oldest](https://disqus.com/embed/comments/?base=default&f=codefornepal-1&t_u=https%3A%2F%2Fcodefornepal.org%2F2022%2F04%2F16%2Fgo-beyond-csv-data-ingestion-with-pandas&t_d=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&t_t=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&s_o=default#)

- - [−](https://disqus.com/embed/comments/?base=default&f=codefornepal-1&t_u=https%3A%2F%2Fcodefornepal.org%2F2022%2F04%2F16%2Fgo-beyond-csv-data-ingestion-with-pandas&t_d=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&t_t=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&s_o=default# "Collapse")
  - [+](https://disqus.com/embed/comments/?base=default&f=codefornepal-1&t_u=https%3A%2F%2Fcodefornepal.org%2F2022%2F04%2F16%2Fgo-beyond-csv-data-ingestion-with-pandas&t_d=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&t_t=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&s_o=default# "Expand")
  - [Flag as inappropriate](https://disqus.com/embed/comments/?base=default&f=codefornepal-1&t_u=https%3A%2F%2Fcodefornepal.org%2F2022%2F04%2F16%2Fgo-beyond-csv-data-ingestion-with-pandas&t_d=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&t_t=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&s_o=default# "Flag as inappropriate")

M

faltering, unprecedented, I was pondering how to fix skin break out regularly. in addition, tracked down your site by google, taken in a ton, eventually i'm genuinely clear. I've bookmark your site what's more consolidate rss. keep us empowered.

[mlops course](https://360digitmg.com/singapore/mlops-engineering-course-training "https://360digitmg.com/singapore/mlops-engineering-course-training")

see more

[Show more replies](https://disqus.com/embed/comments/?base=default&f=codefornepal-1&t_u=https%3A%2F%2Fcodefornepal.org%2F2022%2F04%2F16%2Fgo-beyond-csv-data-ingestion-with-pandas&t_d=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&t_t=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&s_o=default#)

[Load more comments](https://disqus.com/embed/comments/?base=default&f=codefornepal-1&t_u=https%3A%2F%2Fcodefornepal.org%2F2022%2F04%2F16%2Fgo-beyond-csv-data-ingestion-with-pandas&t_d=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&t_t=Go%20Beyond%20CSV%3A%20Data%20Ingestion%20with%20Pandas&s_o=default#)

live.rezync.com

# live.rezync.com is blocked

This page has been blocked by an extension

- Try disabling your extensions.

ERR\_BLOCKED\_BY\_CLIENT

Reload

This page has been blocked by an extension

pippio.com

# pippio.com is blocked

This page has been blocked by an extension

- Try disabling your extensions.

ERR\_BLOCKED\_BY\_CLIENT

Reload

This page has been blocked by an extension