Extracting Excel Data in particular format as an input to a python file

Viewed 107

Good day all, As a project, I undertook a task where I am supposed to extract data from an excel file which would be provided as an input to another python script. The thing is, the extracted data has to be in a specific format for the script to work. Here are some details of what I have done so far in my python script:

import tkinter as tk
from tkinter import filedialog
import pandas as pd

root= tk.Tk()

canvas1 = tk.Canvas(root, width = 400, height = 400, bg = 'lightsteelblue')
canvas1.pack()

def getfile ():

    global df
    import_file_path = filedialog.askopenfilename()
    df = pd.read_excel(import_file_path)
    df = df.fillna("")
    df = df.values.tolist()
    print(df)


browseButton_Excel = tk.Button(text='Import Excel File', command=getfile, bg='green', fg='white', font=('helvetica', 12, 'bold'))
canvas1.create_window(200, 200, window=browseButton_Excel)

root.mainloop()`

The excel file looks like this: raw_data

I want to extract data from this excel file in the format below:

jd = [  
    [(2, 3304), (4, 2942)],  
    [(2, 1119), (4, 987)],  
    [(2, 1025), (2, 859), (4, 961)],  
    [(2, 1094), (2, 916), (4, 1025)],  
    [(2, 576), (2, 488), (4, 552)],  
    [(2, 450), (2, 384), (4, 431)],  
    [(0, 2665), (1, 1281), (3, 3670)],  
    [(0, 1690), (1, 826), (3, 2321)],  
    [(0, 2230), (1, 1078), (3, 3068)],  
    [(2, 1495), (5, 1220)]  
]

This is my first time here so sorry if I have missed anything. Thank you in advance.

~UPDATE~ The data I want to extract would be given as an input to 'jobs_data' variable in the scheduling problem as given in Google or-tools. It has following code:

import collections

from ortools.sat.python import cp_model


def MinimalJobshopSat():

    model = cp_model.CpModel()

    jobs_data = [
        [(2, 3304), (4, 2942)],
        [(2, 1119), (4, 987)],
        [(2, 1025), (2, 859), (4, 961)],
        [(2, 1094), (2, 916), (4, 1025)],
        [(2, 576), (2, 488), (4, 552)],
        [(2, 450), (2, 384), (4, 431)],
        [(0, 2665), (1, 1281), (3, 3670)],
        [(0, 1690), (1, 826), (3, 2321)],
        [(0, 2230), (1, 1078), (3, 3068)],
        [(2, 1495), (5, 1220)],
    ]

    machines_count = 1 + max(task[0] for job in jobs_data for task in job)
    all_machines = range(machines_count)

    horizon = sum(task[1] for job in jobs_data for task in job)

    task_type = collections.namedtuple('task_type', 'start end interval')

    assigned_task_type = collections.namedtuple('assigned_task_type',
                                            'start job index duration')

    all_tasks = {}
    machine_to_intervals = collections.defaultdict(list)

    for job_id, job in enumerate(jobs_data):
        for task_id, task in enumerate(job):
            machine = task[0]
            duration = task[1]
            suffix = '_%i_%i' % (job_id, task_id)
            start_var = model.NewIntVar(0, horizon, 'start' + suffix)
            end_var = model.NewIntVar(0, horizon, 'end' + suffix)
            interval_var = model.NewIntervalVar(start_var, duration, end_var,
                                            'interval' + suffix)
            all_tasks[job_id, task_id] = task_type(start=start_var,
                                                   end=end_var,
                                                   interval=interval_var)
            machine_to_intervals[machine].append(interval_var)

    for machine in all_machines:
        model.AddNoOverlap(machine_to_intervals[machine])

    for job_id, job in enumerate(jobs_data):
        for task_id in range(len(job) - 1):
            model.Add(all_tasks[job_id, task_id +
                                1].start >= all_tasks[job_id, task_id].end)

    obj_var = model.NewIntVar(0, horizon, 'makespan')
    model.AddMaxEquality(obj_var, [
        all_tasks[job_id, len(job) - 1].end
        for job_id, job in enumerate(jobs_data)
    ])
    model.Minimize(obj_var)

    solver = cp_model.CpSolver()
    status = solver.Solve(model)

    if status == cp_model.OPTIMAL:
        assigned_jobs = collections.defaultdict(list)
        for job_id, job in enumerate(jobs_data):
            for task_id, task in enumerate(job):
                machine = task[0]
                assigned_jobs[machine].append(
                    assigned_task_type(start=solver.Value(
                        all_tasks[job_id, task_id].start),
                                       job=job_id,
                                       index=task_id,
                                       duration=task[1]))

        output = ''
        for machine in all_machines:
            assigned_jobs[machine].sort()
            sol_line_tasks = 'Machine ' + str(machine) + ': '
            sol_line = '           '

            for assigned_task in assigned_jobs[machine]:
                name = 'job_%i_%i' % (assigned_task.job, assigned_task.index)
                sol_line_tasks += '%-10s' % name

                start = assigned_task.start
                duration = assigned_task.duration
                sol_tmp = '[%i,%i]' % (start, start + duration)
                sol_line += '%-10s' % sol_tmp

            sol_line += '\n'
            sol_line_tasks += '\n'
            output += sol_line_tasks
            output += sol_line

        print('Optimal Schedule Length: %i' % solver.ObjectiveValue())
        print(output)
    
MinimalJobshopSat()
1 Answers

Maybe you should try to export the excel file to a .csv (Comma-separated values) file. Than you can read the file with python:

f = open("excel.csv", "r")
data = f.read()

After that you can split the values by lines and semicolons and create your multi dimensional array.

Related