Tkinter Pandas Data Cleaning: CSV to SQLite

Q: How can I find missing values in a Pandas DataFrame?

Use df.isna() to identify missing cells and combine it with sum() to count them by column or across the complete DataFrame.

Q: How do I remove duplicate DataFrame rows?

Use duplicated() to count duplicate rows and drop_duplicates() to remove them.

Cleaning CSV data using Pandas through a Tkinter interface before saving to SQLite

This project combines Tkinter, Pandas and SQLite to create an interactive CSV data-cleaning application.

The user selects a CSV file with the Tkinter file browser. Pandas read_csv() creates a DataFrame, and the interface can then handle missing values, remove duplicate records and save the cleaned DataFrame to SQLite.

CSV file
    |
    v
Pandas DataFrame
    |
    +--> inspect missing values
    |
    +--> fill or drop missing values
    |
    +--> remove duplicates
    |
    +--> preview cleaned data
    |
    v
Cleaned DataFrame
    |
    v
SQLite table

Original file: the cleaning operations on this page modify the DataFrame held in memory. They do not modify the source CSV file.

Sample CSV with Missing Values and Duplicate Rows

Show Table of Contents

Recommended Cleaning Workflow
Load CSV Data
Inspect the DataFrame
Handle Missing Values
Fill Numeric Missing Values
Fill Text Missing Values
Drop Rows with Missing Values
Remove Duplicate Rows
Reset Cleaning Changes
Save Cleaned Data to SQLite
Complete Cleaning Application
Video Tutorial
Why Cleaning Order Matters
Continue the Project
FAQs

Recommended CSV Cleaning Workflow Top ↑

Data cleaning should normally begin with inspection rather than immediately changing values.

Load
  |
  v
Inspect rows and columns
  |
  v
Count missing values
  |
  v
Choose a missing-value strategy
  |
  v
Check duplicates
  |
  v
Remove duplicates
  |
  v
Review cleaned result
  |
  v
Save

The application therefore shows a summary after each cleaning operation:

number of rows;
number of columns;
total missing cells;
number of duplicate rows;
missing values in each column;
first ten DataFrame rows.

Load CSV Data into a Pandas DataFrame Top ↑

Select the CSV file through a Tkinter file dialog:

file_path=filedialog.askopenfilename(
    title='Select CSV file',
    filetypes=[
        ('CSV files','*.csv'),
        ('All files','*.*')
    ]
)

Create the DataFrame:

new_df=pd.read_csv(
    file_path
)

Two copies are maintained:

original_df=new_df.copy()
df=new_df.copy()

original_df remains unchanged while df is cleaned. This lets the user reset the application without reopening the CSV file.

Inspect Missing Values and Duplicate Rows Top ↑

The total number of missing cells is:

missing_count=int(
    df.isna().sum().sum()
)

Count duplicate rows using duplicated():

duplicate_count=int(
    df.duplicated().sum()
)

Missing values for individual columns can be inspected with:

df.isna().sum()

A useful summary can therefore look like:

Rows: 35
Columns: 5
Missing cells: 4
Duplicate rows: 2

MISSING VALUES BY COLUMN

id        0
name      1
class     1
mark      2
gender    0

Choose How to Handle Missing Values Top ↑

The original application offered only two choices:

Fill every missing value with 0
or
Drop every row containing a missing value

A single replacement value is not always appropriate for different DataFrame columns. The revised interface uses a read-only Combobox with three strategies:

Fill numeric missing values with 0
Fill text missing values with Unknown
Drop rows containing missing values

The selected strategy is applied only when the user clicks Apply Missing Values.

Fill Missing Numeric Values with 0 Top ↑

First select only numeric columns:

numeric_columns=df.select_dtypes(
    include='number'
).columns

Then use fillna() only on those columns:

df[numeric_columns]=df[
    numeric_columns
].fillna(
    0
)

For example:

mark

75
NaN
82
NaN

becomes:

mark

75
0
82
0

