Finding The Most Correlated Item

I have a restaurant sales details as below. +----------+------------+---------+----------+ | Location | Units Sold | Revenue | Footfall | +----------+------------+---------+-------

Solution 1:

If your correlation should be described like minimal euclidean distance, solution is:

#convert columns to numeric
df1['Revenue'] = df1['Revenue'].str.replace(',','').astype(int)
df2['Revenue'] = df2['Revenue'].str.replace(',','').astype(int)

#distance of all columns subtracted by first row of first DataFrame
dist = np.sqrt((df2['Units Sold']-df1.loc[0, 'Units Sold'])**2 + 
               (df2['Revenue']- df1.loc[0, 'Revenue'])**2 + 
               (df2['Footfall']- df1.loc[0, 'Footfall'])**2)

print (dist)
0    103.077641
1    160.390149
2     55.398556
3    115.991379
4     17.058722
5    115.542200
dtype: float64

#get index of minimal value and select row of second df
print (df2.loc[[dist.idxmin()]])
   Location  Units Sold  Revenue  Footfall
4  Loc - 06          89     1157        74

Solution 2:

Might be a better way to do this, but I think this works, it's quite verbose so I've tried to keep the code clean and readable:

First, lets use a self defined numpy function from this post.

import numpy as np
import pandas as pd


def find_nearest(array, value):
    array = np.asarray(array)
    idx = (np.abs(array - value)).argmin()
    return array[idx]

then using the arrays of your dataframe, pass in the value from your first dataframe to find the closest match.

us = find_nearest(df2['Units Sold'],df['Units Sold'][0])
ff = find_nearest(df2['Footfall'],df['Footfall'][0])
rev = find_nearest(df2['Revenue'],df['Revenue'][0])

print(us,ff,rev,sep=',')
100,87,1157

then return a data frame with all three conditions

    new_ df = (df2.loc[
    (df2['Units Sold'] == us) |
    (df2['Footfall'] == ff) |
    (df2['Revenue'] == rev)])

which gives us :

    Location    Units Sold  Revenue Footfall
0   Loc - 02    100         1250    60
3   Loc - 05    115         1035    87
4   Loc - 06    89          1157    74

Solution 3:

Fix Data

For numeric columns. I generalized this too much probably. Also, I set the index to be the 'Location' column

def fix(d):
    d.update(
        d.astype(str).replace(',', '', regex=True)
         .apply(pd.to_numeric, errors='ignore')
    )
    d.set_index('Location', inplace=True)

fix(df1)
fix(df2)

---

Manhattan Distance

df2.loc[[df2.sub(df1.loc['Loc - 01']).abs().sum(1).idxmin()]]

          Units Sold Revenue  Footfall
Location                              
Loc - 06          89    1157        74

Euclidean Distance

df2.loc[[df2.sub(df1.loc['Loc - 01']).pow(2).sum(1).pow(.5).idxmin()]]

          Units Sold Revenue  Footfall
Location                              
Loc - 06          89    1157        74