Introduction
This project was developed while working on a complete renovation of the Atlanta Evergreen Marriott Conference Resort in Stone Mountain, Georgia. The purpose of this project was to determine the amount of materials needed for each room type on levels 3, 4, and 5. I was assigned to this project in the middle of construction and one of the main tasks I had was to audit the material we had on site and had ordered and compare it to the material we needed to complete the job.
To begin, I performed a takeoff of all the materials in each room type using OASIS takeoff software. Once the takeoff was complete in OASIS, the data was exported to excel. The output excel file form OASIS can be seen in link below.
The condition names in the excel file are the codes used for each material. For example, CG stands for case goods and AC stands for accessories such as towel rods and grab bars. The only way the OASIS software could output the data was formatted to show the room types separate by level and the corresponding material for each room type on that floor. Since the goal was to see the total material amount required for each level of the three levels, the rooms that have the same materials needed to all be summed to see the total amount of that specific material on that level. This could have been done very tediously and manually by looking through the OASIS outputted excel sheet and scanning the document for each material in each room type and summing them that way. Since there were 10 different room types and 109 different material types, identifying the material one by one in each room type and adding them together manually would have taken an extremely long time to complete. Therefore, it was decided to come up with a program that would scan the excel sheet and find each material with the same condition name and automatically sum the values.
To begin automating this process, I used OASIS to export all the condition names of each material alphabetically to excel (just the condition named themselves). Since OASIS is a new takeoff software still under development, the condition names for each material could only be exported alphabetically by themselves. The software would not output to excel all the material counts for each room type sorted alphabetically by condition name which would have made the manual process of calculating the totals even more tedious. The material condition name excel file can be seen in link below.
Below shows the start of the code beginning with importing pandas and numpy. The alphabetized data frame shows NaN in inconvenient locations so this was handled first.
Here I take away the first two columns leaving the condition name in column 1 to make the data frame easier to work with.
Using the original 01_OASIS_takeoff excel file, I took the data from one individual room type and create a excel file of just that one individual room type. See example of excel file here.
After the program was ran for one room type, the rooms.xlsx file was then updated to have the data from a new room type and the whole program was ran again. Once the excel file was transformed into a data frame, the data frame was adjusted to be easier to work with.
Below shows further adjusting of the data frame to get rid of all the NaN values.
To make the data frames easier to understand, the column headers were adjusted to accurately represent what each column stands for.
In preparation for the audit, the locations below were used to make sure the correct locations were being found in each data frame.
The code below was the portion of the project where the audit took place. The first for loop iterated over all the project’s material condition names, and the second for loop iterated over the condition names for the materials in the selected room. In this case, the selected room type was Suite 500. The if statement works by iterating over all the condition names for each material type in the selected room, and when the condition name for the room type matches the condition name in the total list of condition names, the material count for that room type is added to the main data frame (df) under the selected room type. The locations in the data frame where the material counts were added on were strategically setup using the for loops and .loc so that the final data frame would have the condition names still in alphabetical order with the corresponding material count next to the correct condition name under the correct room type.
Once this process was repeated and completed for each of the room types in the project, the data was outputted to excel separated by levels 3, 4, and 5 to complete the examination. Below shows the top 30 rows of the final excel sheet for level 3. To see the full excel sheet, click link below.
