[Python] Correctly Assign Street Sectors Based on Even/Odd Street Numbers and Street Segment...

Ok I cannot wrap my head around that.

I have two dataframes, first_df and second_df, where first_df contains information about street segments, including the street name, start and end numbers of street segments, and whether the segment is for even or odd numbers. second_df contains street numbers and their corresponding addresses.

The goal is to assign the correct "Secteur Elementaire" (elementary sector) to each street number in second_df based on the information in first_df. So it needs to pass three checks:

1. Check if streetname exists (and create a subset dataframe containing only the elemnts corresponding to this streetname)
2. Check if streetnumber is within the range defined by first_df['Début'] and first_df['Fin']
3. Check if it is even or odd, and assign the sector based on its whether the number is part of the even or odd sector.

The issue I'm facing is that the logic to determine the correct sector based on whether the street number is even or odd is not working as expected. The current implementation is assigning the wrong sectors in some cases.

first_df = pd.DataFrame({
'Voie': ['AVENUE D EPERNAY', 'AVENUE D EPERNAY', 'AVENUE D EPERNAY', 'AVENUE D EPERNAY'],
'Périmètre élémentaire': ['PROVENCAUX', 'AVRANCHES', 'MAISON BLANCHE', 'SCULPTEURS JACQUES'],
'Périmètre maternelle': ['AUVERGNATS-PROVENCAUX', 'AVRANCHES', 'MAISON BLANCHE', 'SCULPTEURS JACQUES'],
'Début': [142, 1, 73, 2],
'Fin': [998, 71, 999, 140],
'Partie': ['Pair', 'Impair', 'Impair', 'Pair']
})

second_df = pd.DataFrame({
'numero': [1, 2, 6, 7, 8, 9, 10, 12],
'nom_afnor': ['AVENUE D EPERNAY', 'AVENUE D EPERNAY', 'AVENUE D EPERNAY', 'AVENUE D EPERNAY', 'AVENUE D EPERNAY', 'AVENUE D EPERNAY', 'AVENUE D EPERNAY', 'AVENUE D EPERNAY'],
'Secteur Elementaire': ['tbd', 'tbd', 'tbd', 'tbd', 'tbd', 'tbd', 'tbd', 'tbd']
})


def sort_df(first_df, second_df):
for bano_index, row in second_df.iterrows():
street_number = row['numero']
street_name = row['nom_afnor']
#print(f'first loop: {num_de_rue}')

if street_name in first_df['Voie'].values: # check if street name exists
reims_filtered = first_df.loc[first_df['Voie'] == street_name] # if it does create a dataframe containing only the elements matching the street name
#print(reims_filtered)
for reims_index, reims_matching_row in reims_filtered.iterrows(): # iterate over the rows the filtered dataframe
#print(reims_matching_row)
if street_number >= reims_matching_row['Début'] and street_number <= reims_matching_row['Fin']: # check if street number is between range of street segment
#print(f'Check range {street_number} {reims_matching_row['Périmètre élémentaire']}')
if street_number % 2 == 0: # check if street number is even
print(reims_index, street_number, reims_matching_row['Partie'])
if reims_matching_row['Partie'] == 'Pair': # if it is even, then check which sector should be taken to be assigned based on street segment and its corresponding odd/even sector
print(f'Check column {street_number} {reims_matching_row['Périmètre élémentaire']}')
sector_to_assign = reims_matching_row['Périmètre élémentaire']
second_df.at[bano_index, 'Secteur Elementaire'] = sector_to_assign
break
else:
print(f'Check odd {street_number} {reims_matching_row['Périmètre élémentaire']}')
sector_to_assign = reims_matching_row['Périmètre élémentaire']
second_df.at[bano_index, 'Secteur Elementaire'] = sector_to_assign
break

return second_df

sort_df(first_df, second_df)


Output of running this function:

Check odd 1 AVRANCHES
1 2 Impair
1 6 Impair
Check odd 7 AVRANCHES
1 8 Impair
Check odd 9 AVRANCHES
1 10 Impair
1 12 Impair
numero nom_afnor Secteur Elementaire
0 1 AVENUE D EPERNAY AVRANCHES
1 2 AVENUE D EPERNAY tbd
2 6 AVENUE D EPERNAY tbd
3 7 AVENUE D EPERNAY AVRANCHES
4 8 AVENUE D EPERNAY tbd
5 9 AVENUE D EPERNAY AVRANCHES
6 10 AVENUE D EPERNAY tbd
7 12 AVENUE D EPERNAY tbd


Clearly there is an issue with the odd or even logic, especially when trying to assign reims_matching_row['Partie'] == 'Pair'.

If someone has any leads…

Continue reading...