Transfering data from Excel to Python variables

Question

I have an Excel dataset from which I am supposed to take some data. The problem is that there are 6 sheets and each one of them has data. Does anyone know a efficient way of getting the data to a vector like:

data = [ [sheet1, column 1], [sheet1, column 2], ..., [sheetx, column x]]

I tried this but I can´t because there are to much dimensions (rows) for each column

sheet_Power = pd.read_excel('DatasetTelmo.xlsx', 'Contracted_Power')
sheet_GenerationTariff = pd.read_excel('DatasetTelmo.xlsx', 'Generation Tariff')
sheet_GenerationType = pd.read_excel('DatasetTelmo.xlsx', 'Generation Type')
sheet_Generation = pd.read_excel('DatasetTelmo.xlsx', 'Generation')
sheet_ConsuptionTariff = pd.read_excel('DatasetTelmo.xlsx', 'Consumption Tariff')
sheet_Type = pd.read_excel('DatasetTelmo.xlsx', 'Type')
sheet_Consuption = pd.read_excel('DatasetTelmo.xlsx', 'Consumption')
sheet_Flexibility = pd.read_excel('DatasetTelmo.xlsx', 'Flexibility')

xls = pd.ExcelFile('DatasetTelmo.xlsx')  
sheet_name = xls.sheet_names

data = []
for names in sheet_name:
    if names == 'Type' or names == 'Generation Type':
        continue
    else:
        sheet = pd.read_excel('DatasetTelmo.xlsx', names)
        data_from_sheet = [[sheet.values[r,c] 
                      for c in range(0,sheet.shape[1])] 
                          for r in range(0,sheet.shape[0])]
        data.extend(data_from_sheet)


consumer = np.array(sheet_Type.values.tolist()).ravel()
generation = np.array(sheet_GenerationType.values.tolist()).ravel()

true_labels_names =[i + ' - ' +  j for i, j in zip(consumer, generation)]

Answer 1

Please have a look at the python pandas package... Especially at the excel_read()-function.

 pandas.read_excel(io, sheet_name=0, header=0, names=None, index_col=None, usecols=None, squeeze=None, dtype=None, engine=None, converters=None, true_values=None, false_values=None, skiprows=None, nrows=None, na_values=None, keep_default_na=True, na_filter=True, verbose=False, parse_dates=False, date_parser=None, thousands=None, decimal='.', comment=None, skipfooter=0, convert_float=None, mangle_dupe_cols=True, storage_options=None)

Paraphrasing the documentation:

• io would be your file (eg: file://localhost/path/to/table.xlsx.)
• sheetname would be your sheet, which is used as Dataframe.
  You can either use a string (containing the name of the sheet) or use integers (with 0 being the 1st sheet, 1 being the 2nd sheet, and so on.)

Happy Coding.