How do you order row values from an Excel file in Python with a dictionary?-CodePudding

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)

CodePudding user response：

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)

CodePudding user response：

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.

CodePudding user response：

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