Cleaning Data

Question 1

5 steps of data analysis

Answer 1

S1. Ask questions
S2. Wrangle data - gather, assess, clean
S3. Perform EDA (exploratory data analysis)
S4. Draw conclusions
S5. Communicate your results

Question 2

parts of .read_csv()

Answer 2

filepath = filepath of the csv
header = header of the CSV file
index_col = which columns should be index
sep = separator

ex.
labels = [‘id’,’name’,’scores’]
df = pd.read_csv(‘scores.csv’, sep=’;’, header=None, names=labels, index_col=’Name’)

Question 3

.shape

Answer 3

returns the overall size/shape of the dataframe: (462, 32)

Question 4

.dtypes

Answer 4

return each datatype of the columns

Question 5

.info()

Answer 5

returns
column, non-null count, and Dtype

Question 6

.nunique()

Answer 6

returns number of unique values for each column

Question 7

.describe()

Answer 7

returns summary statistics: count, mean, std, min, max, Q1 and Q3

Question 8

.head()

Answer 8

returns first few lines of Dataframe
df.head(5)

Question 9

.tail()

Answer 9

returns last few lines of Dataframe
df.tail(2)

Question 10

A. .iloc
B. .iloc[0]
C. .iloc[0:5]
D. .iloc[:,1]
E. .iloc[0:5,1:3]
F. radi_means = .iloc[0:3,2]

Answer 10

A. a method in pandas used for integer-location based on indexing
B. Selects the first row
C. Selects the first five rows
D. Selects the second column (index 1)
E. Selects the first five rows and the second and third columns
F. Outputs first three rows of column three.

Question 11

A. .loc
B. .loc[‘row_index’]
C. .loc[[‘row_label’,’row_label2’]]
D. .loc[:, ‘column_label’]
E. .loc[‘row_label1’:’row_label3’,[‘col_lab1’,’column_lab2’]]

Answer 11

A. A method in pandas used for label-based indexing.
B. Selects the row with the specified label
C. Selects multiple rows
D. Selects all rows for the specified column
E. Selects a range of rows and specific columns

Question 12

A. Get the diagnosis of the patient with id 842302
B. Get the radius_mean for patients with id 842302 and 842517

Answer 12

A. diagnosis_patient = df.loc[df[‘id’]==842302,’diagnosis’]
B. radius_means = df.loc[df[‘id’].isin([842302,842517]), ‘radius_mean’] #Output

Question 13

Visualize how to create a subset of a dataframe

Answer 13

make a copy of the data frame with relevant columns

copy_df = df[[‘col_1’,’col_2’,’col_3’,col_4’]]

subset = df[df[‘col_1’] == ‘data’]

Question 13

Visualize how to use subsets in dropping duplicates, filling NaNs, and .isin() method.

Answer 13

A. Dropping duplicates
unique_df = df.drop_duplicates(subset=[‘col1’, ‘col2’])
B. Fill missing values
df.fillna(value=0, subset=[‘col3’], inplace=True)
C. Using pd.DataFrame.isin()
filtered_df = df[df[‘col1’].isin([‘data1’,’data2’])]

Question 14

Fill null values in your DataFrame with values

Answer 14

.fillna()

