シフト作成part2

関数

qiita.rb

import pandas as pd
pd.options.display.precision = 0
import numpy as np
from random import random
import time, os
import openpyxl as opx


def read_excel():
 
 df = pd.read_excel(r"C:\Users\keisu\OneDrive\デスクトップ\月次シフト.xlsx", index_col = 0, header=1)
 df = df.fillna("0")
 df = df.replace("0", 0).replace("年休", 3).replace("希望", 4).replace("優先", 5)

 kiso = df.iloc[2:, 0:31].reset_index(drop = True)
 name = df.iloc[2:, 0:1]
 group = df.iloc[2:,31:32].reset_index(drop = True)
 group.columns = ["G"]
 skill = df.iloc[2:,32:33].reset_index(drop = True)
 skill.columns = ["スキル"]
 kouritu = df.iloc[2:,33:34].reset_index(drop = True)
 kouritu.columns = ["効率"]
 holiday = df.iloc[2:,34:35].reset_index(drop = True)
 holiday.columns = ["必要休日数"]
 people = df.iloc[0:2, 0:31].reset_index(drop = True)
 people.index = ["必要日勤数", "必要夜勤数"]
 
 return kiso, name, group, skill, kouritu, holiday, people


def first_gene(kiso, holiday):
    days = len(kiso.columns)
    kiso_copy = kiso.copy()
    
    for k in range(len(kiso_copy)):
     h = []
     while len(h) < holiday.loc[k][0]:
         n = np.random.randint(1, days + 1)
         if not n in h:
             h.append(n)
     #h以外のリスト=出勤数 filter(n = n !== h)　　
     not_h =  [i for i in range(1, days + 1) if i not in h]
     for i in not_h:
         #日勤が1、夜勤が2
         x = 1 if 0.8 > random() else 2
         if kiso_copy.loc[k,i] == 0:
            kiso_copy.loc[k,i] = x

    return kiso_copy
  
  
def holiday_fix(kiso, holiday):
    
    #人ごとに見る、kiso_copyで出勤をすべて1にして、判定用に使う、希望休を消さないため
    kiso_copy = kiso.copy()
    kiso_copy.replace(2,1)
    for k in range(len(kiso)):
     days = len(kiso.columns)
     if np.count_nonzero((kiso_copy.iloc[k:k + 1] == 0)|(kiso_copy.iloc[k:k + 1] == 3) |(kiso_copy.iloc[k:k + 1] == 4))!= holiday.iloc[k][0]:
         s = np.count_nonzero((kiso_copy.iloc[k:k + 1] == 0)|(kiso_copy.iloc[k:k + 1] == 3) |(kiso_copy.iloc[k:k + 1] == 4)) - holiday.iloc[k][0]
         buf = 0
         c1 = 0 if s > 0 else 1
         c2 = 1 if c1 == 0 else 0
         #増減したい休日数に達するまでループ　夜勤数は固定するため、休み⇔日勤のみ
         while buf < abs(s):
            n = np.random.randint(1, days)
            if kiso.loc[k][n] == c1:
                    buf += 1
                    #休日数を操作
                    kiso.loc[k,n] = c2
    return kiso


def yakin_fix(kiso, holiday):
    
    #日ごとにみる、kiso_copyで、判定用に使う、希望休を消さないため
    kiso_copy = kiso.copy()
    for k in range(len(kiso.columns)):
     if np.count_nonzero(kiso_copy.iloc[:,k:k + 1] == 2) != people.iloc[1,k]:
         s = np.count_nonzero(kiso_copy.iloc[:,k:k + 1] == 2) - people.iloc[1,k]
         buf = 0
         c1 = 2 if s > 0 else 1
         c2 = 1 if c1 == 2 else 2
         #増減したい夜勤数に達するまでループ　休日数は固定するため、夜勤⇔日勤のみ
         while buf < abs(s):
            n = np.random.randint(0, len(kiso))
            if kiso.iloc[n,k] == c1:
                    buf += 1
                    #夜勤数を操作
                    kiso.iloc[n,k] = c2
    return kiso


def evaluation_function(kiso):

 # eva1は、休みを0、出勤を1に統一し、判定用に使う
 eva1 = kiso.copy()
 eva1.replace(2, 1).replace(3, 0).replace(4, 0)
 eva2 = kiso.copy()
 score = 0

 # メンバーごとに評価
 for k in range(len(eva1)):
     # 結合
     x = ''.join([str(i) for i in np.array(eva1.iloc[k:k + 1]).flatten()])
     # 5連勤以上
     score += np.sum([((2 - len(i))**2)*-1 for i in x.split("0") if len(i) > 5])
     # 3連休以上
     score += np.sum([((1 - len(i))**2)*-1 for i in x.split("1") if len(i) > 3])
     # 飛び石連休
     score += -10*(len(x.split("010"))-1)
     # 優先休
     
 
 # 日勤必要数
 score += np.sum([abs(people.iloc[0,k] - np.sum(eva2.iloc[:,k] == 1)) * -4 for k in range(len(eva2.columns))])
 
 # 効率
 K = []
 Kouritu = []
 for i in range(len(eva1.columns)):
    for j in range(len(eva1)):
        if eva1.iloc[j,i] == 1:
          K.append(j)
    if len(K) != 0:
     Kouritu.append(round(np.mean([kouritu.iloc[k,0] for k in K])))    
 score += np.var(Kouritu)* -4
    
 return score


