For my work, I wrote a python script to link 2 files. Since I am an autodidact and since no one of my colleagues writes code, I ask the question here.

My code takes an unbelievable time to run. Is it possible to improve the following code? (I'm sure it is possible but I have no idea how to do that).

Context: link two files of several dozen of thousand lines on the possible building location address. Since it is two manually written databases, it is not straightforward.

Aim: obtain a file where information on the address exists (exactly same address, same street or same street but with different street designation).

import unicodedata
import pandas as pd
import nltk
from nltk .corpus import stopwords

def strip_accents(s):
    '''Remove all accents from words'''
    return ''.join(c for c in unicodedata.normalize('NFD', s)
                   if unicodedata.category(c) != 'Mn')

def tokenize(string):
    '''return list with words and numbers from string'''
    tok = nltk.RegexpTokenizer(r'''(?x)
            \w+               # only words and numbers
    return tok.tokenize(string)

def french_stopwords(my_list, update=[]):
    ''' remove words from stropwords.words('french') from string. Update= list
        of words to add to stopwords'''
    french_stopwords = set(stopwords.words('french'))
    french_stopwords = set(french_stopwords)
    new_list = []
    for token in my_list:
        if token not in french_stopwords:
    return new_list

def set_column_sequence(dataframe, seq, front=True):
    '''Takes a dataframe and a subsequence of its columns,
       returns dataframe with seq as first columns if "front" is True,
       and seq as last columns if "front" is False.'''
    cols = seq[:]  # copy so we don't mutate seq
    for x in dataframe.columns:
        if x not in cols:
            if front:  # we want "seq" to be in the front
                # so append current column to the end of the list
                # we want "seq" to be last, so insert this
                # column in the front of the new column list
                # "cols" we are building:
                cols.insert(0, x)
    return dataframe[cols]

# IPIC data
df = pd.read_excel('all_files_IPIC_2_test.xls', encoding='latin1')
df_ipic = df[(df['Dernier'] == 1)]  # select only last data entry (dernier= 1)
df_ipic = df_ipic.reset_index(drop=True)
df_ipic = df_ipic.fillna('')        # remove all Nan values
df_ipic['Rue_01_Diff'] = df_ipic['Rue_01_Diff'].str.replace(r"\(.*\)", "")
df_ipic['Rue_01_Diff'] = df_ipic['Rue_01_Diff'].str.replace(r"\[.*\]", "")

# Adlib data
df_adlib = pd.read_csv('database_adlib_buildings_test.csv', encoding='latin1')
df_adlib = df_adlib.fillna('')  # remove all Nan values
df_adlib = df_adlib.rename(columns={'object_type_(OB)': 'object_type_OB',
                                    'title and description':'title_and_description'})
df_adlib['current_location'] = df_adlib['current_location'].str.replace(r"\(.*\)","")
df_adlib['current_location'] = df_adlib['current_location'].str.replace(r"\[.*\]","")

# words to add to stopwords
words_list = ['a', 'dite', 'dit']

# lists results
ipic_id = []       # column name = CodeInt
ipic_adress = []   # column name = Rue_01_Diff
adlib_adress = []  # column name = current_location
adlib_street = []  # derived from this script
adlib_street_changed = []
adlib_object_number = []  # column name = object_number
osm_adress = []
osm_id = []

temp1 = []
temp2 = []
temp3 = []
temp_1 = []
temp_2 = []
temp_22 = []
temp_3 = []
temp_33 = []
temp_11 = []
temp_osm = []

# -------------------------------------------------------------------
for idx_ipic in df_ipic.index:
    ipic = df_ipic.Rue_01_Diff[idx_ipic]
    ipic2 = strip_accents(ipic.lower())
    ipic_tok = french_stopwords(tokenize(ipic2), update=words_list)

    for idx_adlib in df_adlib.index:
        adlib = df_adlib.current_location[idx_adlib]
        adlib2 = strip_accents(adlib.lower())
        adlib_tok = french_stopwords(tokenize(adlib2), update=words_list)

        # raw matching
        if set(adlib_tok) == set(ipic_tok):

        # only street name
        a = [x for x in ipic_tok if not x.isdigit()]
        b = [x for x in adlib_tok if not x.isdigit()]
        if set(a) == set(b):
            bb = ' '.join(b)
            if df_adlib.object_number[idx_adlib] not in temp_22:

        # change street denomination
        groupe_semantique = {'nom_generique': 'rue',
                             'liste':['rue', 'avenue', 'boulevard', 'autoroute', 'chaussée']}
        w = [x for x in groupe_semantique['liste'] if x in a]
        y = [x for x in groupe_semantique['liste'] if x in b]
        if len(w) != 0 and len(y) != 0:
            if df_adlib.object_number[idx_adlib] not in temp_33:
                a_new = [groupe_semantique['nom_generique'] if x in groupe_semantique['liste'] else x for x in a]
                b_new = [groupe_semantique['nom_generique'] if x in groupe_semantique['liste'] else x for x in b]
                a_new2 = ' '.join(a_new)
                b_new2 = ' '.join(b_new)
                if a_new2 == b_new2:

df1 = pd.DataFrame({'id_ipic': temp_1, 'adlib_adress': temp1, 'adlib_object_number': temp_11})
df1 = df1.drop_duplicates()
df2 = pd.DataFrame({'id_ipic': temp_2, 'adlib_street': temp2, 'adlib_object_number': temp_22})
df2 = df2.drop_duplicates()
df3 = pd.DataFrame({'id_ipic': temp_3, 'adlib_type_street_changed': temp3, 'adlib_object_number': temp_33})
df3 = df3.drop_duplicates()

# information from xls files
data_ipic = pd.DataFrame({'id_ipic': df_ipic.CodeInt,
                          'ipic_adress': df_ipic.Rue_01_Diff, 
                          'Libelle_Diff': df_ipic.Libelle_Diff})
data_adlib = pd.DataFrame({
                           'adlib_object_name': df_adlib.object_name,
                           'adlib_object_number': df_adlib.object_number,
                           'object_type_OB': df_adlib.object_type_OB,

# final dataframe
df_recap = df1.merge(df2, how='outer').merge(df3, how='outer')
df_recap = df_recap.merge(data_ipic, how='inner').merge(data_adlib, how='inner')

# reshape final dataframe
df_recap = df_recap.drop_duplicates()
df_recap = df_recap.reset_index(drop=True)

# change columns order
seq = ['id_ipic', 'Libelle_Diff', 'ipic_adress', 'adlib_adress', 'adlib_street',
       'adlib_type_street_changed', 'adlib_object_name', 'adlib_object_number',
       'object_type_OB', 'title_and_description']
df_recap = set_column_sequence(df_recap, seq, front=True)
  • \$\begingroup\$ So you have two files, with a list of addresses in both of them. You want to extract the lines that are common between the two files, excluding accents and street designations (which are your stopwords). Is that correct? \$\endgroup\$
    – ChatterOne
    Commented Aug 2, 2017 at 9:50
  • \$\begingroup\$ Globally, yes. But stopwords is there to simplify the street names. For example: 'rue de l'alouette, n°12' -> 'rue alouette 12' (after tokenized). Street designation is compared by means of groupe_semantique. \$\endgroup\$
    – francois
    Commented Aug 2, 2017 at 14:35

3 Answers 3


All you need to do is:

  • normalize the two files, so that the content is in the same format (that means removing whatever you think should not be there, like , de l' and so on)
  • combine the two files
  • sort them
  • pick the duplicate lines

After you've normalized the two files you can do it (in the unix or cygwin command line) with a

cat file1 file2 |sort | uniq -d

This will be much faster ( \$O(n log n)\$ instead of your \$O(n^2)\$ )


I also developed the raw matching to make a link with the words in disorder:

# raw matching
if set(adlib_tok) == set(ipic_tok):
    found = True
if not found:
    if set(re.findall('|'.join(adlib_tok), ipic)) == set(adlib_tok):

A better loop:

lst_dict = []   # to record results in a dict
for idx_i in df_i.index:     # variables taken in first dataframe
    ipic = df_i.Rue_01_Diff[idx_i]
    ipic_street = set(df_i.ipic_street[idx_i])
    ipic_num = df_i.ipic_num[idx_i]
    codeint = df_i.CodeInt[idx_i]

    for idx_a in df_a.index:     # variables from second dataframe
        adlib = df_a.current_location[idx_a]
        adlib_street = set(df_a.adlib_street[idx_a])
        adlib_num = df_a.adlib_num[idx_a]
        object_num = df_a.object_number[idx_a]
        found = False 

        # street matching
        if ipic_street == adlib_street:
            found = True
            lst_dict.append({'CodeInt':codeint, 'ipic_adress': ipic, 'adlib_adress': adlib, 'ipic_street': ipic_street,
                             'adlib_street': adlib_street, 'ipic_num': ipic_num, 'adlib_num': adlib_num, 'object_number': object_num})

        if not found:
            if adlib_street and not adlib_street == [''] and ipic_street and not ipic_street == ['']:
                if adlib_street.issubset(ipic_street) or ipic_street.issubset(adlib_street):
                    lst_dict.append({'CodeInt':codeint, 'ipic_adress': ipic, 'adlib_adress': adlib, 'ipic_street': ipic_street, 'adlib_street': adlib_street, 'ipic_num': ipic_num, 'adlib_num': adlib_num, 'object_number': object_num})
                    found = True

    if not found:
            lst_dict.append({'CodeInt':codeint, 'ipic_adress': ipic, 'adlib_adress': 'xx', 'ipic_street': 'xx', 'adlib_street': 'xx', 'ipic_num': 'xx', 'adlib_num': 'xx', 'object_number': 'xx'})

df_result = pd.DataFrame(lst_dict)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.