Excel VBA vs Python xlwings： Data Preprocessing

Data preprocessing involves handling special data in datasets, including duplicate data, missing values, and outliers. When performing statistical analysis, data often needs to meet specific requirements—if not, data transformation is required (this is also part of data preprocessing).

Data Deduplication

Due to various reasons, duplicate data may exist. Duplicate data can be removed using multiple methods, such as Excel functions, dictionaries, Power Query, and Python.

1. Deduplication Using Excel Functions

Section 13.4.8 introduces using the COUNTIF function to find duplicate rows and deleting them using the Delete method of the Range object.

2. Deduplication Using Dictionaries

Keys in a dictionary must be unique—this property can be used for deduplication. The ID card information of personnel in each department is shown in Figure 21-1. Observation reveals that staff with IDs 1002 and 1008 have duplicate information. Below is an example of deduplication using Excel VBA and Python xlwings.

Figure 21-1 ID Card Information of Personnel in Each Department

【Excel VBA】

In Excel VBA, referencing relevant libraries is required to use dictionaries (refer to Chapter 8). When creating a dictionary, keys are composed of IDs from Column A, and values are corresponding data from other columns—four dictionaries are constructed. Since keys are unique, the final dictionary contains unique key-value pairs (achieving deduplication). The sample file is stored at Samples\ch21\Excel VBA\ID Card Deduplication.xlsm.

code.vba

Sub RemoveDuplicates()
    Dim intI As Integer
    Dim arr
    Dim dicT1 As New Dictionary, dicT2 As New Dictionary
    Dim dicT3 As New Dictionary, dicT4 As New Dictionary

    ' Get data
    arr = Range("A1", Cells(Rows.Count, "E").End(xlUp))

    ' Construct dictionary for deduplication
    For intI = 1 To UBound(arr)
        dicT1(arr(intI, 1)) = arr(intI, 2)  ' Key: ID; Value: Department
        dicT2(arr(intI, 1)) = arr(intI, 3)  ' Key: ID; Value: Name
        dicT3(arr(intI, 1)) = arr(intI, 4)  ' Key: ID; Value: ID Card Number
        dicT4(arr(intI, 1)) = arr(intI, 5)  ' Key: ID; Value: Gender
    Next

    ' Output deduplicated data
    [G1].Resize(dicT1.Count) = Application.Transpose(dicT1.Keys)   ' IDs
    [H1].Resize(dicT1.Count) = Application.Transpose(dicT1.Items)  ' Departments
    [I1].Resize(dicT1.Count) = Application.Transpose(dicT2.Items)  ' Names
    [J1].Resize(dicT1.Count) = Application.Transpose(dicT3.Items)  ' ID Card Numbers
    [K1].Resize(dicT1.Count) = Application.Transpose(dicT4.Items)  ' Genders
End Sub

Running the procedure outputs deduplicated data in Columns G–K.

【Python xlwings】

For the data in Figure 21-1, a dictionary is created where keys are IDs from Column A and values are corresponding row data. The keys method of the dictionary retrieves all keys—if a key already exists, the row data is not added (ensuring uniqueness). The sample file is stored at Samples\ch21\Python\ID Card Deduplication.py.

code.python

import xlwings as xw
import os
root = os.getcwd()
app = xw.App(visible=True, add_book=False)
wb = app.books.open(root + r'/ID Card Deduplication.xlsx', read_only=False)
sht = wb.sheets(1)
# Get data range (A1 to last row of Column B, Column E)
rng = sht.range('A1', sht.cells(sht.cells(1, 'B').end('down').row, 'E'))
dd = {}  # Create dictionary dd
# Traverse rows to build dictionary (key: ID; value: row data)
for i in range(rng.rows.count):
    if sht[i, 0].value not in dd.keys():  # If ID is not in dictionary keys
        dd[sht[i, 0].value] = rng.rows(i + 1).value  # Add row data to dictionary values