Whether zero is a valid replacement depends on the meaning of the column. In some datasets, a missing value means "unknown" rather than zero.

Fill Missing Text Values with Unknown Top ↑

Non-numeric columns can be handled separately:

text_columns=df.select_dtypes(
    exclude='number'
).columns

Each selected column can replace missing values with descriptive text:

for column in text_columns:
    df[column]=df[column].astype(
        'object'
    ).fillna(
        'Unknown'
    )

For example:

class

Four
NaN
Three

becomes:

class

Four
Unknown
Three

Drop Rows Containing Missing Values Top ↑

Another option is to remove every row containing at least one missing value:

df=df.dropna().reset_index(
    drop=True
)

If the DataFrame contains 35 rows before the operation and 4 rows contain missing values, the result may contain 31 rows.

Dropping rows is appropriate only when losing those records is acceptable. A dataset with many missing values can lose a significant amount of information.

Find and Remove Duplicate Rows Top ↑

Count duplicate rows:

duplicates=int(
    df.duplicated().sum()
)

Remove exact duplicate rows using drop_duplicates():

df=df.drop_duplicates().reset_index(
    drop=True
)

The application reports the number removed:

Removed 3 duplicate row(s).

What Counts as a Duplicate? Top ↑

With no subset argument, Pandas compares all columns.

These rows are duplicates:

1,John,Four,75
1,John,Four,75

These are not exact duplicates:

1,John,Four,75
1,John,Four,80

Even though the same student ID appears, one value differs.

Reset All Cleaning Changes Top ↑

Because the originally loaded DataFrame is preserved:

original_df

all cleaning operations can be reversed during the current session:

df=original_df.copy()

This is useful while experimenting with different cleaning strategies.

Save the Cleaned DataFrame to SQLite Top ↑

After reviewing the cleaned DataFrame, the user can choose an SQLite database and table name.

The table name is validated before use:

def valid_table_name(table_name):
    return bool(
        re.fullmatch(
            r'[A-Za-z_][A-Za-z0-9_]*',
            table_name
        )
    )

A SQLAlchemy Engine is created:

engine=create_engine(
    'sqlite:///'+db_path
)

If the requested table already exists, the application asks before replacing it.

The cleaned DataFrame is then saved with:

df.to_sql(
    name=table_name,
    con=engine,
    if_exists=mode,
    index=False
)

See our Pandas to_sql() examples for more on transferring DataFrame records to databases.

Complete Tkinter Pandas Data Cleaning Application Top ↑

import os
import re
import tkinter as tk
from tkinter import filedialog
from tkinter import messagebox
from tkinter import ttk

import pandas as pd

from sqlalchemy import create_engine
from sqlalchemy import inspect
from sqlalchemy.exc import SQLAlchemyError

df=None
original_df=None

root=tk.Tk()
root.title('CSV Data Cleaning - plus2net')
root.geometry('850x650')
root.minsize(680,520)

root.columnconfigure(0,weight=1)
root.rowconfigure(4,weight=1)

file_var=tk.StringVar(value='No CSV file selected')
summary_var=tk.StringVar(value='Rows: 0 | Columns: 0 | Missing: 0 | Duplicates: 0')
status_var=tk.StringVar(value='Select a CSV file.')
table_var=tk.StringVar(value='cleaned_data')

missing_var=tk.StringVar(value='Fill numeric missing values with 0')

missing_options=[
    'Fill numeric missing values with 0',
    'Fill text missing values with Unknown',
    'Drop rows containing missing values'
]

def valid_table_name(table_name):
    return bool(re.fullmatch(r'[A-Za-z_][A-Za-z0-9_]*',table_name))

def set_action_buttons(state):
    missing_button.config(state=state)
    duplicate_button.config(state=state)
    reset_button.config(state=state)
    save_button.config(state=state)

def refresh_preview():
    output.delete('1.0',tk.END)

if df is None:
        summary_var.set('Rows: 0 | Columns: 0 | Missing: 0 | Duplicates: 0')
        return

