vjg vjg - 5 months ago 12
Python Question

Python (Excel, dictionary) how to write it shorter

I wrote a program that is too long and I'm sure that I can write it shorter.

This is a function that export values from Excel:

#"place" is a place where I have Excel file "book1.xlsx"
place=os.path.join(DATA_DIR,'book1.xlsx')
Excel=win32com.client.Dispatch('Excel.Application')
wb=Excel.Workbooks.Open(place)
sheet=wb.ActiveSheet

#we are reading information from Excel
# I have a table in Excel. This table has a name "Table'
#TI3_value is a column in Table with name TI3
#TI3_value is a list, for example, [4.97, 3.02,1.02]
TI3_value=[r[0].value for r in sheet.Range("Table[TI3]")]

#TI4_value is a column in Table with name TI4
TI4_value=[r[0].value for r in sheet.Range("Table[TI4]")]
TI5_value=[r[0].value for r in sheet.Range("Table[TI5]")]

Frank_value=[r[0].value for r in sheet.Range("Table[Frank]")]
Shanh_value=[r[0].value for r in sheet.Range("Table[Shanh]")]
Manil_value=[r[0].value for r in sheet.Range("Table[Manil]")]

TI3_name=[r[0].value for r in sheet.Range("Table[TI3_name]")]
TI4_name=[r[0].value for r in sheet.Range("Table[TI4_name]")]
TI5_name=[r[0].value for r in sheet.Range("Table[TI4_name]")]

Frank_value=[r[0].value for r in sheet.Range("Table[Frank_name]")]
Shanh_value=[r[0].value for r in sheet.Range("Table[Shanh_name]")]
Manil_value=[r[0].value for r in sheet.Range("Table[Manil_name]")]

TI3_name=[x for x in TI3_name if str(x)!='None'


As you can see, it's not a compact code.


How can I write it shorter?





I tried to use a dictionary....

Heroes_name={'TI3_name':1,'TI4_name':2,'TI5_name':3,
'Frank_name':4,'Shanh_name':5,'Manil_name':6}

for hero in Heroes_name:
hero=[r[0].value for r in sheet.Range('Table[%s]' % hero)]

TI3_name=[x for x in TI3_name if str(x)!='None']


But I got the following error:

local variable 'TI3_name' referenced before assignment





Anyway... how can I write my program shorter?

UPDATE:
Alexey Bogomolov wrote:
"Unfortunately, it is still not clear for me, what are you going to have"

I wrote a program that is too long and I'm sure that I can write it shorter. How can I do that? (it is a complete program code)

import os
import win32com.client
import numpy as np

from utils import DATA_DIR
def read_from_excel():
place=os.path.join(DATA_DIR,'book1.xlsx')
Excel=win32com.client.Dispatch('Excel.Application')
wb=Excel.Workbooks.Open(place)
sheet=wb.ActiveSheet


TI3_name=[r[0].value for r in sheet.Range("Table[TI3_Name]")]

TI4_name=[r[0].value for r in sheet.Range("Table[TI4_Name]")]
TI5_name=[r[0].value for r in sheet.Range("Table[TI5_Name]")]
Frank_name=[r[0].value for r in sheet.Range("Table[Frank_name]")]
Shanh_name=[r[0].value for r in sheet.Range("Table[Shanh_name]")]
Manil_name=[r[0].value for r in sheet.Range("Table[Manil_name]")]

TI3_value=[r[0].value for r in sheet.Range("Table[TI3]")]

TI4_value=[r[0].value for r in sheet.Range("Table[TI4]")]
TI5_value=[r[0].value for r in sheet.Range("Table[TI5]")]

Frank_value=[r[0].value for r in sheet.Range("Table[Frank]")]
Shanh_value=[r[0].value for r in sheet.Range("Table[Shanh]")]
Manil_value=[r[0].value for r in sheet.Range("Table[Manil]")]

TI3_name=[x for x in TI3_name if str(x)!='None']
TI4_name=[x for x in TI4_name if str(x)!='None']
TI5_name=[x for x in TI5_name if str(x)!='None']

Frank_name=[x for x in Frank_name if str(x)!='None']
Shanh_name=[x for x in Shanh_name if str(x)!='None']
Manil_name=[x for x in Manil_name if str(x)!='None']

TI3_value=[x for x in TI3_value if str(x)!='None']
TI4_value=[x for x in TI4_value if str(x)!='None']
TI5_value=[x for x in TI5_value if str(x)!='None']

Frank_value=[x for x in Frank_value if str(x)!='None']
Shanh_value=[x for x in Shanh_value if str(x)!='None']
Manil_value=[x for x in Manil_value if str(x)!='None']

TI3=list(zip(TI3_name,TI3_value))
TI4=list(zip(TI4_name, TI4_value))
TI5=list(zip(TI5_name, TI5_value))

Frank=list(zip(Frank_name, Frank_value))
Shanh=list(zip(Shanh_name, Shanh_value))
Manil=list(zip(Manil_name, Manil_value))

TI3=np.array(TI3)
TI4=np.array(TI4)
TI5=np.array(TI5)

Frank=np.array(Frank)
Shanh=np.array(Shanh)
Manil=np.array(Manil)

return TI3, TI4, TI5, Frank, Shanh, Manil

def find_number_uniq_heroes(TI3, TI4, TI5, Frank, Shanh, Manil):
x1=TI3[:,0]
x2=TI4[:,0]
x3=TI5[:,0]
x4=Frank[:,0]
x5=Shanh[:,0]
x6=Manil[:,0]

x=np.concatenate((x1,x2,x3,x4,x5,x6),0)
y=np.unique(x)


return y
def number_popular(x,y,hero):
try:
i=x.tolist().index(hero)
return float(y[i])
except ValueError:
return float(0)


def count_popularity(uniq_heroes,TI3, TI4, TI5, Frank, Shanh, Manil):

n=len(uniq_heroes)
popul=np.zeros(n)

x1=TI3[:,0]
x2=TI4[:,0]
x3=TI5[:,0]
x4=Frank[:,0]
x5=Shanh[:,0]
x6=Manil[:,0]

y1=TI3[:,1]
y2=TI4[:,1]
y3=TI5[:,1]
y4=Frank[:,1]
y5=Shanh[:,1]
y6=Manil[:,1]


i=0
for hero in uniq_heroes:

pop=float(0)
pop=number_popular(x1,y1,hero)
pop=pop+number_popular(x2,y2,hero)
pop=pop+number_popular(x3,y3,hero)
pop=pop+number_popular(x4,y4,hero)
pop=pop+number_popular(x5,y5,hero)
pop=pop+number_popular(x6,y6,hero)

popul[i]=float(pop)
i=i+1


return list(zip(uniq_heroes,popul))

def sort_mass(x):

x=sorted(x,key=lambda x_entry:x_entry[1],reverse=True)

return x


TI3, TI4, TI5, Frank, Shanh, Manil = read_from_excel()
uniq_heroes=find_number_uniq_heroes(TI3, TI4, TI5, Frank, Shanh, Manil)
un_her_with_popul=count_popularity(uniq_heroes,TI3, TI4, TI5, Frank, Shanh, Manil)
sorted_mas=sort_mass(un_her_with_popul)

print(sorted_mas[:10])

Answer

Since I have no Excel installed, I've converted your file to csv, and wrote a simple parser, which returns table headers and a dictionary with every header and it's values. I hope, this is a good starting point, since you can do whatever you want with dictionary values)

