Eric Farkas

I've been told that it's good practice to blog about code you've written, and the thought process behind it. Doing so gives people an insight into how you approach problems, which at times is more important than what exactly you wrote. This is such a post.

My full-time job is as a mechanical draftsman for an HVAC contractor. Our work is mostly in high-end residential apartment buildings and hotels in the 5 boroughs of New York City. We build steam and water-based HVAC systems (sometimes both in the same building). At work, we use QuickPen Pipe Designer 3D, on top of AutoCAD, to draw 3D models of our mechanical piping.

A PRV station

Coordination

To get pipe to the field, the draftsmen first take the contract drawings from the architect and engineer, and we draw our pipe on the floor layout. We then attend coordination meetings, where all trades (plumbers, electricians, sheetmetal, etc.) work on a set of drawings that has everyone's "stuff" on them. This lets us resolve conflicts and hits. Pipe moves around, duct gets raised or lowered, and so on. When all trades sign off on the drawing for a particular floor, the draftsmen are then free to go back to their offices, update their own shop drawings, and work on fabricating their pipe. The adoption of BIM and related software stacks promises to make/has made some of these steps unncessary, but many jobs are still coordinated like this.

Fabrication

The next goal for the draftsman is to hand the fitters in the shop what are called "fabrication sheets", or "cut sheets", that detail what needs to be fabricated, and what tag that piece should be given.

Fabrication pieces

In the above image, R-03 is the tag number given to a piece of pipe that is 1-1/2" in diameter, 9'-6-1/4" long, and has a 1-1/2"x3/4" tee on one end. We only want one of these pieces tagged R-03. A fitting on the end of a piece like this is called a "make-up fitting". Usually, threaded pipe like this only has one make-up fitting. The other end of the pipe is a threaded end, and the fitters put these pieces together to construct all the piping on a given floor. We send "shop drawings" to the field that detail how these pieces fit together:

Shop drawing with pipes tagged

QuickPen makes it (almost) easy to do all this. I draw my pipe in 3D, at real elevations. I then use QuickPen's ISO manager to create my fabrication pieces by literally clicking on a piece of pipe, and it's make-up fitting. QuickPen auto-increments the counter as I create pieces.

The Problem

While QuickPen makes it easy to tag your pipe for fabrication, and make a shop drawing with ballons for each tag, it doesn't make it THAT easy to create the cut sheets. When you ask QuickPen to export your Bill Of Materials so that you can make a spreadsheet to print out and give to the fabricators in the shop, you're given the option to export to a CSV. Here is what QuickPen gives you, truncated for our purposes:

"Iso Number","Size","Quantity","Length","Long Description"
"R-02","1-1/2""","1","9'-6 1/4""","PIPE, S/STD A-53 GRADE B ERW T.B.E."
"R-02","1-1/2""x3/4""","1","","TEE REDUCING, THRD, 150LB MALLEABLE IRON"
"S-13","2""","1","9'-6 1/4""","PIPE, S/STD A-53 GRADE B ERW T.B.E."
"S-13","2""x3/4""","1","","TEE REDUCING, THRD, 150LB MALLEABLE IRON"

And so on, for each piece. The CSV is always un-ordered by ISO number (tag number). So basically what you get is a listing of each individual component of a fabrication piece, and not the whole piece itself. Sometimes this is OK, because there are pieces that may have more than two components. But 95% of the time a fabrication piece is a length of pipe and a fitting (tee, elbow, valve, coupling, etc.). I have to massage this CSV so that it's useful to the shop fabricators.

For a while I did this by hand, which was tedious. I made mistakes as well, which were costly in real dollar terms. When union steamfitters call the office while they're standing on the 50th floor deck of a building, in the middle of winter, screaming that your pipe doesn't fit...well, that costs everyone money in wasted time and material. In this way, drafting is different from much of the programming I've done recently. Aside from the processing of subscriptions, bugs that occur on TalentSoup don't immediately result in lost money, but simply an annoyance. Drafting is different: a mistake could cost tens of thousands of dollars.

So after spending so much time on these CSVs, and making some bad mistakes, I decided to let the computer handle the CSVs for me. This problem was always begging to be solved by a script. The result is cut-sheets.

Walk-Through

Now that I told you more than you wanted to know about mechanical piping, I want to quickly walk though the parsing script.

First, I shamelessly steal Rails' blank? method, which is useful in this context. Then I set up some constants that map to the column indexes in the CSV. It's more readable this way. Then I read the CSV in as an array.

require 'csv'

class Object
  def blank?
    respond_to?(:empty?) ? empty? : !self
  end
end

ISO_NUMBER  = 0
SIZE        = 1
QUANTITY    = 2
LENGTH      = 3
DESCRIPTION = 4

fabrication_pieces_as_array = CSV.read("isos.csv")

Sometimes, QuickPen's Bill of Materials generator brings in pieces of pipe or fittings that are not part of any fabrication piece. I remove them from my array so I can process the CSV knowing that I am only working with fabrication pieces. The motivation for want to assume this will be clear shortly.

fabrication_pieces_as_array.delete_if { |x| x[ISO_NUMBER].blank? }

Next, I change some of the descriptions of the pieces to conform to our shop standard. Also, if the current array element is a fitting (which doesn't have a length as it's not a piece of pipe), then I put the fitting size into the description. This is so when I make this element the make-up fitting of a piece of pipe, I get the fitting size in the description without any other manipulation.

fabrication_pieces_as_array.each do |x|
  x[DESCRIPTION].gsub!(/, 150.*/, '')
  x[DESCRIPTION].gsub!(/ 150.*/, '')
  x[DESCRIPTION].gsub!(' PIPE', '')
  x[DESCRIPTION] = x[SIZE] + " " + x[DESCRIPTION] if x[LENGTH].blank?