missing_count=int(df.isna().sum().sum())
    duplicate_count=int(df.duplicated().sum())

summary_var.set(
        f'Rows: {df.shape[0]} | Columns: {df.shape[1]} | '
        f'Missing: {missing_count} | Duplicates: {duplicate_count}'
    )

missing_by_column=df.isna().sum()
    missing_by_column=missing_by_column[missing_by_column>0]

if missing_by_column.empty:
        missing_text='No missing values'
    else:
        missing_text=missing_by_column.to_string()

preview=df.head(10).to_string(index=False)

text=(
        'DATA SUMMARY\n\n'
        f'Rows: {df.shape[0]}\n'
        f'Columns: {df.shape[1]}\n'
        f'Missing cells: {missing_count}\n'
        f'Duplicate rows: {duplicate_count}\n\n'
        'MISSING VALUES BY COLUMN\n\n'
        f'{missing_text}\n\n'
        'DATA TYPES\n\n'
        f'{df.dtypes.to_string()}\n\n'
        'FIRST 10 ROWS\n\n'
        f'{preview}'
    )

output.insert(tk.END,text)

def load_csv():
    global df,original_df

file_path=filedialog.askopenfilename(
        title='Select CSV file',
        filetypes=[('CSV files','*.csv'),('All files','*.*')]
    )

if not file_path:
        status_var.set('No CSV file selected.')
        return

try:
        new_df=pd.read_csv(file_path)
    except (
        OSError,
        UnicodeDecodeError,
        pd.errors.ParserError,
        pd.errors.EmptyDataError
    ) as e:
        status_var.set(f'Unable to read CSV: {e}')
        return

original_df=new_df.copy()
    df=new_df.copy()

file_var.set(file_path)
    refresh_preview()
    set_action_buttons(tk.NORMAL)

status_var.set(
        f'Loaded {os.path.basename(file_path)} with '
        f'{df.shape[0]} row(s) and {df.shape[1]} column(s).'
    )

def handle_missing_values():
    global df

if df is None:
        status_var.set('Load a CSV file first.')
        return

option=missing_var.get()
    before_missing=int(df.isna().sum().sum())
    before_rows=len(df)

try:
        if option=='Fill numeric missing values with 0':
            numeric_columns=df.select_dtypes(include='number').columns
            df[numeric_columns]=df[numeric_columns].fillna(0)
            action='Filled missing values in numeric columns with 0.'

elif option=='Fill text missing values with Unknown':
            text_columns=df.select_dtypes(exclude='number').columns

for column in text_columns:
                df[column]=df[column].astype('object').fillna('Unknown')

action='Filled missing values in non-numeric columns with Unknown.'

elif option=='Drop rows containing missing values':
            df=df.dropna().reset_index(drop=True)
            removed=before_rows-len(df)
            action=f'Dropped {removed} row(s) containing missing values.'

else:
            status_var.set('Select a missing-value strategy.')
            return

except Exception as e:
        status_var.set(f'Unable to clean missing values: {e}')
        return

after_missing=int(df.isna().sum().sum())

refresh_preview()
    status_var.set(
        f'{action} Missing cells: {before_missing} -> {after_missing}.'
    )

def remove_duplicates():
    global df

if df is None:
        status_var.set('Load a CSV file first.')
        return

duplicates=int(df.duplicated().sum())

if duplicates==0:
        status_var.set('No duplicate rows found.')
        return

df=df.drop_duplicates().reset_index(drop=True)

refresh_preview()
    status_var.set(f'Removed {duplicates} duplicate row(s).')

def reset_data():
    global df

if original_df is None:
        status_var.set('No original DataFrame is available.')
        return

df=original_df.copy()

refresh_preview()
    status_var.set('All cleaning changes were reset.')

def save_to_sqlite():
    if df is None:
        status_var.set('Load and clean a CSV file first.')
        return

table_name=table_var.get().strip()

if not table_name:
        status_var.set('Enter an SQLite table name.')
        return