import csv


def return_column(rownubmer, delimiter_value):
    column = {}
    column_values = []   
    file = open('sheet1.csv')
    data = csv.reader(file, delimiter=delimiter_value)
    header = next(data)
    for row in data:
        try:
            column_values.append(row[rownubmer])
        except IndexError:
            pass
    column[header[rownubmer]] = column_values
    return column

n = return_column(0, ',')
m = return_column(1, ',')

print(n,m)

The output is:

{'TI3_name': ['Batrider', 'Outworld Devourer', 'Lifestealer', 'Visage', 'Chen', 'Io', 'Alchemist', 'Dark Seer', 'Weaver', "Nature's Prophet", 'Dragon Knight', 'Naga Siren', 'Gyrocopter', 'Rubick', 'Nyx Assassin', 'Razor', 'Puck', 'Shadow Demon', 'Enchantress', 'Lone Druid', 'Bane', 'Keeper of the Light', 'Anti-Mage', 'Beastmaster', 'Treant Protector', 'Bounty Hunter', 'Timbersaw', 'Queen of Pain', 'Enigma', 'Storm Spirit', 'Clockwerk', 'Shadow Fiend', 'Vengeful Spirit', 'Jakiro', 'Magnus', 'Crystal Maiden', 'Tinker', 'Spectre', 'Windranger', 'Leshrac', 'Juggernaut', 'Sand King', 'Ursa', 'Doom', 'Chaos Knight', 'Earthshaker', 'Tidehunter', 'Mirana', 'Templar Assassin', 'Venomancer', 'Phantom Lancer', 'Morphling', 'Lina', 'Viper', 'Skywrath Mage', 'Silencer', 'Kunkka', 'Disruptor', 'Pudge', 'Lion', 'Axe', 'Ancient Apparition', 'Faceless Void', 'Luna', 'Lich', 'Clinkz', 'Undying', 'Sven', 'Spirit Breaker', 'Pugna', 'Omniknight', 'Slark', 'Dazzle', 'Meepo', 'Bloodseeker', 'Shadow Shaman', 'Slardar', 'Zeus', 'Wraith King', 'Bristleback', 'Tiny', 'Invoker', 'Broodmother', 'Ogre Magi', 'Night Stalker', 'Warlock', 'Sniper', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']} {'TI3': ['4.97', '4.69', '4.66', '4.63', '4.32', '4.14', '4.07', '4.07', '3.77', '3.4', '3.3', '3.12', '2.9', '2.84', '2.41', '2.31', '2.31', '2.04', '1.98', '1.88', '1.82', '1.54', '1.54', '1.42', '1.42', '1.36', '1.3', '1.2', '1.17', '1.14', '1.11', '1.08', '1.05', '1.05', '0.93', '0.86', '0.86', '0.74', '0.68', '0.68', '0.62', '0.62', '0.59', '0.56', '0.52', '0.49', '0.46', '0.46', '0.43', '0.37', '0.37', '0.37', '0.34', '0.28', '0.22', '0.19', '0.15', '0.15', '0.15', '0.12', '0.12', '0.12', '0.12', '0.12', '0.12', '0.09', '0.09', '0.09', '0.06', '0.06', '0.06', '0.06', '0.06', '0.06', '0.06', '0.06', '0.06', '0.03', '0.03', '0.03', '0.03', '0.03', '0.03', '0.03', '0.03', '0.03', '0.03', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']}

In case you encounter IndexError exception, you can change the for-loop to something like:

for row in data:
    try:
        lis.append(row[rownubmer])
    except IndexError:
        pass

But actually the script should just work...