Python Data Analysis with Pandas Functions

Writing Parquet Files in Python with Pandas, PySpark, and Koalas

This notebook demonstrates selected Pandas functions. The text and code ideas below are selectively obtained from an article by Aryan Garg in the KDnuggets Newsletter.

List of Built-in Datasets from seaborn

import seaborn as sns
# Print without brackets and quotes:
dataset = sns.get_dataset_names()
print(", ".join(datasets))

anagrams, anscombe, attention, brain_networks, car_crashes, diamonds, dots, dowjones, exercise, flights, fmri, geyser, glue, healthexp, iris, mpg, penguins, planets, seaice, taxis, tips, titanic

import seaborn as sns
# Print the dataset names as a vertical list
datasets = list(sns.get_dataset_names())
for i, name in enumerate(datasets, 1):
    print(f"{i}. {name}")

A. Data Viewing

1.	df.head() displays the first five rows of the sample data.
2.	df.tail() displays the last five rows of the sample data.
3.	df.sample(n) displays the random n number of rows in the sample data.
4.	df.shape displays the sample data's rows and columns (dimensions).

# import seaborn as sns
df = sns.load_dataset("titanic")
df.head()

	survived	pclass	sex	age	sibsp	parch	fare	embarked	class	who	adult_male	deck	embark_town	alive	alone
0	0	3	male	22.0	1	0	7.2500	S	Third	man	True	NaN	Southampton	no	False
1	1	1	female	38.0	1	0	71.2833	C	First	woman	False	C	Cherbourg	yes	False
2	1	3	female	26.0	0	0	7.9250	S	Third	woman	False	NaN	Southampton	yes	True
3	1	1	female	35.0	1	0	53.1000	S	First	woman	False	C	Southampton	yes	False
4	0	3	male	35.0	0	0	8.0500	S	Third	man	True	NaN	Southampton	no	True

df.tail()

	survived	pclass	sex	age	sibsp	parch	fare	embarked	class	who	adult_male	deck	embark_town	alive	alone
886	0	2	male	27.0	0	0	13.00	S	Second	man	True	NaN	Southampton	no	True
887	1	1	female	19.0	0	0	30.00	S	First	woman	False	B	Southampton	yes	True
888	0	3	female	NaN	1	2	23.45	S	Third	woman	False	NaN	Southampton	no	False
889	1	1	male	26.0	0	0	30.00	C	First	man	True	C	Cherbourg	yes	True
890	0	3	male	32.0	0	0	7.75	Q	Third	man	True	NaN	Queenstown	no	True

df.sample(10)

	survived	pclass	sex	age	sibsp	parch	fare	embarked	class	who	adult_male	deck	embark_town	alive	alone
664	1	3	male	20.0	1	0	7.9250	S	Third	man	True	NaN	Southampton	yes	False
106	1	3	female	21.0	0	0	7.6500	S	Third	woman	False	NaN	Southampton	yes	True
494	0	3	male	21.0	0	0	8.0500	S	Third	man	True	NaN	Southampton	no	True
511	0	3	male	NaN	0	0	8.0500	S	Third	man	True	NaN	Southampton	no	True
623	0	3	male	21.0	0	0	7.8542	S	Third	man	True	NaN	Southampton	no	True
555	0	1	male	62.0	0	0	26.5500	S	First	man	True	NaN	Southampton	no	True
855	1	3	female	18.0	0	1	9.3500	S	Third	woman	False	NaN	Southampton	yes	False
127	1	3	male	24.0	0	0	7.1417	S	Third	man	True	NaN	Southampton	yes	True
304	0	3	male	NaN	0	0	8.0500	S	Third	man	True	NaN	Southampton	no	True
282	0	3	male	16.0	0	0	9.5000	S	Third	man	True	NaN	Southampton	no	True

df.shape

(891, 15)

B. Statistics

