Laboratory 7: Pandas for Butter!

# Preamble script block to identify host, user, and kernel
import sys
! hostname
! whoami
print(sys.executable)
print(sys.version)
print(sys.version_info)

DESKTOP-EH6HD63
desktop-eh6hd63\farha
C:\Users\Farha\Anaconda3\python.exe
3.7.4 (default, Aug  9 2019, 18:34:13) [MSC v.1915 64 bit (AMD64)]
sys.version_info(major=3, minor=7, micro=4, releaselevel='final', serial=0)

Full name:¶

R#:¶

Title of the notebook:¶

Date:¶

Pandas

A data table is called a DataFrame in pandas (and other programming environments too).

The figure below from https://pandas.pydata.org/docs/getting_started/index.html illustrates a dataframe model:

Each column and each row in a dataframe is called a series, the header row, and index column are special.

To use pandas, we need to import the module, generally pandas has numpy as a dependency so it also must be imported

import numpy as np #Importing NumPy library as "np"
import pandas as pd #Importing Pandas library as "pd"

Dataframe-structure using primative python

First lets construct a dataframe like object using python primatives. We will construct 3 lists, one for row names, one for column names, and one for the content.

mytabular = np.random.randint(1,100,(5,4))
myrowname = ['A','B','C','D','E']
mycolname = ['W','X','Y','Z']
mytable = [['' for jcol in range(len(mycolname)+1)] for irow in range(len(myrowname)+1)] #non-null destination matrix, note the implied loop construction

print(mytabular)

[[95 54 77 70]
 [29  7 59 19]
 [99 76 85 40]
 [72 87 97 21]
 [52 85 45 83]]

The above builds a placeholder named mytable for the psuedo-dataframe. Next we populate the table, using a for loop to write the column names in the first row, row names in the first column, and the table fill for the rest of the table.

for irow in range(1,len(myrowname)+1): # write the row names
    mytable[irow][0]=myrowname[irow-1]
for jcol in range(1,len(mycolname)+1): # write the column names
    mytable[0][jcol]=mycolname[jcol-1]  
for irow in range(1,len(myrowname)+1): # fill the table (note the nested loop)
    for jcol in range(1,len(mycolname)+1):
        mytable[irow][jcol]=mytabular[irow-1][jcol-1]

Now lets print the table out by row and we see we have a very dataframe-like structure

for irow in range(0,len(myrowname)+1):
    print(mytable[irow][0:len(mycolname)+1])

['', 'W', 'X', 'Y', 'Z']
['A', 95, 54, 77, 70]
['B', 29, 7, 59, 19]
['C', 99, 76, 85, 40]
['D', 72, 87, 97, 21]
['E', 52, 85, 45, 83]

We can also query by row

print(mytable[3][0:len(mycolname)+1])

['C', 99, 76, 85, 40]

Or by column

for irow in range(0,len(myrowname)+1):  #cannot use implied loop in a column slice
    print(mytable[irow][2])

X
54
7
76
87
85

Or by row+column index; sort of looks like a spreadsheet syntax.

print(' ',mytable[0][3])
print(mytable[3][0],mytable[3][3])

  Y
C 85

Create a proper dataframe

We will now do the same using pandas

df = pd.DataFrame(np.random.randint(1,100,(5,4)), ['A','B','C','D','E'], ['W','X','Y','Z'])
df

We can also turn our table into a dataframe, notice how the constructor adds header row and index column

df1 = pd.DataFrame(mytable)
df1

To get proper behavior, we can just reuse our original objects

df2 = pd.DataFrame(mytabular,myrowname,mycolname)
df2

Getting the shape of dataframes

The shape method will return the row and column rank (count) of a dataframe.

df.shape

(5, 4)

df1.shape

(6, 5)

df2.shape

(5, 4)

Appending new columns

To append a column simply assign a value to a new column name to the dataframe

df['new']= 'NA'
df

Appending new rows

A bit trickier but we can create a copy of a row and concatenate it back into the dataframe.

newrow = df.loc[['E']].rename(index={"E": "X"}) # create a single row, rename the index
newtable = pd.concat([df,newrow]) # concatenate the row to bottom of df - note the syntax

newtable

Removing Rows and Columns

To remove a column is straightforward, we use the drop method

newtable.drop('new', axis=1, inplace = True)
newtable

To remove a row, you really got to want to, easiest is probablty to create a new dataframe with the row removed

newtable = newtable.loc[['A','B','D','E','X']] # select all rows except C
newtable

Indexing

We have already been indexing, but a few examples follow:

newtable['X'] #Selecing a single column

