Tutorial on Python Pandas

How to read or import Pandas data

It is quite easy to read or import data from other files using the Pandas library. In fact, we can use various sources, such as CSV, JSON, or Excel to load our data and access it. Let’s take a look at each.

Reading and importing data from CSV files

We can import data from a CSV file, which is common practice for Pandas users. We simply create or open our CSV file, copy the data, paste it in our Notepad, and save it in the same directory that houses your Python scripts. You then use a bit of code to read the data using the read_csv function build into Pandas.

import pandas as pd
data = pd.read_csv('vegetables.csv')
print(data)

read_csv will generate the index column as a default, so we need to change this for the first column is the index column. We can do this by passing the parameter index_col to tell Pandas which column to index.

data = pd.read_csv("data.csv", index_col=0)

Once we’ve used Pandas to sort and clean data, we can then save it back as the original file with simple commands. You only have to input the filename and extension. How simple!

df.to_csv('new_vegetables.csv')

Reading and importing data from JSON

Say you have a JSON file. A JSON file is basically like a stored Python dict, so Pandas can easily access and read it using the read_json function. Let’s look at an example.

df = pd.read_json('purchases.json')

Just like with CSV files, once we’ve used Pandas to sort and clean data, we can then save it back as the original file with simple commands. You only have to input the filename and extension.

df.to_json('new_purchases.json')

Reading and importing data from Excel file

Say you have an Excel file. You can similarly use the read_excel function to access and read that data.

import pandas as pd
data = pd.read_excel('workers.xlsx')
print(data)

Once we call the read_excel function, we pass the name of the Excel file as our argument, so read_excel will open the file’s data. We can the print() to display the data. If we want to go one step further, we can add the loc() method from earlier, allowing us to read specific rows and columns of our file.

import pandas as pd
data = pd.read_excel('workers.xlsx')

print(data.loc[[1,4,7],['Name','Salary']])

Data Wrangling with Pandas

Once we have our data, we can use data wrangling processes to manipulate and prepare data for the analysis. The most common data wrangling processes are merging, concatenation, and grouping. Let’s get down the basics of each of those.

Merging with Pandas

Merging is used when we want to collect data that shares a key variable but are located in different DataFrames. To merge DataFrames, we use the merge() function. Say we have df1 and df2.

import pandas as pd

d = {
'subject_id':['1','2','3','4','5'],
'student_name':['Mark','Khalid','Deborah','Trevon','Raven']
}
df1 = pd.DataFrame(d, columns=['subject_id','student_name'])
print(df1)

import pandas as pd

data ={
'subject_id':['4','5','6','7','8'],
'student_name':['Eric','Imani','Cece','Darius','Andre']
}
df2 = pd.DataFrame(data, columns=['subject_id','student_name'])
print(df2)

So, how do we merge them? It’s simple: with the merge() function!

pd.merge(df1, df2, on='subject_id')

# import pandas library
import pandas as pd

raw = {
    'Name': ['Darell', 'Darell', 'Lilith', 'Lilith', 'Tran', 'Tran', 'Tran',
        'Tran', 'John', 'Darell', 'Darell', 'Darell'],
    'Position': [2, 1, 1, 4, 2, 4, 3, 1, 3, 2, 4, 3],
    'Year': [2009, 2010, 2009, 2010, 2010, 2010, 2011, 2012, 2011, 2013, 2013, 2012],
    'Marks':[408, 398, 422, 376, 401, 380, 396, 388, 356, 402, 368, 378]
}
df = pd.DataFrame(raw)

group = df.groupby('Year')
print(group.get_group(2011))