1. df.describe() provides the basic statistics of each column of the sample data
2. df.info() provides information about the various data types used and the non-null count of each column.
3. df.corr(numeric_only=True) gives you the correlation matrix between all numeric columns in the data frame.
4. df.memory_usage() tells you how much memory is being consumed by each column.

df.describe()

	survived	pclass	age	sibsp	parch	fare
count	891.000000	891.000000	714.000000	891.000000	891.000000	891.000000
mean	0.383838	2.308642	29.699118	0.523008	0.381594	32.204208
std	0.486592	0.836071	14.526497	1.102743	0.806057	49.693429
min	0.000000	1.000000	0.420000	0.000000	0.000000	0.000000
25%	0.000000	2.000000	20.125000	0.000000	0.000000	7.910400
50%	0.000000	3.000000	28.000000	0.000000	0.000000	14.454200
75%	1.000000	3.000000	38.000000	1.000000	0.000000	31.000000
max	1.000000	3.000000	80.000000	8.000000	6.000000	512.329200

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 891 entries, 0 to 890
Data columns (total 15 columns):
 #   Column       Non-Null Count  Dtype   
---  ------       --------------  -----   
 0   survived     891 non-null    int64   
 1   pclass       891 non-null    int64   
 2   sex          891 non-null    object  
 3   age          714 non-null    float64 
 4   sibsp        891 non-null    int64   
 5   parch        891 non-null    int64   
 6   fare         891 non-null    float64 
 7   embarked     889 non-null    object  
 8   class        891 non-null    category
 9   who          891 non-null    object  
 10  adult_male   891 non-null    bool    
 11  deck         203 non-null    category
 12  embark_town  889 non-null    object  
 13  alive        891 non-null    object  
 14  alone        891 non-null    bool    
dtypes: bool(2), category(2), float64(2), int64(4), object(5)
memory usage: 80.7+ KB

df.corr(numeric_only=True)

	survived	pclass	age	sibsp	parch	fare	adult_male	alone
survived	1.000000	-0.338481	-0.077221	-0.035322	0.081629	0.257307	-0.557080	-0.203367
pclass	-0.338481	1.000000	-0.369226	0.083081	0.018443	-0.549500	0.094035	0.135207
age	-0.077221	-0.369226	1.000000	-0.308247	-0.189119	0.096067	0.280328	0.198270
sibsp	-0.035322	0.083081	-0.308247	1.000000	0.414838	0.159651	-0.253586	-0.584471
parch	0.081629	0.018443	-0.189119	0.414838	1.000000	0.216225	-0.349943	-0.583398
fare	0.257307	-0.549500	0.096067	0.159651	0.216225	1.000000	-0.182024	-0.271832
adult_male	-0.557080	0.094035	0.280328	-0.253586	-0.349943	-0.182024	1.000000	0.404744
alone	-0.203367	0.135207	0.198270	-0.584471	-0.583398	-0.271832	0.404744	1.000000

df.memory_usage()

Index           132
survived       7128
pclass         7128
sex            7128
age            7128
sibsp          7128
parch          7128
fare           7128
embarked       7128
class          1023
who            7128
adult_male      891
deck           1247
embark_town    7128
alive          7128
alone           891
dtype: int64

C. Data Selection

Filtering Dataframes

1.	df.iloc[row_num] will select a particular row based on its index.
2.	df[col_name] will select the particular column.
3.	df[[‘col1’, ‘col2’]] will select multiple columns given.

df.iloc[1]

survived               1
pclass                 1
sex               female
age                 38.0
sibsp                  1
parch                  0
fare             71.2833
embarked               C
class              First
who                woman
adult_male         False
deck                   C
embark_town    Cherbourg
alive                yes
alone              False
Name: 1, dtype: object

df['embark_town'] 

0      Southampton
1        Cherbourg
2      Southampton
3      Southampton
4      Southampton
          ...     
