Missing values and MySQL

The essentials

Real data often has gaps. Pandas marks missing values as NaN (Not a Number). Before analysis, missing values must be found and then either removed or filled. Pandas can also read data from and write data to a MySQL database.

Key points

• Finding: df.isnull() returns True where a value is missing; df.isnull().sum() counts missing values per column; df.isnull().any() tells whether a column has any.
• Dropping: df.dropna() removes every row that has at least one NaN. df.dropna(how='all') removes only rows where all values are NaN. df.dropna(axis=1) removes columns with NaN.
• Filling: df.fillna(0) replaces NaN with 0; df.fillna(value) can also use the column mean or another estimate. Filling keeps the number of rows unchanged but changes statistics such as the mean.
• Example: for [10, NaN, 30, 40], mean() is about 26.67, but after fillna(0) it becomes 20.0.
• Pandas and MySQL: a connection is made with a connector module (such as pymysql or mysql.connector) or through SQLAlchemy's create_engine().
• Reading: pd.read_sql('SELECT * FROM student', con) runs a query and returns the result as a DataFrame. read_sql_query() and read_sql_table() are related functions.
• Writing: df.to_sql('student', con, if_exists='append', index=False) stores the DataFrame as a table. if_exists can be 'fail' (default), 'replace' or 'append'.

Exam tips

• Count rows before and after dropna(): any row with a single NaN disappears.
• Know the three values of if_exists and what each does.

Common mistakes

• Thinking dropna() removes only the NaN cell; it removes the whole row (or column).
• Assuming fillna() changes the DataFrame in place; it returns a new object unless inplace=True or the result is assigned.