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

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

1 Comment

Leave Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.