ex.
1. df[‘col1’] = df[‘col1’].fillna(pd.to_datetime(‘1900-01-01’)
2. mean_date = df[‘col1’].mean()
df[‘col1’] = df[‘col1’].fillna(mean_date)

Question 15

A. Using .query() to discover null values in rows of a column
B. Using .query() for all rows with null values

Answer 15

A.
1. df_null = df[df[‘col1’].isnull()]
2. df_null = df.query(‘col1.isnull()’)
B.
df_null = df[df.isnull().any(axis=1)]

Question 16

Clean data

Answer 16

A. .duplicated() - True or false for rows that are duplicates = df.duplicated() and then .drop_duplicates(): df = df.drop_duplicates()
B. Converting to right dtypes: .to_datetime(): df[‘time’] = pd.to_datetime(df[‘time’])
C. .isnull(): df[‘col’].isnull()
D. .fillna() : df[‘col’] = df[‘col’].fillna(mean)
E. Correcting column names
F. Merging dataframes
G. Saving the cleaned version of the dataframes.

Question 17

Drop extraneous columns

Answer 17

confirm changes

df_copy = df.drop([‘col1’,’col2’,’col3’],axis=1, errors=’ignore’)

df_copy.head(2)

axis=0: refers to rows, axis=1: refers to columns

Question 18

A. Renaming columns with a library
B. renaming columns with a lambda function

Answer 18

A. df.rename(columns={‘Col 1’: ‘col_1’, ‘Col 2’: ‘col_2’, ‘Col 3’:’col_3’ })
B. df.rename(columns=lambda x: x.strip().lower().replace(“ “, “_”), inplace=True)

Question 19

Visualize a lambda function that renames columns by removing trailing spaces, converting characters to lowercase, and replacing spaces with underscores

Answer 19

df.rename(columns=lambda x: x.strip().lower().replace(“ “,”_”), inplace=True)

Question 20

Visualize panda query examples
Example A
Example B
Example C
Example D

Answer 20

With brackets
A. df = df[df[“col1”] == “data”]
C. df = df[df.col1 < 3]
With .query()
B. df = df.query(“col1” == “data”’)
D. df = df.query(“col1 < 3”)

Question 21

A. Visualize how to count total null values across all columns
B. Visualize how to get null values in each column

Answer 21

A. total_nulls = df.isnull().sum().sum()
print(total_nulls)
B. null_col_counts = df.isnull().sum()
print(null_col_counts)

Question 22

Visualize an if statement that drops rows with null values

Answer 22

if df.isnull().any().any():
df_cleaned = df[~df.isnull().any(axis=1)]
print(“Rows with null values have been dropped”)
else:
df_cleaned = df
print(“No null values found.”)

Question 23

Visualize how to count duplicates and then drop them.

Answer 23

A. df.duplicated().sum()
B. df = df.drop_duplicates()

Question 24

How do you save a dataframe?

Answer 24

df.to_csv(‘filepath/filename.csv’, index=False)

Question 25

Name the pandas data types

Answer 25

Objects, integers, floats, booleans, datetimes, and categorical

Question 26

Visualize the two main ways to convert dtypes

Answer 26

A.df[‘col1’] = df[‘col1’].astype(‘int32’)
B. df = df.astype({‘col1’: ‘int32’, ‘col2’: ‘int32’, ‘col3’: ‘object’})

Question 27

What is Regex or Regular Expressions?

Answer 27

A tool for pattern matching in strings that allows you to or replace parts of strings based on defined rules. Some basic components are: ., *, +, ?, [], (), \d, \w, \s

Question 28

Visualize the example of using a lambda function to split column data when it appears like 18/25

Answer 28

Split the values into separate rows

Original DataFrame
data = {‘city_mpg’: [‘13/18’, ‘19/25’], ‘hwy_mpg’: [‘18/22’, ‘25/30’]}
split_columns = [‘city_mpg’, ‘hwy_mpg’]
for col in split_columns:
df1[col] = df1[col].apply(lambda x: x.split(“/”)[0] if isinstance(x, str) else x)
df2[col] = df2[col].apply(lambda x: x.split(“/”)[1] if isinstance(x, str) and len(x.split(“/”)) > 1 else x)
#Concatenate df1 and df2 into a single DataFrame
expanded_df = pd.concat([df1, df2], ignore_index=True)

Question 29

Visualize how to use a for loop to change dtypes in multiple columns.

Answer 29

columns = [‘col1’, ‘col2’, ‘col3’]
for x in columns:
df[x] = df[x].astype(‘dtype’)

Question 30

Visualize how to use a function to change dtypes in multiple dataframes and columns

Answer 30

def change_column_dtype(dataframes, columns, dtype):
for df in dataframes:
for col in columns:
df[col] = df[col].astype(dtype)

Usage
columns = [‘col1’, ‘col2, ‘col3’]
change_column_dtype([df1, df2], columns, ‘float’)

Question 31

Visualize how to optimize data types

Answer 31

df[‘col1’].astype(“int8”)

Question 32

When should you use categorical data types for data optimization?

Answer 32

When you have object columns with a low number of unique values.

ex.
df[‘col1’].astype(“category”)

Question 33

What is the categorical data type?

Answer 33

Data that is limited with a fixed number of possible values. Categorical data is particularly useful when you have variables that represent groups such as gender, color, or type of vehicle.

Question 34

What is pd.concat([df1,df2])?

Answer 34

Primarily used to stack DataFrames either vertically (rows) or horizontally (columns), combining them by axis. It’s often used when you want to extend a DataFrame by adding rows or columns from another DataFrame without complex merging logic.

result = pd.concat([df1, df2], axis=0, ignore_index=True)

Question 35

What is pd.merge()

Answer 35

Primarily used for database-style joins (similar to SQL joins), which means merging DataFrames based on one or more common columns or indices. This is useful for combining related data with specific relationships, such as inner, outer, left, and right joins.

result = pd.merge(df1, df2, on=’key’, how=’inner’)

Question 36

How does inner_merge = pd.merge(df1, df2, on=’key’, how=’inner’) work?

Answer 36

Keeps only rows where keys are present in both DataFrames.

Question 37

Visualize outer_merge = pd.merge(df1, df2, on=’key’, how=’outer’)

Answer 37

Keeps all rows from both DataFrames, filling with NaN for missing values where no match is found.

Question 38

Visualize left_merge = pd.merge(df1, df2, on=’key’, how=’left’)

Answer 38

Keeps all rows from the left DataFrame, with matches from the right DataFrame where available. Non-matching entries from the right df2 are NAN

Question 39

Visualize right_merge = pd.merge(df1, df2, on=’key’, how=’right’)

Answer 39

Keeps all rows from the right DataFrame, with matches from the left DataFrame where available. Non-matching entries from the left df1 are NAN

Question 40

Visualize how to use pd.merge() on index

Answer 40

pd.merge(df1, df2, left_index=True, right_index=True, how=”inner”)

Question 41

Visualize how to calculate the mean mpg for the below DataFrame

data = {
‘model’: [‘Car A’, ‘Car B’, ‘Car A’, ‘Car B’, ‘Car C’],
‘cmb_mpg_2008’: [25, 30, 25, 30, 20],
‘cmb_mpg’: [30, 35, 32, 36, 25]
}

Answer 41

model_mpg = df_combined.groupby(‘model’).agg(
mean_cmb_mpg_2008=(‘cmb_mpg_2008’, ‘mean’),
mean_cmb_mpg=(‘cmb_mpg’, ‘mean’).reset_index()
groupby(‘model’): groups the dataframe by the model column
agg(): Calculates the mean for cmb_mpg_2008 and cmb_mpg.
Using agg, you can apply different aggregation functions (like sum, median, etc.) to different columns if needed, adding flexibility for additional metrics
reset_index(): Resets the index of the resulting DF to make it easier to work with.

Question 42

A. Visualize how to aggregate totals on multiple columns in a dataframe
B. Visualize how to aggregate totals using groupby.
C. Visualize how to sort values after aggregating with groupby.

Answer 42

A. df[[‘col1’,’col2’]].sum()
B. df.groupby(‘col’).sum(numeric_only=True)
C. df.groupby(‘col’).sum(numeric_only=True).sort_values(by=”col”, ascending=False)

Question 43

What .reset_index()

Answer 43

After merging a dataframe, this is used to convert the index levels back so that you can use standard DataFrame column access methods and maintain consistency.