if not valid_table_name(table_name):
        status_var.set(
            'Table name must start with a letter or underscore and '
            'contain only letters, digits and underscores.'
        )
        return

db_path=filedialog.asksaveasfilename(
        title='Select or create SQLite database',
        defaultextension='.db',
        filetypes=[('SQLite database','*.db'),('All files','*.*')]
    )

if not db_path:
        status_var.set('SQLite save cancelled.')
        return

engine=None

try:
        engine=create_engine('sqlite:///'+db_path)
        inspector=inspect(engine)

if inspector.has_table(table_name):
            replace=messagebox.askyesno(
                'Replace existing table?',
                f'Table "{table_name}" already exists.\n\n'
                'Replace the existing table and its data?'
            )

if not replace:
                status_var.set('SQLite save cancelled.')
                return

mode='replace'
        else:
            mode='fail'

df.to_sql(
            name=table_name,
            con=engine,
            if_exists=mode,
            index=False
        )

status_var.set(
            f'Saved {len(df)} cleaned row(s) to table '
            f'{table_name} in {os.path.basename(db_path)}.'
        )

except (SQLAlchemyError,ValueError) as e:
        original=getattr(e,'orig',e)
        status_var.set(f'Unable to save to SQLite: {original}')

finally:
        if engine is not None:
            engine.dispose()

top_frame=ttk.Frame(root,padding=10)
top_frame.grid(row=0,column=0,sticky='ew')
top_frame.columnconfigure(1,weight=1)

ttk.Button(top_frame,text='Load CSV',command=load_csv).grid(row=0,column=0,padx=(0,10))
ttk.Label(top_frame,textvariable=file_var,wraplength=690).grid(row=0,column=1,sticky='w')

clean_frame=ttk.LabelFrame(root,text='Cleaning Operations',padding=10)
clean_frame.grid(row=1,column=0,sticky='ew',padx=10,pady=5)
clean_frame.columnconfigure(0,weight=1)

missing_combo=ttk.Combobox(
    clean_frame,
    textvariable=missing_var,
    values=missing_options,
    state='readonly',
    width=40
)
missing_combo.grid(row=0,column=0,padx=5,pady=3,sticky='w')

missing_button=ttk.Button(
    clean_frame,
    text='Apply Missing Values',
    command=handle_missing_values,
    state=tk.DISABLED
)
missing_button.grid(row=0,column=1,padx=5)

duplicate_button=ttk.Button(
    clean_frame,
    text='Remove Duplicates',
    command=remove_duplicates,
    state=tk.DISABLED
)
duplicate_button.grid(row=0,column=2,padx=5)

reset_button=ttk.Button(
    clean_frame,
    text='Reset Data',
    command=reset_data,
    state=tk.DISABLED
)
reset_button.grid(row=0,column=3,padx=5)

save_frame=ttk.Frame(root,padding=(10,5))
save_frame.grid(row=2,column=0,sticky='ew')

ttk.Label(save_frame,text='SQLite table:').grid(row=0,column=0,padx=(0,5))
ttk.Entry(save_frame,textvariable=table_var,width=25).grid(row=0,column=1,padx=5)

save_button=ttk.Button(
    save_frame,
    text='Save Cleaned Data to SQLite',
    command=save_to_sqlite,
    state=tk.DISABLED
)
save_button.grid(row=0,column=2,padx=10)

ttk.Label(
    root,
    textvariable=summary_var,
    font=('Arial',10,'bold')
).grid(row=3,column=0,sticky='w',padx=10,pady=(5,0))

output_frame=ttk.Frame(root,padding=(10,5))
output_frame.grid(row=4,column=0,sticky='nsew')
output_frame.columnconfigure(0,weight=1)
output_frame.rowconfigure(0,weight=1)

output=tk.Text(output_frame,wrap='none')
output.grid(row=0,column=0,sticky='nsew')

y_scroll=ttk.Scrollbar(output_frame,orient='vertical',command=output.yview)
y_scroll.grid(row=0,column=1,sticky='ns')