lst = list(dd.values())  # Convert dictionary values to list
sht.range('G1').options(expand='table&#x27;).value = lst  # Input list to worksheet

Running the script outputs deduplicated data (as shown in Figure 21-2).

Figure 21-2 Deduplicated Data

3. Deduplication Using Power Query and the pandas Package

When dealing with large datasets, Power Query or the pandas package can be used (they also work for small-to-medium data). Below is an example of deduplication using the drop_duplicates method of the pandas DataFrame object.

The following Python script uses the pandas package to open ID Card Deduplication.xlsx in the current path. It first imports data using read_excel, then deletes duplicates using drop_duplicates (specifying subset=['ID'] to deduplicate by ID, keep='first' to retain the first occurrence, and ignore_index=True to reset the index). The script is stored at Samples\ch21\Python\ID Card Deduplication2.py.

code.python

import pandas as pd
import os
root = os.getcwd()
df = pd.read_excel(io=root + r'\ID Card Deduplication.xlsx', engine='openpyxl')
# Deduplicate by 'ID' (retain first occurrence, reset index)
df2 = df.drop_duplicates(subset=['ID'], keep='first', ignore_index=True)
print(df2)

Running the script outputs:

code.python

>>> = RESTART: ...\Basic\Samples\ch21\Python\ID Card Deduplication2.py
   ID Department   Name                 ID Card Number Gender
0  1001    Finance  Chen Dong  5103211978100300**   Male
1  1002    Finance    Tian Ju  4128231980052512** Female
2  1008    Finance    Xia Dong  1328011947050583**   Male
3  1003  Production    Wang Wei  4302251980031135**   Male
4  1004  Production    Wei Long  4302251985111635**   Male
5  1005      Sales    Liu Yang  4302251980081235** Female
6  1006  Production    Lv Chuan  3203251970010171**   Male
7  1007      Sales    Yang Li  4201171973021753** Female

By default, drop_duplicates returns a new DataFrame. Setting inplace=True modifies the original DataFrame instead of creating a new one.

Handling Missing Values

Missing values occur when data cannot be collected (due to limited conditions) or is lost during collection. Missing values are not 0—they represent empty cells. Missing values prevent data processing, so they must be handled (either deleted or filled with a specified value).

【Excel VBA】

Section 13.5.7 mentions using the SpecialCells method of the Range object to reference special cells (including blank cells). This is a way to identify missing values.

The SpecialCells method can also fill blank cells with a specified value (e.g., mean or median). Below is an example of filling blank cells with 10. The sample file is stored at Samples\ch21\Excel VBA\Missing Values.xlsm.

code.vba

Sub MissingValues()
    Dim sht As Worksheet, rngN As Range
    Set sht = ActiveSheet

    ' Find blank cells
    Set rngN = sht.UsedRange.SpecialCells(xlCellTypeBlanks)
    If Not rngN Is Nothing Then
        rngN.Value = 10  ' Fill blank cells with 10
    End If
End Sub

Running the procedure fills blank cells with 10 (as shown in Figure 21-3).

【Python xlwings】

The following script uses Python xlwings to fill blank cells in a specified range with 10. The script is stored at Samples\ch21\Python\Missing Values.py.

code.python

import xlwings as xw
import os
root = os.getcwd()
app = xw.App(visible=True, add_book=False)
wb = app.books.open(root + r'/Missing Values.xlsx', read_only=False)
sht = wb.sheets(1)
# Get blank cells in the current region of A1
rng = sht.api.Range('A1').CurrentRegion.SpecialCells(xw.constants.CellType.xlCellTypeBlanks)
if not rng is None:
    rng.Value = 10  # Fill with 10

To delete rows/columns containing blank cells, use:

code.python

rng.EntireRow.Delete()   # Delete row with blank cell
rng.EntireColumn.Delete() # Delete column with blank cell

【Python pandas】

The pandas DataFrame provides methods to handle missing values:

isnull(): Identify missing values (returns True for missing values).

dropna(): Delete rows/columns with missing values.

fillna(): Fill missing values.

The sample file (with missing values) is shown in Figure 21-4. The script is stored at Samples\ch21\Python\Missing Values2.py.

Figure 21-4 Data with Missing Values

code.python

import pandas as pd
import os
root = os.getcwd()
df = pd.read_excel(io=root + r'\Missing Values2.xlsx', engine='openpyxl')
# Identify missing values
df2 = df.isnull()
print(df2)

Output (missing values are True):

code.python

>>> = RESTART: ...\Basic\Samples\ch21\Python\Missing Values2.py
       A      B      C      D
0  False  False  False  False
1  False  False   True  False
2  False   True  False  False
3  False  False  False  False
4  False  False  False   True
5  False  False  False  False
6   True  False  False  False
7  False  False   True  False
8  False  False  False   True
9  False   True  False  False
10 False  False  False  False

Delete rows with any missing value:

df3 = df.dropna(how='any') # 'any' means delete rows with at least one missing value

Fill missing values:

Fill all with 10: df4 = df.fillna(10)

Fill with column mean: df5 = df.fillna({'A': df['A'].mean(), 'B': df['B'].mean()})

Fill with the next valid value (backward fill): df6 = df.fillna(method='backfill')

Handling Outliers

Outliers are values that are statistically too large or too small (caused by errors or special cases). Including them in analysis distorts results. Two common methods to identify outliers:

Mean and Standard Deviation: Values outside [mean - 3×std, mean + 3×std] are outliers.

Quantiles: Calculate the interquartile range (IQR = 0.75 quantile - 0.25 quantile). Values outside [0.25 quantile - 1.5×IQR, 0.75 quantile + 1.5×IQR] are outliers (visualized via boxplots).

Outliers are often treated as missing values (deleted or replaced).

【Excel】

Example file: Samples\ch21\Excel Functions\Outliers.xlsx.

Method 1 (Mean + Std): In B1, enter:

code.python

=OR($A1<AVERAGE($A$1:$A$14)-3*STDEV($A$1:$A$14), $A1>AVERAGE($A$1:$A$14)+3*STDEV($A$1:$A$14))

Drag down to apply—value 326 is identified as an outlier.

Method 2 (Quantiles): In C1, enter:

code.python

=OR($A1<PERCENTILE.EXC($A$1:$A$14,0.25)-1.5*(PERCENTILE.EXC($A$1:$A$14,0.75)-PERCENTILE.EXC($A$1:$A$14,0.25)), $A1>PERCENTILE.EXC($A$1:$A$14,0.75)+1.5*(PERCENTILE.EXC($A$1:$A$14,0.75)-PERCENTILE.EXC($A$1:$A$14,0.25)))

Drag down—values 3, 104, and 326 are outliers.

Boxplot: Insert a boxplot for Column A—points outside the whiskers are outliers (as shown in Figure 21-5).

【Excel VBA】

Example file: Samples\ch21\Excel VBA\Outliers.xlsm.

Procedure Test (Mean + Std):

code.vba

Sub Test()
    Dim intI As Integer
    Dim sngMean As Single, sngSTDEV As Single

    ' Calculate mean and std
    sngMean = Application.WorksheetFunction.Average(Range("A1:A14"))
    sngSTDEV = Application.WorksheetFunction.StDev(Range("A1:A14"))

    ' Mark outliers
    For intI = 1 To 14
        If Cells(intI, 1) < sngMean - 3 * sngSTDEV Or Cells(intI, 1) > sngMean + 3 * sngSTDEV Then
            Cells(intI, 2).Value = True
        Else
            Cells(intI, 2).Value = False
        End If
    Next
End Sub

Procedure Test2 (Quantiles):

code.vba

Sub Test2()
    Dim intI As Integer
    Dim sngP25 As Single, sngP75 As Single, sngIQR As Single

    ' Calculate quantiles and IQR
    sngP75 = Application.WorksheetFunction.Percentile(Range("A1:A14"), 0.75)
    sngP25 = Application.WorksheetFunction.Percentile(Range("A1:A14"), 0.25)
    sngIQR = sngP75 - sngP25

    ' Mark outliers
    For intI = 1 To 14
        If Cells(intI, 1) < sngP25 - 1.5 * sngIQR Or Cells(intI, 1) > sngP75 + 1.5 * sngIQR Then
            Cells(intI, 3).Value = True
        Else
            Cells(intI, 3).Value = False
        End If
    Next
End Sub

【Python xlwings】

Scripts to call Excel functions for outlier detection:

Mean + Std: Samples\ch21\Python\Outliers-xlwings-1.py

Quantiles: Samples\ch21\Python\Outliers-xlwings-2.py

Example (Mean + Std):

code.python

import xlwings as xw
import os
root = os.getcwd()
app = xw.App(visible=True, add_book=False)
wb = app.books.open(root + r'/Outliers.xlsx', read_only=False)
sht = wb.sheets(1)
# Calculate mean and std
mean_v = app.api.WorksheetFunction.Average(sht.api.Range('A1:A14'))
stdev_v = app.api.WorksheetFunction.StDev(sht.api.Range('A1:A14'))
# Mark outliers
for i in range(1, 15):
    if sht.api.Cells(i, 1).Value < mean_v - 3 * stdev_v or sht.api.Cells(i, 1).Value > mean_v + 3 * stdev_v:
        sht.api.Cells(i, 2).Value = True
    else:
        sht.api.Cells(i, 2).Value = False

【Python pandas】

Use pandas to calculate mean, std, and quantiles:

Mean + Std: Samples\ch21\Python\Outliers-pandas-1.py

code.python

import pandas as pd
import numpy as np
import os
root = os.getcwd()
df = pd.read_excel(io=root + r'\Outliers2.xlsx', engine='openpyxl')
mean_v = df['A'].mean()
stdev_v = df['A'].std()
data = df['A']
# Print outliers
print(data[(data > mean_v + 3 * stdev_v) | (data < mean_v - 3 * stdev_v)])
# Replace outliers with NaN
data[(data > mean_v + 3 * stdev_v) | (data < mean_v - 3 * stdev_v)] = np.nan
print(data)

Quantiles: Samples\ch21\Python\Outliers-pandas-2.py

code.python

import pandas as pd
import numpy as np
import os
root = os.getcwd()
df = pd.read_excel(io=root + r'\Outliers2.xlsx', engine='openpyxl')
stp75 = df['A'].quantile(0.75)
stp25 = df['A'].quantile(0.25)
iqr = stp75 - stp25
data = df['A']
# Print outliers
print(data[(data > stp75 + 1.5 * iqr) | (data < stp25 - 1.5 * iqr)])
# Replace outliers with NaN
data[(data > stp75 + 1.5 * iqr) | (data < stp25 - 1.5 * iqr)] = np.nan
print(data)

Boxplot: Use matplotlib to plot a boxplot (Samples\ch21\Python\Boxplot.py):

code.python

import pandas as pd
import matplotlib.pyplot as plt
import os
root = os.getcwd()
df = pd.read_excel(io=root + r'\Outliers2.xlsx', engine='openpyxl')
plt.boxplot(df['A'])
plt.show()

Data Transformation

To eliminate the effects of scale and magnitude (or meet statistical method requirements), data is often transformed before analysis. Common transformations include:

Centralization: Subtract the mean from each value (shift data to center at 0).

Standardization: Transform data to follow a standard normal distribution (subtract mean, divide by std).

Normalization: Scale data to [0, 1] (subtract min, divide by range = max - min).

【Excel】

Example file: Samples\ch21\Excel Functions\Data Transformation.xlsx.

Centralization (Column B): =$A2-AVERAGE($A$2:$A$15)

Normalization (Column C): =($A2-MIN($A$2:$A$15))/(MAX($A$2:$A$15)-MIN($A$2:$A$15))

Standardization (Column D): =STANDARDIZE($A2, AVERAGE($A$2:$A$15), STDEV($A$2:$A$15))

Table of Contents

Data Deduplication

Handling Missing Values

Handling Outliers

Data Transformation