886    Southampton
887    Southampton
888    Southampton
889      Cherbourg
890     Queenstown
Name: embark_town, Length: 891, dtype: object

df[['embark_town', 'embarked']] 

	embark_town	embarked
0	Southampton	S
1	Cherbourg	C
2	Southampton	S
3	Southampton	S
4	Southampton	S
...	...	...
886	Southampton	S
887	Southampton	S
888	Southampton	S
889	Cherbourg	C
890	Queenstown	Q

891 rows × 2 columns

D. Data Cleaning

1. df.isnull() will identify the missing values in your dataframe.
2.df.dropna() will remove the rows containing missing values in any column.
3. df.fillna(val) will fill the missing values with val given in the argument.
4. df[‘col’].astype(new_data_type) can convert the data type of selected columns to a different data type.
5. wadf[‘col’].astype(new_data_type)can convert the data type of selected columns to a different data type.

df.isnull() checks every cell in the DataFrame for a missing value (NaN). It returns a DataFrame of True and False values:

• True → the value is missing
• False → the value is not missing

df.isnull()

	survived	pclass	sex	age	sibsp	parch	fare	embarked	class	who	adult_male	deck	embark_town	alive	alone
0	False	False	False	False	False	False	False	False	False	False	False	True	False	False	False
1	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False
2	False	False	False	False	False	False	False	False	False	False	False	True	False	False	False
3	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False
4	False	False	False	False	False	False	False	False	False	False	False	True	False	False	False
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
886	False	False	False	False	False	False	False	False	False	False	False	True	False	False	False
887	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False
888	False	False	False	True	False	False	False	False	False	False	False	True	False	False	False
889	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False
890	False	False	False	False	False	False	False	False	False	False	False	True	False	False	False

891 rows × 15 columns

df.dropna() removes rows that contain one or more missing values (NaN) from the DataFrame.

df.dropna()

	survived	pclass	sex	age	sibsp	parch	fare	embarked	class	who	adult_male	deck	embark_town	alive	alone
1	1	1	female	38.0	1	0	71.2833	C	First	woman	False	C	Cherbourg	yes	False
3	1	1	female	35.0	1	0	53.1000	S	First	woman	False	C	Southampton	yes	False
6	0	1	male	54.0	0	0	51.8625	S	First	man	True	E	Southampton	no	True
10	1	3	female	4.0	1	1	16.7000	S	Third	child	False	G	Southampton	yes	False
11	1	1	female	58.0	0	0	26.5500	S	First	woman	False	C	Southampton	yes	True
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
871	1	1	female	47.0	1	1	52.5542	S	First	woman	False	D	Southampton	yes	False
872	0	1	male	33.0	0	0	5.0000	S	First	man	True	B	Southampton	no	True
879	1	1	female	56.0	0	1	83.1583	C	First	woman	False	C	Cherbourg	yes	False
887	1	1	female	19.0	0	0	30.0000	S	First	woman	False	B	Southampton	yes	True
889	1	1	male	26.0	0	0	30.0000	C	First	man	True	C	Cherbourg	yes	True

182 rows × 15 columns

df['survived'].astype(bool)

0      False
1       True
2       True
3       True
4      False
       ...  
886    False
887     True
888    False
889     True
890    False
Name: survived, Length: 891, dtype: bool

E. Data Analysis

1.	Aggregation Functions group a column by its name and then apply some aggregation functions like sum, min/max, mean, etc.
2.	Filtering Data:  You can filter the data in rows based on a specific value or a condition.
3.	Sorting Data: You can sort the data based on a specific column in ascending or descending order.
4.	Pivot Tables: You can create pivot tables that summarize the data using specific columns. This type of tables is very useful in analyzing the data when you only want to consider the effect of particular columns.
5.	Combining Data Frames: We can combine and merge several data frames horizontally or vertically. It will concatenate two data frames and return a single merged data frame.
6.	Applying Custom Functions: Depending on your needs, you can apply custom functions in either a row or a column.
7.	Applymap: We can also apply a custom function to every element of the dataframe in a single line of code. But remember that it applies to all the dataframe elements.