end

Now that my fabrication_pieces_as_array object is cleaned up, I can start combining pipe pieces with their make-up fittings. First I create a hash using Ruby's awesome group_by method. What I get back is a Hash whose keys are the ISO_NUMBER, or tag number.

  {"R-02"=>[["R-02", "1-1/2\"", "1", "9'-6 1/4\"", "PIPE, S/STD A-53 GRADE B ERW T.B.E."],
            ["R-02", "1-1/2\"x3/4\"", "1", "", "1-1/2\"x3/4\" TEE REDUCING, THRD"]],
   "R-03"=>[["R-03", "1-1/2\"", "1", "9'-6 1/4\"", "PIPE, S/STD A-53 GRADE B ERW T.B.E."],
            ["R-03", "1-1/2\"x3/4\"", "1", "", "1-1/2\"x3/4\" TEE REDUCING, THRD"]],
   "R-04"=>[["R-04", "1-1/2\"", "1", "9'-6 1/4\"", "PIPE, S/STD A-53 GRADE B ERW T.B.E."],
            ["R-04", "1-1/2\"x3/4\"", "1", "", "1-1/2\"x3/4\" TEE REDUCING, THRD"]]}

iso_pieces = fabrication_pieces_as_array.group_by{|row| row[ISO_NUMBER]}

Now I can test to see how many pieces each tag number has. As I said before, 95% of the pieces I make will will have two components: a length of pipe and a fitting. For cases where the pieces are more complex, I handle those manually, usually by making a sketch of the piece in QuickPen. I'll make a note on the cut sheet to "See Sketch" for that pieces. Otherwise, I now go through my original array. For each element I test to see if it's ISO_NUMBER number has two components, and if the LENGTH column is not nil?, meaning this element is the pipe portion of the fabrication piece that also has one make-up fitting. I add the fitting row to the pipe row because the pipe row has the pipe LENGTH; it just "feels" more natural to do it that way.

fabrication_pieces_as_array.each do |x|
  if iso_pieces[x[ISO_NUMBER]].size == 2 and !x[LENGTH].blank?
    x[DESCRIPTION] = iso_pieces[x[ISO_NUMBER]].select{ |y| y[LENGTH].blank? }.first[DESCRIPTION]
  end
end

Now that I've replaced the DESCRIPTION column of each piece of pipe with the corresponding make-up fitting, I can remove the rows of make-up fittings, which, again, don't have a LENGTH value. Then I sort the array by ISO_NUMBER. This is not necessary, as I can do this in Excel when add this information to our template, but it saves me a step.

fabrication_pieces_as_array.delete_if {|x| x[LENGTH].blank? and iso_pieces[x[ISO_NUMBER]].size == 2}.sort!{ |x, y| x[ISO_NUMBER] <=> y[ISO_NUMBER] }

Lastly, I generate a new CSV with all the pieces and their make-up fittings. Pieces that have more > 2 components are also listed, but they have not been "combined" together, so I'll resolve them manually. At the bottom of the CSV I also generate a bill of materials, which is a count of each fitting, so the shop knows how many of each to order from the supply house. Ruby's group_by gets all the distinct pieces into a hash, and then it's as simple as getting the size of each key.

CSV.open("cut-sheets.csv", "wb") do |csv|
  csv << ["ISO", "SIZE", "QUANTITY", "LENGTH", "DESCRIPTION"]
  fabrication_pieces_as_array.each { |x| csv << [x[ISO_NUMBER], x[SIZE], x[QUANTITY], x[LENGTH], x[DESCRIPTION]] }
  fabrication_pieces_as_array.group_by { |x| x[DESCRIPTION] }.each { |key, values| csv << ["", "", values.size, "", key] }
end

The finished CSV looks like this:

Iso Number,Size,Quantity,Length,Long Description
R-01,"1-1/4""",1,"9'-6 1/4""","PIPE, S/STD A-53 GRADE B ERW T.B.E."
R-02,"1-1/2""",1,"9'-6 1/4""","1-1/2""x3/4"" TEE REDUCING, THRD"
R-03,"1-1/2""",1,"9'-6 1/4""","1-1/2""x3/4"" TEE REDUCING, THRD"
R-04,"1-1/2""",1,"9'-6 1/4""","1-1/2""x3/4"" TEE REDUCING, THRD"
R-05,"1-1/4""",1,"9'-6 1/4""","1-1/4""x3/4"" TEE REDUCING, THRD"
R-08,"1-1/4""",1,"9'-6 1/4""","1-1/4""x3/4"" TEE REDUCING, THRD"
R-09,"1-1/4""",1,"9'-6 1/4""","1-1/4""x3/4"" TEE REDUCING, THRD"
R-10,"1-1/4""",1,"9'-6 1/4""","1-1/4""x3/4"" TEE REDUCING, THRD"
R-11,"1-1/4""",1,"9'-6 1/4""","1-1/4""x3/4"" TEE REDUCING, THRD"
...
...
"","",3,"","1-1/2""x3/4"" TEE REDUCING, THRD"
"","",8,"","1-1/4""x3/4"" TEE REDUCING, THRD"
"","",9,"","2""x3/4"" TEE REDUCING, THRD"
"","",1,"","2-1/2""x3/4"" TEE REDUCING, THRD"
"","",1,"","2-1/2""x2"" REDUCER, CONC "
"","",1,"","4""x3/4"" THREAD-OLET"

I can plug that into our template, and I am good to go. Manually, this would take up to an hour depending on how many pieces are on the drawing. This script takes less than a second.

This is a simple script but it's made my life easier, and I got to write out my thought process, which was fun. If you have any suggestions for improvements, feel free to reach out.

See Also

cut-sheets on GitHub