# Find the most similar pair of rows from a table

I have a list of n cities and hourly weather prediction for each one of them for h hours. I'm writing a script to find a pair of cities which has the most similar weather. And to represent the similarity in terms of a percentage scale.

An example dataset with 5 hour forecast for 3 cities are as follows:

city                        temp    feels_like    pressure    humidity    wind_speed    wind_deg
------------------------  ------  ------------  ----------  ----------  ------------  ----------
Hoboken                   302.59        302.64        1013          44          4.45         266
Hoboken                   304.41        303.97        1013          37          4.57         252
Hoboken                   305.44        304.84        1012          34          5.26         254
Hoboken                   305.8         305.15        1012          33          6.22         251
Hoboken                   305.69        305.16        1012          34          6.7          253
Port Hueneme              289.65        289.39        1015          78          1.51         172
Port Hueneme              290.16        289.9         1015          76          2.17         191
Port Hueneme              290.3         290.08        1015          77          2.81         202
Port Hueneme              290.49        290.26        1014          76          3.64         208
Port Hueneme              290.41        290.18        1015          76          4.06         212
Auburn                    299.29        299.29        1011          66          7.12         265
Auburn                    299.92        301.21        1012          64          7.63         266
Auburn                    300.19        301.51        1011          63          7.52         265
Auburn                    299.89        301.1         1012          63          7.52         267
Auburn                    299.27        299.27        1012          64          7.17         266


And here is my script to find most similar cities in terms of weather assuming all datapoint have same weightage.

import pandas as pd
import numpy as np

def nearest(df):
"""
Process Dataframe with weather data and returns closest match for each city
"""
ret = []
for i, row in df.iterrows():
diff = np.abs(df.drop(i)['sum'] - df['sum'][i])
idx = diff.argmin()
min_diff = diff.min()
ret.append((df.drop(i).iloc[idx]['city'], min_diff))
return ret

def main():
# Prepare dataframe

# find average of each weather data points (temp, feels_like, etc) for each city
df = df.groupby('city', as_index=False).mean()

# Adds all mean values to a single value for comparison
df['sum'] = df.sum(axis=1)
df = df[['city', 'sum']]

# Find closest city to each city in weather data mean
df[['closest_city', 'diff']] = nearest(df)

# Calculate similarity %
df['%match'] = (df['sum'] - df['diff']) / df['sum'] * 100

# Find closest match by taking highest match%
closest = df.iloc[df['%match'].argmax()]


You probably want to replace

def main():


with

if __name__ == "__main__":


This second form means only run the indented code if this module specifically is called, not if it is imported. I'm assuming after the provided code, you just call main(), which is fine, but if you have no intentions of using main outside of this module, you should use the second form.

Also, avoid df.iterrows() at all costs. It returns a pandas Series for every row, which adds a huge amount of unnecessary overhead. You always want to create a vectorized solution if you can, so it would be good practice to investigate your nearest function to determine how you can replace the df.iterrows() with a form which uses the entire series at once, rather than row by row.

• Thanks for the input. If you have any suggestions on the best approach to do the nearest function, adding them to the answer will be very helpful Jun 6 at 6:54