A    89
B    43
D    47
E    48
X    48
Name: X, dtype: int32

newtable[['X','W']] #Selecing multiple columns

newtable.loc['E'] #Selecing rows based on label via loc[ ] indexer

W    89
X    48
Y    88
Z    70
Name: E, dtype: int32

newtable.loc[['E','X','B']] #Selecing multiple rows based on label via loc[ ] indexer

newtable.loc[['B','E','D'],['X','Y']] #Selecting elemens via both rows and columns via loc[ ] indexer

Conditional Selection

df = pd.DataFrame({'col1':[1,2,3,4,5,6,7,8],
                   'col2':[444,555,666,444,666,111,222,222],
                   'col3':['orange','apple','grape','mango','jackfruit','watermelon','banana','peach']})
df

#What fruit corresponds to the number 555 in ‘col2’?

df[df['col2']==555]['col3']

1    apple
Name: col3, dtype: object

#What fruit corresponds to the minimum number in ‘col2’?

df[df['col2']==df['col2'].min()]['col3']

5    watermelon
Name: col3, dtype: object

Descriptor Functions

#Creating a dataframe from a dictionary

df = pd.DataFrame({'col1':[1,2,3,4,5,6,7,8],
                   'col2':[444,555,666,444,666,111,222,222],
                   'col3':['orange','apple','grape','mango','jackfruit','watermelon','banana','peach']})
df

`head` method

Returns the first few rows, useful to infer structure

#Returns only the first five rows

df.head()

`info` method

Returns the data model (data column count, names, data types)

#Info about the dataframe

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 8 entries, 0 to 7
Data columns (total 3 columns):
col1    8 non-null int64
col2    8 non-null int64
col3    8 non-null object
dtypes: int64(2), object(1)
memory usage: 320.0+ bytes

`describe` method

Returns summary statistics of each numeric column.
Also returns the minimum and maximum value in each column, and the IQR (Interquartile Range).
Again useful to understand structure of the columns.

#Statistics of the dataframe

df.describe()

Counting and Sum methods

There are also methods for counts and sums by specific columns

df['col2'].sum() #Sum of a specified column

3330

The unique method returns a list of unique values (filters out duplicates in the list, underlying dataframe is preserved)

df['col2'].unique() #Returns the list of unique values along the indexed column

array([444, 555, 666, 111, 222], dtype=int64)

The nunique method returns a count of unique values

df['col2'].nunique() #Returns the total number of unique values along the indexed column

5

The value_counts() method returns the count of each unique value (kind of like a histogram, but each value is the bin)

df['col2'].value_counts()  #Returns the number of occurences of each unique value

222    2
444    2
666    2
111    1
555    1
Name: col2, dtype: int64

Using functions in dataframes - symbolic apply

The power of pandas is an ability to apply a function to each element of a dataframe series (or a whole frame) by a technique called symbolic (or synthetic programming) application of the function.

Its pretty complicated but quite handy, best shown by an example

def times2(x):  # A prototype function to scalar multiply an object x by 2
    return(x*2)

print(df)
print('Apply the times2 function to col2')
df['col2'].apply(times2) #Symbolic apply the function to each element of column col2, result is another dataframe

   col1  col2        col3
0     1   444      orange
1     2   555       apple
2     3   666       grape
3     4   444       mango
4     5   666   jackfruit
5     6   111  watermelon
6     7   222      banana
7     8   222       peach
Apply the times2 function to col2

0     888
1    1110
2    1332
3     888
4    1332
5     222
6     444
7     444
Name: col2, dtype: int64

Sorts

df.sort_values('col2', ascending = True) #Sorting based on columns

Aggregating (Grouping Values) dataframe contents

#Creating a dataframe from a dictionary

data = {
    'key' : ['A', 'B', 'C', 'A', 'B', 'C'],
    'data1' : [1, 2, 3, 4, 5, 6],
    'data2' : [10, 11, 12, 13, 14, 15],
    'data3' : [20, 21, 22, 13, 24, 25]
}

df1 = pd.DataFrame(data)
df1

# Grouping and summing values in all the columns based on the column 'key'

df1.groupby('key').sum()

# Grouping and summing values in the selected columns based on the column 'key'

df1.groupby('key')[['data1', 'data2']].sum()

Filtering out missing values

#Creating a dataframe from a dictionary

df = pd.DataFrame({'col1':[1,2,3,4,None,6,7,None],
                   'col2':[444,555,None,444,666,111,None,222],
                   'col3':['orange','apple','grape','mango','jackfruit','watermelon','banana','peach']})
df

Below we drop any row that contains a NaN code.

