To find matches between strings in two CSV files, use fuzzy matching on huge datasets.
Dataset Link Provided below:
Solution/Approach:
Step 1: Install Libraries
# Installing Libraries pip install pandas rapidfuzz
Installing collected packages: rapidfuzz Successfully installed rapidfuzz-3.9.6
!pip install fuzzywuzzy[speedup]
Installing collected packages: fuzzywuzzy, Levenshtein, python-levenshtein Successfully installed Levenshtein-0.25.1 fuzzywuzzy-0.18.0 python-levenshtein-0.25.1
import pandas as pd
# The two datasets
df1 = pd.read_csv('/content/Customers_100K.csv', encoding='ISO-8859-1')
df2 = pd.read_csv('/content/Customers_1M.csv', encoding='ISO-8859-1')
For data manipulation, this code imports the `pandas` library as `pd}. After that, it reads two CSV files, Customers_100K.csv} and Customers_1M.csv}, into data frames, df1} and df2}, respectively, handling special characters with the provided encoding, ISO-8859-1}. The data from the corresponding CSV files is now contained in the data frames {df1} and {df2} for additional processing.
#print the columns in first dataset print(df1.columns) #print the columns in second dataset print(df2.columns)
Index(['ID', 'NAME_', 'SURNAME', 'NAMESURNAME', 'GENDER', 'BIRTHDATE', 'EMAIL', 'TCNUMBER', 'TELNR', 'CITY', 'TOWN', 'DISTRICT', 'STREET', 'POSTALCODE', 'ADDRESSTEXT'], dtype='object') Index(['ID', 'NAME_', 'SURNAME', 'NAMESURNAME', 'GENDER', 'BIRTHDATE', 'EMAIL', 'TCNUMBER', 'TELNR', 'CITY', 'TOWN', 'DISTRICT', 'STREET', 'POSTALCODE', 'ADDRESSTEXT'], dtype='object')
The output demonstrates that the column names for df1 and df2 are the same. Nonetheless, each data frame’s first column name, “ID,” denotes the existence of a Byte Order Mark (BOM) artifact (). This BOM can create problems in data processing and usually shows up when files are stored with UTF-8 encoding. The following are the columns present in both data frames: “ID” (including BOM artifact) “NAME_,” “SURNAME,” “NAMESURNAME,” “GENDER,” “BIRTHDATE,” “EMAIL,” “TCNUMBER,” “TELNR,” “CITY,” “TOWN,” “DISTRICT,” “STREET,” “POSTALCODE,” and “ADDRESSTEXT.”
Step 4: Apply the fuzzy_match function
# Adjust this list based on the actual columns in your dataframe
columns_to_concat = ['CITY']
# Create concatenated string in both dataframes
df1['Concatenated'] = df1[columns_to_concat].astype(str).agg(' '.join, axis=1)
df2['Concatenated'] = df2[columns_to_concat].astype(str).agg(' '.join, axis=1)
# Apply the fuzzy_match function
df1['Match'] = df1['Concatenated'].apply(lambda x: fuzzy_match(x, df_to_match=df2, column_to_match='Concatenated'))
This code combines the values in the specified columns (in this example, {‘CITY’}) into a single string, creating a new column {Concatenated} in both `df1} and `df2}. After that, it uses the concatenated column in `df2} to discover fuzzy matches when applying the `fuzzy_match} function to `df1[‘Concatenated’]}. The results are then stored in a new column called `Match} in `df1}.
Step 5: Matched File
matched_df = df1[df1['Match'].notna()]
`matched_df = df1[df1[‘Match’].notna()]} filters {df1} such that only rows with a `Match` column that is not {NaN} are included. By doing this, a new data frame called `matched_df} is produced that only includes the rows in `df1} for which a fuzzy match was discovered in `df2}.