Python for Data Science – Importing CSV, JSON, Excel Using Pandas

Although I think that R is the language for Data Scientists, I still prefer Python to work with data. In this blog post, I will show you how easy to import data from CSV, JSON and Excel files using Pandas libary. Pandas is a Python package designed for doing practical, real world data analysis.

Here is the content of the sample CSV file (test.csv):

Here is the content of the sample JSON file (test.json):

I also created an Excel file (test.xls):

Here is the script which reads each sample file and prints its content:

When we run the above code, we get the following output:

As you can see, all I need is to import panda package and call the related function. The pandas package provides powerful functions that can be used to import the data from these files into “DataFrames” (two-dimensional arrays/matrices).

To be able to read Excel files, you may need to install an additional package to your system. If you get an error while reading excel saying xlrd is missing, run the following code to install the package:

One of the important point is, JSON data needs some extra methods to convert it a dataframe because of its schema-less structure. When we import JSON data using Panda, all values (name, email in our sample) are stored in one column. To be able to effectively analyse the data, we need to split this column.

I didn’t want to use loops to split the data, so I did a little trick and applied a simple function to transform the customer column. I get “name” and “email”, and then return them as a Panda Series, so our customer column is transformed to two columns (name and email).

As you can see, Panda is a great library to handle different file formats. I’ll continue writing about Python for Data Science series, share more samples about Pandas. See you on next blog post!

Please share this post Share on Facebook3Share on Google+0Share on LinkedIn34Share on Reddit0Tweet about this on Twitter

Gokhan Atil is a database administrator who has hands-on experience with both RDBMS and noSQL databases (Oracle, PostgreSQL, Microsoft SQL Server, Sybase IQ, MySQL, Cassandra, MongoDB and ElasticSearch), and strong background on software development. He is certified as Oracle Certified Professional (OCP) and is awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for his continuous contributions to the Oracle users community.

Leave Comment

Your email address will not be published. Required fields are marked *