reading data from excel

回答済み

hajar shirneshan

2020年12月10日 07:46

• 
• 

I have a question about defining parameters in Gurobi
I have a table like the excel file that I attached
and I want to define a parameter patient_time(i,j,k)
i:patient(1,2,3,4,5)
j:qualification(doctor,nurse)
k:day(day1,...,day5)
How can I define it?
Thanks for your help

0

• 正式なコメント

  

  Simranjit Kaur

  • Gurobi Staff

  2025年06月23日 03:03 編集済み

  This post is more than three years old. Some information may not be up to date. For current information, please check the Gurobi Documentation or Knowledge Base. If you need more help, please create a new post in the community forum. Or why not try our AI Gurobot?.
• 

  Jaromił Najman

  • Gurobi Staff

  2020年12月14日 15:33 編集済み

  Hi Hajar,

  It is more of a Python question rather than a Gurobi question.

  You can use the pandas module to read excel files into Python code.

  It is always better to not leave any excel cells empty as these are then most likely handled as NaNs which leads to other problems in most cases. In the following I assume that your excel table is filled with ones and zeros.

  import gurobipy as gp
  from gurobipy import GRB
  
  import pandas as pd
  
  patient = [1,2,3,4,5]
  qualification = ["doctor","nurse"]
  day = ["day1","day2","day3","day4","day5"]
  
  data = pd.read_excel(r'test.xlsx') # excel file called test.xlsx
  
  doctor_column_names = [col for col in data if col.startswith('doctor')]
  nurse_column_names = [col for col in data if col.startswith('nurse')]
  
  parameter_dict = {} # dictionary holding the final parameter values
  
  for q in qualification:
    for d in day:
      for p in patient:
        parameter_dict[(q,d,p)] = 0
  
  for doctor in doctor_column_names:
    column = data[doctor] # column holding all entries below a "doctor"
    day = column[0] # get the day 
    for patient in range(1,6): # loop over the 5 patients and get the values
      parameter_dict[("doctor",day,patient)] = column[patient]
  
  for nurse in nurse_column_names:
    column = data[nurse] # column holding all entries below a "nurse"
    day = column[0] # get the day 
    for patient in range(1,6): # loop over the 5 patients and get the values
      parameter_dict[("nurse",day,patient)] = column[patient]
  
  print(parameter_dict[("nurse","day3",1)])

  \(\texttt{doctor_column_names}\) holds the list

  ['doctor', 'doctor.1', 'doctor.2', 'doctor.3', 'doctor.4']

  which is due to the fact that multiple columns are called \(\texttt{doctor}\). The same applies to the \(\texttt{nurse}\) columns.
  Note that this is only one way of achieving your goal and may not be the best/fastest one.

  Best regards,
  Jaromił

  0

投稿コメントは受け付けていません。