8. Time Series Analysis: In mathematics, time series analysis means analyzing the data collected over a specific time interval, and pandas have functions to perform this type of analysis.
9. Cross Tabulation: We can perform cross-tabulation between two table columns. It is generally a frequency table that shows the frequency of occurrences of various categories. It can help you to understand the distribution of categories across different regions.
10. Handling Outliers: Outliers in data means a particular point goes far beyond the average range.

1. Python aggregation functions

You can group data by one or more columns and then apply functions such as sum(), min(), max(), and mean() to summarize the data.

df.groupby('sex')['fare'].mean()

sex
female    44.479818
male      25.523893
Name: fare, dtype: float64

2. Filtering Data

You can filter rows in a DataFrame based on a specific value or condition.

df[df['sex'] == 'female']

df[df['age'] > 50]

df[(df['sex'] == 'female') & (df['age'] > 60)]

	survived	pclass	sex	age	sibsp	parch	fare	embarked	class	who	adult_male	deck	embark_town	alive	alone
275	1	1	female	63.0	1	0	77.9583	S	First	woman	False	D	Southampton	yes	False
483	1	3	female	63.0	0	0	9.5875	S	Third	woman	False	NaN	Southampton	yes	True
829	1	1	female	62.0	0	0	80.0000	NaN	First	woman	False	B	NaN	yes	True

3a. Sorting Data

You can sort the data based on a specific column in ascending or descending order.

df.sort_values('age')
df.sort_values('age', ascending=False)
df.sort_values(['sex', 'age'])

3b. Filtering and Sorting Data

You can filter rows based on specific conditions and then sort the resulting data by one or more columns.

df[df['age'] > 50].sort_values('age')
df[df['age'] > 50].sort_values('age', ascending=False)

4. Pivot Tables

Use pivot_table() to summarize data by specific columns and apply aggregation functions such as mean(), sum(), and count().

Example: Mean age by sex

df.pivot_table(
    values='age',
    index='sex',
    aggfunc='mean'
)

	age
sex
female	27.915709
male	30.726645

Example: Average fare by sex and passenger class

df.pivot_table(
    values='fare',
    index='sex',
    columns='class',
    aggfunc='mean',
    observed=False
)

class	First	Second	Third
sex
female	106.125798	21.970121	16.118810
male	67.226127	19.741782	12.661633

5. Combining Data Frames

You can combine and merge several data frames horizontally or vertically. It will concatenate two data frames and return a single merged data frame.

• Use pd.concat() to combine DataFrames vertically or horizontally
• Use pd.merge() to combine DataFrames based on a common column or key

Concatenate vertically

Stack DataFrames row by row:

This is useful when df1 and df2 have the same or similar columns.

df_combined = pd.concat([df1, df2], ignore_index=True)

Concatenate horizontally

Place DataFrames side by side:

df_combined = pd.concat([df1, df2], axis=1)

This combines columns from both DataFrames based on their index.

Merge DataFrames

Use merge() when two DataFrames have a common column (key):

df_combined = pd.merge(df1, df2, on='id')

For example:

df_combined = pd.merge(
    df1,
    df2,
    on='id',
    how='inner'
)

Common how options are:

• inner — only matching rows
• left — all rows from df1
• right — all rows from df2
• outer — all rows from both DataFrames

6. Applying Custom Functions

Depending on your needs, you can apply custom functions in either a row or a column.

Example 1: Custom function

def categorize_age(age):
    if age < 18:
        return 'Child'
    elif age < 60:
        return 'Adult'
    else:
        return 'Senior'

df['age_group'] = df['age'].apply(categorize_age)

This creates a new age_group column based on the passenger's age.

Example 2: Using a lambda function

df['fare_double'] = df['fare'].apply(lambda x: x * 2)

