Python (Excel，字典)如何写得更短

Question

我写了一个太长的程序，我相信我可以写得更短。

这是一个从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'

正如您所看到的，这不是一个紧凑的代码。

我怎么写得更短？

我试着用字典..。

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']

但我得到了以下错误：

local variable 'TI3_name' referenced before assignment

总之..。我如何写我的程序更短？

更新： Alexey Bogomolov写道：“不幸的是，对我来说还不清楚，你会有什么？”

我写了一个太长的程序，我相信我可以写得更短。我怎么能这么做？(这是一个完整的程序代码)

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 1

由于没有安装Excel，所以我将您的文件转换为csv，并编写了一个简单的解析器，该解析器返回带有每个标题及其值的表头和字典。我希望，这是一个很好的起点，因为你可以用字典值做你想做的任何事情)

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)

产出如下：

{'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', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']}

如果遇到IndexError异常，可以将for-循环更改为如下所示：

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

但实际上剧本应该能用..。