0

Let's say I have an excel sheet with 2 rows:

0.296178    0.434362    0.033033    0.758968
0.559323    0.455792    0.780323    0.770423

How could I go about putting each rows values in order from highest to lowest with a dictionary?

For example, the dictionary input for row 1 should look like: {1:[4,2,1,3]} since the 4th value in row 1 is highest and the 3rd value in row 1 is lowest.

(not indexing from 0 due to it being an Excel file)

DannyBuoyo
  • 1
  • 1

4 Answers4

0

For that, first, you need a module to import excel files. I recommend pandas, as it is widely used. (install it using 'pip install pandas', if you haven't)

after that use this code:

import pandas as pd
path = r'C:\Users\tanuj\Desktop\temp.xlsx' # replace it with your file path
df = pd.read_excel(path, header = None)
df.head() # to visualise the file

#And then, use this simple logic to get the required dictionary
d = {}
for x in range(df.shape[0]):
    temp = {}
    values = list(df.iloc[x])
    for y in range(len(values)):
        temp[df.loc[x][y]] = y+1
    l = []
    for t in sorted(temp):
        l.append(temp[t])
    l.reverse()
    d[x+1] = l
print(d)
Tanuj T.V.S
  • 36
  • 4
0

argsort function in numpy will do the trick. Consider this code:

import numpy as np
import pandas as pd

df = pd.read_csv('excel.csv', delimiter=',', header=None)

i = 0
dict = {}
for row in df.values:
    arg = np.argsort(row)
    iarg = list(map(lambda x: x+1, arg))
    iarg.reverse()
    dict[i]=iarg
    i = i + 1

print(dict)

It reads input data as formatted csv and gives you the desired output.

Pankaj Saini
  • 1,164
  • 1
  • 5
  • 4
0

After Reading Your Question I think you want to Read row values from an excel sheet and store it in dictionary and then want to sort values from dictionary from highest to lowest order...

So First you have to read excel file that store such value for that u can use openpyxl module

from openpyxl import load_workbook
wb = load_workbook("values.xlsx")  
ws = wb['Sheet1']
for row in ws.iter_rows():
    print([cell.value for cell in row])

the above code will generate a list of values that are in excel file

In your case:

[0.296178, 0.434362, 0.033033, 0.758968] [0.559323, 0.455792, 0.780323, 0.770423]

now you have to store it in dictionary and now sort it...

from openpyxl import load_workbook

wb = load_workbook("values.xlsx")  
ws = wb['Sheet1']

value_dict={}
n=1
#extracting value from excel
for row in ws.iter_rows():  
    values=[cell.value for cell in row]
    value_dict[n]=values
    n=n+1

print(value_dict)

#Sorting Values
for keys,values in value_dict.items():
    values.sort(reverse=True)
    print("Row "+ str(keys),values)

The Above Code Perform The same task that you want to perform...

Output Image

Community Programmer
  • 26
  • 2
0

For each row in a df you can compare each element to a sorted version of that row and get the indexes.

import pandas as pd


a = [0.296178,    0.434362,    0.033033,    0.758968]
b = [0.559323,    0.455792,    0.780323,    0.770423]
df = pd.DataFrame(columns = ['1', '2'], data = zip(a, b)).T


def compare_sort(x):
  x = list(x)
  y = sorted(x.copy())[::-1]
  return([x.index(y[count]) +1 for count, _ in enumerate(y) ])



print(df.apply(compare_sort, axis=1)) # apply func. to each row of df  

1    [4, 2, 1, 3]
2    [3, 4, 1, 2]

# Get data by row name

df = df.apply(compare_sort, axis=1)
print(df['1'])

[4, 2, 1, 3]

Useful links.

get-indices-of-items

reverse-a-list

RSale
  • 463
  • 5
  • 14