Skip to content Skip to sidebar Skip to footer

Csv: Find Best Match/closest Value By Several Parameters In 2 Different Csv Files?

I have a code that conducts a search of closest value between 2 CSV files. It reads a CSV file called 'common_list' with some database which looks like this: The code puts these C

Solution 1:

Input data:

>>> plans
  common_name  common_Price  common_Offnet  common_Traffic
0       plan1          1300            250           13000
1       plan2          1800            350           18000

>>> df
  User ID  ARPU_AVERAGE  Offnet Calls  Traffic (MB)
0   Louis          1300           250         13000  # plan1 for sure (check 1)
1    Paul          1800           350         18000  # plan2, same values (check 2)
2    Alex          1500           260         14000  # plan1, probably

Create your matching function:

def rmse(user, plans):
    u = user[['ARPU_AVERAGE', 'Offnet Calls', 'Traffic (MB)']].values.astype(float)
    p =  plans[['common_Price', 'common_Offnet', 'common_Traffic']].values
    plan = np.sqrt(np.square(np.subtract(p, u)).mean(axis=1)).argmin()
    return plans.iloc[plan]['common_name']


df['Best Plan'] = df.apply(rmse, axis="columns", plans=plans)

Output:

>>> df
  User ID  ARPU_AVERAGE  Offnet Calls  Traffic (MB) Best Plan
0   Louis          1300           250         13000     name1
1    Paul          1800           350         18000     name2
2    Alex          1500           260         14000     name1

Edit: Full code with you variable names:

common_list = pd.read_csv("common_list.csv")
csv_data = pd.read_csv("By_ARPU.csv")

find_the_best_plan = lambda target: np.sqrt(np.square(np.subtract(array, target)).mean(axis=1)).argmin()

array = common_list[['common_Price', 'common_Offnet', 'common_Traffic']].values
data = csv_data[['ARPU_AVERAGE', 'Offnet Calls', 'Traffic (MB)']].values

sol = np.apply_along_axis(find_the_best_plan, 1, data)

csv_data["Suggested Plan [SP]"] = common_list['common_name'].iloc[sol].values
csv_data["SP: Offnet Minutes"] = common_list['common_Offnet'].iloc[sol].values
csv_data["SP: Traffic"] = common_list['common_Traffic'].iloc[sol].values

Post a Comment for "Csv: Find Best Match/closest Value By Several Parameters In 2 Different Csv Files?"