Introduction to Pandas#

Importing Libraries#

For this notebook, we will be reading and analyzing data using pandas. These are not part of standard Python, and we must import the package to use it.

import pandas as pd

Now, if we want to use functions from plotly or pandas, we will do pd.function_name or px.function_name

In this notebook, we will be working with a data set that contains information about the elements in the periodic table. The data is a csv (comma separated value) file from PubChem in your data folder called PubChemElements_all.csv.

This is a comma-separated value file, and we will use the pandas function read_csv. You can read more about this function in your Jupyter by typing pd.read_csv in the cell below, then right clicking and choosing “Show Contextual Help”.

It is common to name the variable from the read_csv function df. This is short for DataFrame. Since this file is relatively simple, we do not need any additional arguments to the function. The read_csv function reads in tabular data which is comma delimited by default.

df = pd.read_csv("data/PubChemElements_all.csv")

Examining the data#

The variable df is now a pandas DataFrame with the information contained in the csv file. You can examine the DataFrame using the .head() method. This shows the first 5 rows stored in the DataFrame. The first row of the file was used for column headers.

df.head()

The .info function will give information about the columns and the data type of those columns. The data type will become very important later as we work with more data.

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 118 entries, 0 to 117
Data columns (total 17 columns):
 #   Column                 Non-Null Count  Dtype  
---  ------                 --------------  -----  
 0   AtomicNumber           118 non-null    int64  
 1   Symbol                 118 non-null    object 
 2   Name                   118 non-null    object 
 3   AtomicMass             118 non-null    float64
 4   CPKHexColor            108 non-null    object 
 5   ElectronConfiguration  118 non-null    object 
 6   Electronegativity      95 non-null     float64
 7   AtomicRadius           113 non-null    float64
 8   IonizationEnergy       102 non-null    float64
 9   ElectronAffinity       57 non-null     float64
 10  OxidationStates        103 non-null    object 
 11  StandardState          118 non-null    object 
 12  MeltingPoint           103 non-null    float64
 13  BoilingPoint           93 non-null     float64
 14  Density                96 non-null     float64
 15  GroupBlock             118 non-null    object 
 16  YearDiscovered         118 non-null    object 
dtypes: float64(8), int64(1), object(8)
memory usage: 15.8+ KB

This output will show information about each column of data. Pandas assigns data types to columns, and will do its best to decide the data column for each column based on what is in the column. We can also see how many values are in each column.

We can also see descriptive statisics easily using the .describe() command.

df.describe()

The describe function lists the mean, max, min, standard deviation and percentiles for each column excluding NaN (not a number) values.

Accessing Data in DataFrames#

One way to get information in a data frame is by using the headers, or the column names using squre brackets. The synatx for this is

dataframe["column_name"]

For example, to get the column with information about the atomic symbol, we would use the “Symbol” column.

df["Symbol"]

0       H
1      He
2      Li
3      Be
4       B
       ..
113    Fl
114    Mc
115    Lv
116    Ts
117    Og
Name: Symbol, Length: 118, dtype: object

If you want to select multiple columns, you use a list of column names in square brackets.

df[["Symbol", "AtomicNumber"]]

Get data using number index#

If we want to get information in the dataframe using row and column numbers, we use the iloc function.

The syntax for iloc is

dataframe.iloc[row_number, column_number]

If you specify only a row number, you will get all the columns.

# This will get the first row, all of the columns.
df.iloc[0]

AtomicNumber                    1
Symbol                          H
Name                     Hydrogen
AtomicMass                  1.008
CPKHexColor                FFFFFF
ElectronConfiguration         1s1
Electronegativity             2.2
AtomicRadius                120.0
IonizationEnergy           13.598
ElectronAffinity            0.754
OxidationStates            +1, -1
StandardState                 Gas
MeltingPoint                13.81
BoilingPoint                20.28
Density                   0.00009
GroupBlock               Nonmetal
YearDiscovered               1766
Name: 0, dtype: object

# This will get the first row and the second column.
df.iloc[0, 1]

'H'

Performing Calculations#

You can do mathematical operations on entire columns or rows of pandas dataframes using single lines of code. For example, if we wanted to subtract 273.15 from our melting point column, we could do so with the following like of code.

df["MeltingPoint"] - 273.15

0      -259.34
1      -272.20
2       180.50
3      1286.85
4      2074.85
        ...   
113        NaN
114        NaN
115        NaN
116        NaN
117        NaN
Name: MeltingPoint, Length: 118, dtype: float64

To save your calculation in a new column in your dataframe, use the syntax

df["new_column_name"] = CALCULATION

df["MeltingPointC"] = df["MeltingPoint"] - 273.15

If you have a more complicated action you’d like to peform for every column, you can use the syntax

df["column_name"].apply(FUNCTION)

where FUNCTION is a function that exists (like len) or a function that you’ve defined. Consider a function to calculate the temperature in Farhenheit from Celsius.

def kelvin_to_fahrenheit(kelvin_temp):
    fahrenheit = (kelvin_temp - 273.15) * 9/5 +32
    return fahrenheit

If you wanted to apply this function to every row, your first instinct might be to write a for loop. This would work, but pandas has a built in method called apply to easily allow you to do this.

When you call the apply method, you give it a function name which you would like to apply to every element of whatever you are using it on.

# Calculate the boiling point in fahrenheit
df['BoilingPoint'].apply(kelvin_to_fahrenheit)

0      -423.166
1      -452.074
2      2447.330
3      4479.530
4      7231.730
         ...   
113         NaN
114         NaN
115         NaN
116         NaN
117         NaN
Name: BoilingPoint, Length: 118, dtype: float64

Saving a new DataFrame#

If you wanted to save your data to a csv, you could do it using the method to_csv.

df.to_csv('periodic_data_processed.csv')

Filtering Data#

We can also filter data using signs like greater than >, less than <, or equal to ==.

In the cell below, we check whether or not each boiling point is greater than 500. If it is, True is returned, if not False.

df["BoilingPoint"] > 500

0      False
1      False
2       True
3       True
4       True
       ...  
113    False
114    False
115    False
116    False
117    False
Name: BoilingPoint, Length: 118, dtype: bool

We can use this as an index or slice, similar to how we learned with lists. We can see from the output that this gives us 80 elements.

df[df["BoilingPoint"]>500]

Grouping Data#

You can perform operations like grouping data by category using pands. For this analysis, we will group elements by their standard state.

grouped_data = df.groupby(by="StandardState")

This is now essentially a group of dataframes. If we use describe, we will get separate statistics for each group.

grouped_data.describe()

Similarly, we can see statistics for each group when using a method like .mean().

grouped_data["BoilingPoint"].mean()

StandardState
Expected to be a Gas              NaN
Expected to be a Solid            NaN
Gas                        102.452727
Liquid                     480.915000
Solid                     2922.236875
Name: BoilingPoint, dtype: float64