df_dropped = df.dropna()
df_dropped

Below we replace NaN codes with some value, in this case 0

df_filled1 = df.fillna(0)
df_filled1

Below we replace NaN codes with some value, in this case the mean value of of the column in which the missing value code resides.

df_filled2 = df.fillna(df.mean())
df_filled2

Reading a File into a Dataframe

Pandas has methods to read common file types, such as csv,xlsx, and json. Ordinary text files are also quite manageable.

On a machine you control you can write script to retrieve files from the internet and process them.

readfilecsv = pd.read_csv('CSV_ReadingFile.csv')  #Reading a .csv file
print(readfilecsv)

    a   b   c   d
0   0   1   2   3
1   4   5   6   7
2   8   9  10  11
3  12  13  14  15

Similar to reading and writing .csv files, you can also read and write .xslx files as below (useful to know this)

readfileexcel = pd.read_excel('Excel_ReadingFile.xlsx', sheet_name='Sheet1') #Reading a .xlsx file
print(readfileexcel)

   Unnamed: 0   a   b   c   d
0           0   0   1   2   3
1           1   4   5   6   7
2           2   8   9  10  11
3           3  12  13  14  15

Writing a dataframe to file

#Creating and writing to a .csv file
readfilecsv = pd.read_csv('CSV_ReadingFile.csv')
readfilecsv.to_csv('CSV_WritingFile1.csv')
readfilecsv = pd.read_csv('CSV_WritingFile1.csv')
print(readfilecsv)

   Unnamed: 0   a   b   c   d
0           0   0   1   2   3
1           1   4   5   6   7
2           2   8   9  10  11
3           3  12  13  14  15

#Creating and writing to a .csv file by excluding row labels 
readfilecsv = pd.read_csv('CSV_ReadingFile.csv')
readfilecsv.to_csv('CSV_WritingFile2.csv', index = False)
readfilecsv = pd.read_csv('CSV_WritingFile2.csv')
print(readfilecsv)

    a   b   c   d
0   0   1   2   3
1   4   5   6   7
2   8   9  10  11
3  12  13  14  15

#Creating and writing to a .xlsx file
readfileexcel = pd.read_excel('Excel_ReadingFile.xlsx', sheet_name='Sheet1', index = False)
readfileexcel.to_excel('Excel_WritingFile.xlsx', sheet_name='MySheet', index = False)
readfileexcel = pd.read_excel('Excel_WritingFile.xlsx', sheet_name='MySheet', index = False)
print(readfileexcel)

   Unnamed: 0   a   b   c   d
0           0   0   1   2   3
1           1   4   5   6   7
2           2   8   9  10  11
3           3  12  13  14  15

This is a Pandas Cheat Sheet

Here are some of the resources used for creating this notebook:

Pandas foundations. Retrieved February 15, 2021, from https://www.datacamp.com/courses/pandas-foundations
Pandas tutorial. Retrieved February 15, 2021, from https://www.w3schools.com/python/pandas/default.asp
Pandas tutorial: Dataframes in Python. Retrieved February 15, 2021, from https://www.datacamp.com/community/tutorials/pandas-tutorial-dataframe-python

Here are some great reads on this topic:

"Introduction to Pandas in Python" available at *https://www.geeksforgeeks.org/introduction-to-pandas-in-python/
"Pandas Introduction & Tutorials for Beginners" by Walker Rowe, available at *https://www.bmc.com/blogs/pandas-basics/
"Using Pandas and Python to Explore Your Dataset" by Reka Horvath available at *https://realpython.com/pandas-python-explore-dataset/
"Python Pandas Tutorial: A Complete Introduction for Beginners" by George McIntire, Lauren Washington, and Brendan Martin available at *https://www.learndatasci.com/tutorials/python-pandas-tutorial-complete-introduction-for-beginners/

Here are some great videos on these topics:

"Python: Pandas Tutorial | Intro to DataFrames" by Joe James available at *https://www.youtube.com/watch?v=e60ItwlZTKM
"Complete Python Pandas Data Science Tutorial! (Reading CSV/Excel files, Sorting, Filtering, Groupby)" by Keith Galli available at *https://www.youtube.com/watch?v=vmEHCJofslg
"What is Pandas? Why and How to Use Pandas in Python" by Python Programmer available at *https://www.youtube.com/watch?v=dcqPhpY7tWk

	col1	col2
count	8.00000	8.0000
mean	4.50000	416.2500
std	2.44949	211.8576
min	1.00000	111.0000
25%	2.75000	222.0000
50%	4.50000	444.0000
75%	6.25000	582.7500
max	8.00000	666.0000