Pandas Tutorial
Pandas is a Python library used for working with data sets, often used to conduct cleaning, exploring, and statistic summary.
0. Pandas data types
Pandas support Series and DataFrames data objects.
0.1 Series
A series is like a column in a table. To create a series, we can list out the elements (i.e., values) in a pair of square brackets [ ] and transform it into a series object using Series(). For example:
0.2 Dataframe
A DataFrame is similar to a table with rows and columns. We can create a table as a dictionary using curvy brackets { }, where the key is the variable name and the value for each variable are stored in [ ] after a :. Note that all columns should have the same number of entries in the value. We can then transform the dict into a DataFrame object using DataFrame(). For example:
1. Load data
Depending on the data file type, we can use
read_csv()read_json()read_table()read_excel()
to load in the data and save it as a DataFrame object.
Using shape, we can check that the dataset consists of 409 rows and 12 columns. To take a quick look of the data, display the first k rows using head(k) or the last k rows using tail(k). Let’s view the first 5 rows of our data.
As an exercise, display the last 5 rows from the dataset df.
2. Data Cleaning
2.1 Empty/ NA Cell
First, let’s examine rows that contain at least one missing value, such as
To remove ALL rows that contain empty cells in the data, we can use dropna().
By checking the cleaned data dimension, we can confirm that there are 409-379 = 30 rows consist of at least one NA value.
We can also remove rows that have NA values in certain columns by specifying the subset of columns in dropna().
Notice that row 5 (shown in na_rows.iloc[:5]), which contains missing value in Heart_attack is not longer available in the output.
If you do not save the object after dropping NA values, the original data remains unchanged. There is a handy option that allows you to alter the original data directly by setting inplace = True. In that case, running df.dropna(inplace = True) would not output anything.
Alternatively, we can also replace NA with other values using fillna(). For example, df.fillna(130, inplace = True) will replace all NA values in the original dataframe with 130. We can also specify the replacement values for each column. For example:
Notice that row 5 has “Heart_attack” value replaced as Unknown now.
We can also replace them with mean, median or mode.
As an exercise, try to replace all missing values in “Sedentary_min” by the median.
2.2 Wrong Data
Sometimes, wrong data does not have to be empty cells, it can just be an wrong but valid entry. To replace these with correct values, we can use loc[row_index, column_name] to locate the entry.
For instance, suppose row 156 in the data records 1.75 instead of 175 in weight when it is measured in lb. We can fix it by running df.[156, "Weight"] = 175.
If we know that a variable has some certain constrain, such as weight must be below 500lb. We can remove all out-of-boundary entries using and if-then statement inside a for loop.
2.3 Duplicate Rows
Duplicate rows are rows that have been recorded more than one time. We can use duplicated() to check if each row has a duplicate, it will give TRUE/FALSE for each row. Wrapping it with sum() allows us to summarize how many duplicate rows there are in the data.
In case when we want to remove duplicate rows, we can do this by drop_duplicates(), there is a inplace = True option allowing us to remove it directly on the original data as well. i.e., df.drop_duplicates(inplace = True).
2.4 Extracting columns
To extract a column from the dataframe, we use the square brackets [ ].
df[column_name]stores as a Series (only applicable when we select one variable)df[[column_name]]stores a DataFrame
For example, we can extract “ID” and “Weight” columns from df by:
As an exercise, choose to display only Age, Gender, and BMI from df.
Pandas also have a filter() function that selects a subset of variables. But note that this filter function serves as a different role than filter in R.
2.5 Filtering rows
Similar to R, we can filter rows in the dataset based on some condition. The coditional operations include:
>: greater than<: smaller than==: equal to!=: not equal to&: AND|: OR
We can extract rows using df[conditional_statement]. Different from R, we need to specify a Series (single squared bracket) object in the conditional statement such as df["variable_name"] > 20 instead of "variable_name" > 20. If there are more than one conditions, remember to wrap each condition with ().
Here are some examples:
As an exercise, filter for patients that have “Age” above 40 and experienced “Heart_attack” (indicated as “Yes”).
2.6 Creating new variables
To create a new variable, simply store the list of values into df["new_variable_name"]. You can create the new values by setting them as a constant value, some calculations of existing variable or conditional assignment.
Recall the original data is:
Here are some examples of creating new variables:
Now, view the updated dataset with additional columns attached:
As an exercise, create a new column that stores patients’ age 10 years later. Define this new column as age_10_years_later.
2.7 Creating new variable using conditional assignment
With numpy, the syntax is where(conditional_statment, true_assignment, false_assigment). Essentially, It checks a condition for every row and then chooses one of two values. If the conditional_statment is satisfied, true_assignment value will be assigned for that row. Otherwise, it will be set as false_assigment value.
Here is a step-by-step breakdown:
- Set up a condition:
df["Weight"] > 120 - Set a value to use when the condition is true:
"High" - Set a value to use when the condition is false:
"Normal" - Put into the
where(conditional_statment, true_assignment, false_assigment)syntax as follow
With pandas, df["Variable"].where(conditional_statment) will assign the corresponding Variable value of that row if it satisfies the conditional_statment, otherwise NaN.
As an exercise, create a new column that denote Gender in terms of Male(1) and Female(2). Name this new column as gender_text.
2.8 Group summaries
We can use groupby() to organize the dataset by a variable of interest.
We can then compute summary statistics for each group on a numerical variable by chaining on the appropriate aggregation functions like
mean()median()mode()max()min()std()var()count()
As an exercise, evaluate the group level means of BMI by Heart_attack status.
2.9 Transforming categorical variables into dummy variables
Often time, when we fit regression models with categorical predictors, we have to represent the corresponding levels using dummy indicators. For example, the model can’t directly use text labels like “Male”, “Female”, “Other” or “High school”, “College”, “Graduate”. Dummy variables are the way we translate categories into numbers. Imagine each category level gets its own light switch:
- If the observation belongs to that category, the switch is ON (1)
- If not, the switch is OFF (0)
For a variable like Education with 3 levels: “High school”, “College”, “Graduate”. We create:
Collegeis 1 if subject has “College” level education, 0 otherwiseGraduateis 1 if subject has “Grduate” level education, 0 otherwise
We don’t need to create a dummy for “High school” because it becomes the baseline (all switches OFF).
To create these dummy variables, we use get_dummies. The drop_first=True suggests that we only create k-1 dummy variables if there are k levels by dropping the first level.
The dummy variables are coded as originalname_levelname. You can choose to format them using options in get_dummies.
3. Correlation
The Pandas module also allows us to calculate the relationship between each column in your data set using corr(). By setting numeric_only = True, it ignores all non-numerical columns.