Introduction¶
Pandas is a python package for reading, cleaning, exploring and analyzing tabular data such as data from spreadsheets or databases. Pandas provides flexible and expressive data structures that facilitate working with labelled and relational database
Supported Data Format¶
Pandas is ideal for working with any table-like data that can be stored in common format such as excel. Dataframe is the equivalent table representation in Pandas. You can create a dataframe manually or import from excel, txt, csv. To create data manually, call the “DataFrame” method and insert array of data to create. Just as we have column and row labels in common program for handling 2-D table data format, pandas also has column headers and row labels. It is crucial to understand how pandas create dataframe from different python data structure. If the data is a python dictionary, the key would be the column headers and the values in each list would be column of the dataframe while the row labels would be integers by default except specified otherwise. The column and row lables are important for indexing. If the data is a list of list, the values in each nested list would be the rows. If column name and row headers are not specified in the case of list of list, pandas would use default integer values as column headers and row labels.
# import package
import pandas as pd
#Create a dataframe from a dictionary
df1 = pd.DataFrame({"A": [1, 2, 3], "B": [4, 5, 6], "C": [7, 8, 9]})
df1
print(type(df1))
#create a dataframe from a list of lists
df2 = pd.DataFrame([[1, 2, 3], [4, 5, 6], [7, 8, 9]])
df2
#create a dataframe from a list of lists and specify column names
df3 = pd.DataFrame([[16, 2, 53], [44, 35, 68], [73, 28, 95]], columns=['A', 'B', 'C'])
df3
To select a colum in a dataframe, use column label in between square bracket. Each column in a dataframe is a series.
#select column C in df1
df1['C']
print(type(df1['C']))
Dataframe can store data of different format including integers, float, string, boolean etc. Let’s create a more heterogenous data for firs 5 passengers in the titanic dataset shown in the image below.
df_4 = pd.DataFrame({
'PassengerID':[1,2,3,4,5],
'Survived': [0,1,1,1,0],
'Name': ['Braud', 'Cumings', 'Heikkinen', 'Frode', 'Kallio'],
'Sex': ['male', 'female', 'female', 'female', 'male'],
'Age': [22, 38, 26, 35, 35],
'Ticket': [2457, 1135, 3421, 3, 1419],
'Fare': [7.25, 15.0, 26.0, 8.05, 9.0]
})
df_4
We can perform some artithmetric operation on series on dataframe. But before that two vital methods for getting statistical info about the dataframe are info() and describe()
df_4.info()
df_4.describe()
So far, we have created dataframes manually. What if we are to read data from other programs which is most commonly the case. Pandas provide the read_ method. The is the name/extension of the source. For example, to read from a csv, use read_csv()
#import from csv
titanic_df = pd.read_csv('train.csv')
titanic_df
The data that we just read has 891 rows and 12 columns but just the top 5 rows and last 5 are shown by default
Subsetting¶
We at times need only some parts of the dataset to work. Pandas, provide a easy way to select specific columns and/or row values. To select a column, call the dataframe and the name of the column in a square brackect. This syntax works in similar approach as dictionary indexing using keys. To select multiple colums, use a list of column names
#Select the age column
Age = titanic_df['Age']
print(type(Age)) #this is a series since it is one column
Age
Filtering a dataframe is also very easy with dataframe. The syntax involves calling the dataframe with the filtering condition in a square bracket; df[‘condition’]. Let’s look at the example of how to select all teanagers in our titanic dataset. The condition is ‘df[‘Age’] < 19′. So we put this in a selection bracket of the dataframe. Then we have ‘df[df[‘Age’] < 19]’
teens_df = titanic_df[titanic_df['Age'] < 19]
teens_df
We can also combine conditions. Let see an example of selecting teenagers i.e df[‘Age’] < 19 that survived i.e df[‘Survived’] == 1
survteens_df = titanic_df[(titanic_df['Age'] < 19) & (titanic_df['Survived'] == 1)]
survteens_df
At times, when we filter our dataset, we want to selct only one column. We just use select bracket on the column of interest. However, we can also use loc operator. This works in similar way as the indexing above except that we have to specify the column we want to return in addition to the filtering condition
survteens_df['Name']
titanic_df.loc[((titanic_df['Age'] < 19) & (titanic_df['Survived'] == 1), 'Name')]
Syntax of loc or iloc operator looks complex but they are simple. The idea is the they allow row and column indexing at a go. Call the operator with the row condition and columns of interest.
df.loc[row_condition, column_of_interest]. When using row and column names
df.iloc[row_condition, column_of_interest]. When specifically interested in certain rows and columns based on position indexing
Let’s use iloc operator to select the first 30 rows of the last three columns in our titanic dataframe
titanic_df.iloc[:30,-3:]
Creating Plots in Pandas¶
Pandas plot() method eanables easy visualization of data. To demonstrate its use, we will be using Snow data downloaded from snotel sites.
Use read_csv, tell pandas the column to use for indexing, and parse_dates = True
SWE =pd.read_csv('Monthly_Mean_SWE.csv', index_col='Months', parse_dates=True)
SWE
SWE.plot()
With a dataframe, pandas plot() method will give one line plot for each columns with numeric data as shown by the result of the code cell above. To plot a specific column, use the selection method described above before the plot method.
SWE[['2000', '2005', '2010', '2015', '2020']].plot(figsize=(10,7))
Besides the default line plot, data can be visualized with other types of plots such as scatter, boxplots etc
#datetime index
SWE.plot.scatter(x='2000', y='2005')
SWE[['2000', '2005', '2010', '2015', '2020']].plot.box()
#seperate plots
SWE[['2000', '2005', '2010', '2015', '2020']].plot(figsize=(10,7), subplots=True)
Groupby¶
Finding statistics for each category in a datset is a common task in data analysis. For example we might be interested in knowing the mean of the ages for each gender in the titanic dataset.
The syntax is:
df.groupby(catgerogy_to_groupby).statistics()
In this example, the category is sex and the statistics of interest is mean.
titanic_df[['Age', 'Sex']].groupby('Sex').mean()
Note that the groupby().mean() syntax was applied to a subselection of the two columns of interest (‘Sex’ and ‘Age’ in this case), otherwise the statitical operation will be performed on all columns containing numerical values. This is demonstrated by the code cell below. However, some columns such as Pclass (passenger class) with numerical values encode categorical information or other data types thus not ideal to find the mean or other statistics.
titanic_df.groupby('Sex').mean()
Alternatively, we can use a selection bracket [] after the groupby() but before the statistics to specifically select the column of interest
titanic_df.groupby('Sex')['Age'].mean()
We can also find the statistics of multiple category combination. For example, we might be interested in knowing the average ticket fare for each sex of each Passenger class. These can be achieved using the two code cells below
titanic_df[['Fare', 'Sex', 'Pclass']].groupby(['Sex', 'Pclass']).mean()
titanic_df.groupby(['Sex', 'Pclass'])['Fare'].mean()
It is crucial to note that the first example returned a dataframe while the second returned a series. Another information to derive from groupby is the number of occurence of each category in a function. This can be achieved using the count() method
titanic_df.groupby('Sex')['Sex'].count()
This count operation can be achieved using just value_counts() method on the dataframe
titanic_df['Sex'].value_counts()