This creates a new column containing twice the original fare.

Example 3: Custom function with multiple conditions

def fare_category(fare):
    if fare < 20:
        return 'Low'
    elif fare < 50:
        return 'Medium'
    else:
        return 'High'

df['fare_category'] = df['fare'].apply(fare_category)

A concise description for your lesson:

Applying Custom Functions: Use .apply() to apply a custom function to each value in a pandas Series or DataFrame.

7. Applymap

You can also apply a custom function to every element of the dataframe in a single line of code. But remember that it applies to all the dataframe elements.

 df = df.map(lambda x: str(x).upper())

Important distinction

• Every cell in a DataFrame: df.map(function)
• Every value in one column: df['column'].map(function)
• Every row: df.apply(function, axis=1)
• Every column: df.apply(function, axis=0)

For example, to multiply every numeric value by 2:

df = df.map(lambda x: x * 2 if isinstance(x, (int, float)) else x)

8. Time Series Analysis

In mathematics, time series analysis means analyzing the data collected over a specific time interval, and pandas have functions to perform this type of analysis.

Code yet to be added.

9. Cross Tabulation

You can perform cross-tabulation between two table columns. It is generally a frequency table that shows the frequency of occurrences of various categories. It can help you to understand the distribution of categories across different regions.

Use pd.crosstab() to summarize the relationship between two categorical columns by displaying their frequency counts.

Basic example

Using the Titanic dataset, cross-tabulate sex and survived:

pd.crosstab(df['sex'], df['survived'])

import pandas as pd
pd.crosstab(df['sex'], df['survived'])

survived	0	1
sex
female	81	233
male	468	109

This produces a table showing the number of passengers by sex and survival status.

pd.crosstab(
    df['sex'],
    df['survived'],
    margins=True
)

survived	0	1	All
sex
female	81	233	314
male	468	109	577
All	549	342	891

Cross-tabulation with percentages

To show percentages by row:

pd.crosstab(
    df['sex'],
    df['survived'],
    normalize='index'
)

survived	0	1
sex
female	0.257962	0.742038
male	0.811092	0.188908

10. Handling Outliers

An outlier is a data point that is unusually far from the other observations or falls outside the expected range of the data.

1. Using the IQR method

A common way to detect outliers is the Interquartile Range (IQR) method.

Q1 = df['age'].quantile(0.25)
Q3 = df['age'].quantile(0.75)

IQR = Q3 - Q1

outliers = df[
    (df['age'] < Q1 - 1.5 * IQR) |
    (df['age'] > Q3 + 1.5 * IQR)
]

outliers

	survived	pclass	sex	age	sibsp	parch	fare	embarked	class	who	adult_male	deck	embark_town	alive	alone
33	0	2	male	66.0	0	0	10.5000	S	Second	man	True	NaN	Southampton	no	True
54	0	1	male	65.0	0	1	61.9792	C	First	man	True	B	Cherbourg	no	False
96	0	1	male	71.0	0	0	34.6542	C	First	man	True	A	Cherbourg	no	True
116	0	3	male	70.5	0	0	7.7500	Q	Third	man	True	NaN	Queenstown	no	True
280	0	3	male	65.0	0	0	7.7500	Q	Third	man	True	NaN	Queenstown	no	True
456	0	1	male	65.0	0	0	26.5500	S	First	man	True	E	Southampton	no	True
493	0	1	male	71.0	0	0	49.5042	C	First	man	True	NaN	Cherbourg	no	True
630	1	1	male	80.0	0	0	30.0000	S	First	man	True	A	Southampton	yes	True
672	0	2	male	70.0	0	0	10.5000	S	Second	man	True	NaN	Southampton	no	True
745	0	1	male	70.0	1	1	71.0000	S	First	man	True	B	Southampton	no	False
851	0	3	male	74.0	0	0	7.7750	S	Third	man	True	NaN	Southampton	no	True