def crossover(ep, sd, p1,p2):
 
    days = len(p1.columns)
    p1 = np.array(p1).flatten()
    p2 = np.array(p2).flatten()
    
    ch1 = []
    ch2 = []
    
    for p1_, p2_ in zip(p1,p2):
      x = True if ep > random() else False
      if x == True:
        ch1.append(p1_)
        ch2.append(p2_)
      else:
        ch1.append(p2_)
        ch2.append(p1_)
    
    ch1, ch2 = mutation(sd, np.array(ch1).flatten(), np.array(ch2).flatten())
    
    ch1 = pd.DataFrame(ch1.reshape(int(len(ch1)/days), days))
    ch2 = pd.DataFrame(ch2.reshape(int(len(ch2)/days), days))
    ch1.columns = [i+1 for i in range(len(ch1.columns))]
    ch2.columns = [i+1 for i in range(len(ch2.columns))]
    
    return ch1, ch2


def mutation(sd, ch1, ch2):
  
  #夜勤は変えない
  x = True if sd > random() else False
  if x == True:
        rand = np.random.permutation([i for i in range(len(ch1))])
        rand = rand[:int(len(ch1)//10)]
        for i in rand:
         if ch1[i] == 1:
            ch1[i] = 0
         
         elif ch1[i] == 0:
            ch1[i] = 1
            
  x = True if sd > random() else False
  if x == True:
        rand = np.random.permutation([i for i in range(len(ch1))])
        rand = rand[:int(len(ch2)//10)]
        for i in rand:
         if ch2[i] == 1:
            ch2[i] = 0
         
         elif ch2[i] == 0:
            ch2[i] = 1
  return ch1, ch2


def kouritu_out(x, kouritu):

 days = len(kiso.columns)
 n_kouritu = []
 y_kouritu = []
 n_avekouritu = []
 y_avekouritu = []

 for i in range(len(x.columns)):
    for j in range(len(x)):
        if x.iloc[j,i] == "日":
          n_kouritu.append(j)
        elif x.iloc[j,i] == "夜":
          y_kouritu.append(j)
        
    if len(n_kouritu) != 0:
     n_avekouritu.append(round(np.mean([kouritu.iloc[k,0] for k in n_kouritu]),2))
     y_avekouritu.append(round(np.mean([kouritu.iloc[k,0] for k in y_kouritu]),2))   
    elif len(n_kouritu) == 0:
     n_avekouritu.append(0)
     y_avekouritu.append(0)  
        
 n_avekouritu = pd.DataFrame([n_avekouritu])
 n_avekouritu.columns = [i for i in range(1, days + 1)]
 n_avekouritu.index = ["日勤平均効率",]
 y_avekouritu = pd.DataFrame([y_avekouritu]) 
 y_avekouritu.columns = [i for i in range(1, days + 1)]
 y_avekouritu.index = ["夜勤夜勤効率",]

 return n_avekouritu, y_avekouritu

処理

qiita.rb

first_length = 10
elite_length = 2
gene_length = 1
ep = 0.5
sd = 0.1

kiso, name, group, skill, kouritu, holiday, people = read_excel()

parent = []

for i in range(first_length):
  kiso = first_gene(kiso, holiday)
  kiso = holiday_fix(kiso, holiday)
  kiso = yakin_fix(kiso, holiday)
  score = evaluation_function(kiso)
  parent.append([score,kiso])

for i in range(gene_length):
  korenp = np.array(parent, dtype=object)
  parent = sorted(korenp,key=lambda x: -x[0])
  parent = parent[:elite_length]

  if i == 0 or top[0] < parent[0][0]:
    top = parent[0]
  else:
    parent.append(top)

  print(f"第{i+1}世代")
  print(top[0])

  children = []
  for k1,v1 in enumerate(parent):
   for k2,v2 in enumerate(parent):
     if k1 < k2:
       ch1,ch2 = crossover(ep,sd, v1[1], v2[1])
       ch1 = yakin_fix(ch1, holiday)
       ch2 = yakin_fix(ch2, holiday)
       ch1 = holiday_fix(ch1, holiday)
       ch2 = holiday_fix(ch2, holiday)   
       score1 = evaluation_function(ch1)
       score2 = evaluation_function(ch2)
       children.append([score1,ch1])
       children.append([score2,ch2])
  
  parent = children.copy()


x = top[1].replace(1, "日").replace(2, "夜").replace(0, "●").replace(3, "年休").replace(4, "●'")
n_set = pd.DataFrame([(x == '日').sum()])
n_set.index = ["日勤数",]
y_set = pd.DataFrame([(x == '夜').sum()])
y_set.index = ["夜勤数",]
n_kou, y_kou = kouritu_out(x, kouritu)

x.insert(0, 'name', name.index)
holiday.insert(0, 'name', name.index)
# n_peo = people.drop("夜勤必要数")

pd.options.display.precision = 2
x = pd.merge(x, holiday, how = "left")
x = pd.concat([x,people,n_set,y_set,n_kou,y_kou])
# y = pd.DataFrame(x.loc["日勤設定数"]-x.loc["日勤必要数"])
# x.drop("日勤必要数", inplace = True)
x

path = r"C:\Users\keisu\OneDrive\デスクトップ\シフト試作品.xlsx"
x.to_excel(path)