x_scroll=ttk.Scrollbar(output_frame,orient='horizontal',command=output.xview)
x_scroll.grid(row=1,column=0,sticky='ew')

output.configure(yscrollcommand=y_scroll.set,xscrollcommand=x_scroll.set)

ttk.Label(
    root,
    textvariable=status_var,
    wraplength=800
).grid(row=5,column=0,sticky='w',padx=10,pady=(5,10))

root.mainloop()

Video: CSV Cleaning with Tkinter, Pandas and SQLite Top ↑

CSV Data Upload with Tkinter and Pandas to Save Cleaned Data in SQLite

Why the Order of Data Cleaning Operations Matters Top ↑

The final result can depend on which cleaning operation is performed first.

Consider:

name | mark
John | NaN
John | NaN

Initially these rows are duplicates.

If we first remove duplicates:

John | NaN

and then fill the missing mark:

John | 0

we retain one row.

A practical sequence is usually:

Inspect
   |
   v
Handle missing values
   |
   v
Normalize / validate values
   |
   v
Remove duplicates
   |
   v
Review
   |
   v
Save

However, the correct sequence depends on what the dataset represents.

Why Keep original_df and df Separately? Top ↑

The application uses:

original_df
df

original_df represents the CSV immediately after loading. df is the working copy.

CSV
 |
 v
original_df
 |
 +------------------+
 |                  |
 v                  |
df --> cleaning     |
 |                  |
 +--> Reset --------+

This is safer for an interactive cleaning application because the user can try different strategies without reopening the file.

Why Filling Every Column with 0 Can Be Misleading Top ↑

The original example used:

df.fillna(0)

Suppose the DataFrame contains:

name   class   mark
John   Four     75
NaN    Three    80
Max    NaN      NaN

filling everything with zero can create:

name   class   mark
John   Four     75
0      Three    80
Max    0         0

The numeric replacement may be meaningful for some columns, but name=0 and class=0 usually have a different meaning.

The revised application therefore separates numeric and non-numeric handling.

Cleaning Is Not the Same as Validation Top ↑

This project handles two common cleaning tasks:

missing values;
duplicate rows.

A real dataset may also need validation such as:

checking acceptable numeric ranges;
standardizing text capitalization;
converting columns to intended data types;
validating dates;
checking required fields;
detecting invalid category values.

These rules depend on the meaning of the dataset, so they should not be applied automatically without knowing the data requirements.

Continue the Tkinter Pandas Data Workflow Top ↑

Create SQLite columns dynamically from CSV structure:

CSV to SQLite Dynamic Schema

Import large files with progress tracking:

CSV to SQLite with Progressbar

Export stored SQLite records back to CSV:

SQLite to CSV using Pandas

The next project moves from cleaning into data analysis:

Pandas Data Analysis with Tkinter

Frequently Asked Questions Top ↑

Q1: How can I find missing values in a Pandas DataFrame?

Use df.isna() to identify missing cells and combine it with sum() to count them by column or across the complete DataFrame.

Q2: Should all missing values be replaced with zero?

No. Zero can be appropriate for some numeric columns, but text, category, date and other fields may require a different replacement or should remain missing.

Q3: How do I remove duplicate DataFrame rows?

Use duplicated() to count duplicate rows and drop_duplicates() to remove them.

Q4: Does this application change the original CSV?

No. Cleaning is applied to an in-memory working DataFrame. The source CSV remains unchanged.

Q5: What does Reset Data do?

It replaces the current working DataFrame with a fresh copy of the DataFrame created when the CSV was loaded.

Q6: What happens if the SQLite table already exists?

The application asks for confirmation before replacing the existing table and its records.

Q7: Is removing missing values and duplicates enough to clean every dataset?

No. Other datasets may require type conversion, range validation, text normalization, date validation, category checks and other domain-specific rules.

Dynamic SQLite Schema Data Analysis

Tkinter Projects Tkinter Pandas Projects

Python Tkinter Pandas Data Cleaning fillna() drop_duplicates() SQLite Python Tutorials

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: