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