Automation of Scheduling Interview
There could be cases that people need to help schedule interview time for both interviewer and interviewee. Nonetheless, the process of that is quite annoying, especially when it’s in large number.
When facing this difficulty, I wonder if it’s possible to make it purely automatic. So let’s take a look into it. First of all, the google form is chosen as the tool to get the dummy data for our test. And we design our form as the following, which is quite simple.
And then we could get the result from the Google sheet connected with the form.
At the same time, time provided by the interviewer is generated as well.
Then before just jumping into the coding stuff, we’ll explain the concept of how to make it automatic. So let’s check the back-of-the-envelope jotted down by me first. In a real scenario, some people may be available all the time but some may just have only one time slot. Logically to see, the arrangement should start with people with less available time. After that, we then should pick the person with a lower overall supply rate among the people with the same number of available time. The supply rate here is defined by us as the ratio of supply count and demand count (supply count/demand count). The overall supply rate would be the aggregation of the weighted supply rates. For example, if person B has available days of Monday, Wednesday, and Thursday, then the overall supply rate would be :
(1/3)*(Monday supply count/Monday demand count) + (1/3) * (Wednesday supply count/Wednesday demand count) + (1/3) * (Thursday supply count/Thursday demand count)
After picking the minimum overall supply rate among the targeted batch, then it comes to choosing the day for this person. The day with the highest supply rate should then be chosen as this means supply is relatively more. In doing so, we can increase the probability of having most people get the day for an interview. So this is the concept of how we are going to get the interview day for each interviewee, then let’s go to check how to implement it via Python.
First of all, we get the data from the Google sheet and then create the interviewee dataframe.
Then we get the demand count by the group result of the date.
Then we concatenate the demand count table to the table showing the supply count from the Google sheet.
Then the next step goes to create the sequence for assigning the day to the interviewee. The following is the code of how this is created.
Then we would have the result like the following table. From here we could know that the sequence is sorted based on the available day number, overall supply rate, and then finally is the time to input the data. That means if the former 2 factors are the same, then we would choose the person who provides the data earlier.
Then the last but not least, we have to execute the process of assigning the day to each interviewee as the following code:
Finally, we would have the result showing each day’s interviewee.
The above is the initial idea of automating the whole process of assigning the interviewee to the time provided by the interviewers. However, I do think there might be a better approach or algorithm to optimize the whole process. If you have interest in this and spot any better method, feel free to give your feedback or comment to me. Thanks for reading the article! Hope you enjoy this! :)