View source for 2.90:Use Case: Transposing Rows and Columns with Row Match

So you have a set of documents.  Most of them have a very straightforward table.  It's easily extracted using the ''[[Row Match]]'' method.  But every now and then, you get another document with a table presenting the same data, but in a wildly different table structure.  So different, in fact, that the data is presented as rows instead of columns.  One solution would be to create two different [[Data Model]]s for two different [[Document Type]]s with two different [[Data Table]]s using two different [[Table Extraction|extraction techniques]].  However, there may be a more elegant solution, using what we call the "Pivot Table Method" and a "Pivot Row Extractor".

{|cellpadding="10" cellspacing="5"
|-style="background-color:#36b0a7; color:white"
|style="font-size:18pt"|'''&#128526;'''||''Special thanks to BIS team member Larry Bellipanni (with help from Bradley White) for contributing the idea for this article!''
|}

== The Problem ==
{|cellpadding=10 cellspacing=5
|style="width:40%"|
This use case comes from the world of oil and gas.  Oil and gas companies receive and produce production reports detailing volumes and sales of materials produced for a particular oil and gas well.  They are essentially big tables of information.  But there's a lot of different ways to make a table.  The vast majority of the documents for this project looked something like this.

This has a very straightforward table structure.  It is well suited for the ''Row Match'' method.  You could fairly easily write a pattern to match the data on each row of this table.
|
[[File:Pivot Row 01.png]]
|-
|
However, sometimes, this project would encounter documents that look something like this one.  It has a completely different table structure.  In fact it uses a combination of field labels for the "Well" and "Field" etc values, and a table for the "Oil" "Water" and "Gas" data.
|
[[File:Pivot Row 02.png|center]]
|}

While these tables present data in different ways, they share a great deal of data points.  For this explanation, we will focus on eight shared data points.  These data points will become the '''Data Columns''' for our '''Data Table'''.

* Well Name (also known as "Completion Name")
* API Number
* Oil Produced
* Oil Sales
* Gas Produced
* Gas Sales
* Water Produced
* Producing Days

{|cellpadding=10 cellspacing=5
|style="width:40%"|
The problem is part of the data that is just contained on a single row on this document...
|
[[File:Pivot Row 03.png]]
|-
|
...is in multiple columns on a table in this one, as seen by the yellow and blue highlighted data.

Or, it's not even in the table at all, as seen by the green highlighted data.
|
[[File:Pivot Row 04.png|center]]
|}

So, the question is:  How can we use the ''Row Match'' method to extract this data, populating it into a single table structure, using a single '''''Row Extractor''''', from two very different kinds of documents?

== The Solution ==

The answer is to use what we call the "Pivot Table Method".  The idea is to translate the columnar data in the second documents into rowed data like the first document.  We will "pivot" from data organized in columns to data organized in rows.  Or another way to think of it is we are going to transpose the X and Y axis of the table.  We will also end up "pivoting" the data listed as fields in repeating sections to cells in the table's rows.

Let's get into Grooper and see how it's done!  If you'd like a copy of this '''Batch''' and '''Content Model''' to inspect in your own Grooper environment (version 2.90), you can download the zip file below and import it.
* [[Media:Pivot Table - O&G Production Data (2.90).zip]]

<tabs style="margin:20px">
<tab name="The Documents" style="margin:20px">
=== The Documents: Oil and Gas Production Data Tables ===
{|
|style="width:40%" valign=top|
For this use case, imagine the vast majority of our documents look like this one.  These are mocked up documents coming from "Portmanteau Oil Company".  It contains data pertaining to production volume and sales of oil and gas for a particular well site over a one month period.

This is a very basic, spreadsheet style table.  We could write a single regex pattern to match the pattern of each row.  This informs Grooper how to structure the table and extract data for each cell, using the ''[[Row Match]]'' table extraction method.
|
[[File:Pivot Row 06.png|center]]
|-
|valign=top|
However, some of these reports come from a ''different'' company, "Scrableword Producers".  They output their production data reports in a much different format.  Unless you are the one controlling how a document is made, you may not have a lot of control over how information is structured from one company to the next.  

This presents a problem of variability.  There is no "one-size-fits-all" solution here. So the question is, what's the easiest, most efficient solution to extract both tables?

We will show you our solution using the "Pivot Table Method" described earlier, manipulating the data inside this table structure to conform it to the one we want.
|
[[File:Pivot Row 05.png|center]]
|}
</tab>
<tab name="The Content Model" style="margin:20px">
=== The Content Model ===
{|cellpadding=10 cellspacing=5
|-
|style="width:40%" valign=top|
To accomplish this task, we need a '''Content Model'''.  '''Content Models''' define classification and extraction logic for a batch of documents.
|
[[File:Pivot Row - Content Model.png]]
|-
|valign=top|
This '''Content Model''' has two '''Document Types''' one for each mock vendor using a different table structure on their documents.  The are as follows:
* Portmanteau - For "Portmanteau Oil Company".  This company uses the very basic table structure on its production data reporting document..
* Scrableword - For "Scrableword Producers".  This company uses the more complex table structure, presenting us our problem we have to solve.
|
[[File:Pivot Row - Doc Types.png]]
|-
|valign=top|
For this exercise, we are ''only'' focusing on extracting the table.  To extract any kind of data, we need a '''Data Model''' to detail what we want to extract (as well as define the extractors returning the data from the documents).
|
[[File:Pivot Row - Data Model.png]]
|-
|valign=top|
Since we are extracting a table, we will need a '''Data Table''' object in the '''Data Model'''.  This object defines configuration settings for table extraction, most importantly the table extraction '''''Method'''''.  For this exercise, we will use the ''[[Row Match]]'' method.
|
[[File:Pivot Row - Data Table.png]]
|-
|valign=top|
A table without columns is, frankly, not a table.  The '''Data Table''' must have one or more '''Data Column''' children objects.  These define which of the table's columns we want to extract.  We could extract all the columns' data, but for our use case, we don't want to.  We will a single '''Data Table''', named "Production", with eight '''Data Columns''':
* Well Name
* API
* Oil Prod
* Oil Sales
* Gas Prod
* Gas Sales
* Water Prod
* Days
|
[[File:Pivot Row - Data Columns.png]]
|}
</tab>
<tab name="The Primary Row Extractor" style="margin:20px">
=== The Primary Row Extractor ===

{|cellpadding=10 cellspacing=5
|style="width:40%" valign=top|
We will focus on the easiest document first, the "Portmanteau" '''Doc Type'''.  This is doubly useful because, in our use case, the majority of documents use this table structure.

# What we have here is a '''Data Type''' with a pattern that matches each row of the table. It is named "TBL-RM - Production [Portmanteau]".
#* This is the first thing the ''Row Match'' method needs to know in order to function.  For straightforward tables like this, if a table's structure can be described as rows of information, and you can pattern match what that row looks like, you can model the structure of the entire table, whether it's one row, twenty, or a million.
# In our results list, you can see each row is returned as a full line.  Since, we have a result for each row line, we've successfully modeled our table's structure.
|
[[File:Pivot Row - Grooper Screenshots 01.png]]
|-
|
# The pattern for this extractor is located in the child '''Data Format''' named "Row Pattern".
#* Note: This could have also just been an internal pattern on the parent '''Data Type''' using its '''''Pattern''''' property.
# The '''''Value Pattern''''' essentially matches each cell's data with '''''[[Tab Marking]]''''' enabled to match the large gaps between each cell as tab characters (<code>\t</code>). The full pattern is as follows:
<pre>
(?<Well_Name>[^\r\n\t\f]+)\t
(?<API>\d{10})\t
(?<Oil_Prod>\d{1,6}\.\d{2})\t
(?<Oil_Sales>\d{1,6}\.\d{2})\t
\d{1,6}\.\d{2}\t
(?<Gas_Prod>\d{1,6}\.\d{2})\t
(?<Gas_Sales>\d{1,6}\.\d{2})\t
(?<Water_Prod>\d{1,6}\.\d{2})\t
\d{1,6}\.\d{2}\t
\d{1,6}\.\d{2}\t
(?<Days>\d{2})
</pre>
# This pattern also takes advantage of Named Groups.  Named Groups will create data instances for each portion of regex captured by the group encapsulated in parenthesis.  For the ''Row Match'' method, if the data instance's name matches the name of a '''Data Column''' it will populate that column with the data instance's text.  For example, the Named Group "Days" matches the '''Data Column''' named "Days".  The two digits picked up by the regex in that group <code>\d{2}</code> will populate the table cells for the "Days" column.
# This '''Data Format''' also uses a '''''Prefix Pattern''''' to only match rows that begin on a new line or the begining of a string (in this case the first line of the document) using the regex <code>\n|^</code>.
# It also uses a '''''Suffix Pattern''''' to only match rows that terminate at the end of a line or the end of a string (in this case the last line of the document) using the regex <code>\r|$</code>
#* Note: The '''''Value Pattern''''' would still match ''without'' the '''''Prefix''''' and '''''Suffix Patterns'''''.  However, it is generally regarded as best practice to include these patterns (or patterns like them) as positional anchors for tables.  This gives more specificity to your row patterns and can eliminate false positives in many cases.
|valign=top|
[[File:Pivot Row - Grooper Screenshots 02.png]]
|-
|valign=top|
If we were to set this extractor as our '''Data Table's''' '''''Row Extractor''''', we would extract the document's table data with no problem.

However, this is only the case for the "Portmanteau" documents.
|valign=top|
[[File:Pivot Row - Grooper Screenshots 03.png]]
|-
|valign=top|
This extractor will not work at all for the "Scrableword" documents.  It has a different table structure.  The extractor does not properly match the "table".

We need a completely different extractor for this table, pivoting the data into the table structure we have created for our "Portmanteau" documents with what we're calling a "Pivot Row Extractor"
|valign=top|
[[File:Pivot Row - Grooper Screenshots 04.png]]
|}
</tab>
<tab name="The Pivot Row Extractor" style="margin:20px">
=== The Pivot Row Extractor ===

Instead of a table presenting the data we want in single rows, this document lays out the data in a repeating section.  Some of the data are fields within these repeating sections.  Some of the data is inside a table within these repeating section, but the data is organized in columns instead of rows.  Overall, a completely different way of structuring this data on the page.

{|cellpadding=10 cellspacing=5
|style="width:40%" valign=top|
# Rather than using a single pattern to model the structure of a table, we will use an '''Ordered Array''' to find the same data elements within these sections.
# What we produce is four results, each containing all the data we want in each section.  We will pivot this information, using the data instances inside each result, to match the rows of our '''Data Table'''.
# Instead of Named Groups in a single regex line producing these data instances, multiple '''Data Formats''', as well as Named Groups within certain of these '''Data Formats''' will produce the data instances.
|
[[File:Pivot Row - Grooper Screenshots 05.png]]
|-
|valign=top|
# The '''Data Format''' named "Well Name" will create a data instance that matches our '''Data Column''' named "Well Name".  
#* This is analogous to the Named Group of our Primary Row Extractor populating the "Well Name" column using a Named Group to create a data instance of a portion of the regex line.
# The extraction technique here is to use the field's label "Well: " as a '''''Prefix Pattern'''''
# And capture the field's value (the well's name) with a simple segment pattern <code>[^\r\n\t\f]+</code>
#* Note: You will need to enable '''''Tab Marking''''' in the '''''Preprocessing Options''''' of the "Properties" tab for this to work.
# We are left with four well names for four sections.  This data will populate the "Well Name" column of our '''Data Table'''.
|
[[File:Pivot Row - Grooper Screenshots 06.png]]
|-
|valign=top|
The "API" '''Data Format''' uses similar logic, just using a different '''''Prefix Pattern''''' and '''''Value Pattern''''' to match the appropriate data.
|
[[File:Pivot Row - Grooper Screenshots 07.png]]
|-
|valign=top|
Now for the fun part.  How do we take a table organized by columns and flip it into a table organized by rows?  That is what the three "Pivot Row Pattern" child '''Data Formats''' are for.  

We have three patterns (one for each child '''Data Format'''), each one picking up a row of the table in these repeating sections.  In those rows contain elements of our '''Data Table'''.  We will use Named Groups to call out where that data should go in our '''Data Column'''.  Again, if the data instance's name matches the name of our '''Data Column''', it doesn't matter how it's structured or where it is in the array of data we ultimately create for the parent '''Data Type'''.  As long as the name matches, it will get placed in the appropriate column.

# Now, it's just a matter of writing a pattern to match each row.  We will start with the first row detailing information about oil production, and the "Pivot Row Pattern - Oil" '''Data Format'''
# We can hone in on where the "Oil" row is on this document using a '''''Prefix Pattern'''''
#* The regex <code>oil \(bbls\):\s</code> will match the row's label "Oil (bbls): " on the document.
# This row contains three pieces of information relevant for our table: oil production, oil sales, and producing days.  We simply need to place the portions of the '''''Value Pattern's''''' regex matching this data into Named Groups that match our '''Data Column''' names: Oil Prod, Oil Sales, and Days.  The '''''Value Pattern''''' seen here is as follows:
<pre>
(?<Oil_Prod>\d{1,3}(,\d{3})?\.\d{2})\t
(?<Oil_Sales>\d{1,3}(,\d{3})?\.\d{2})\t
\d{1,3}\.\d{2,5}\t
\d{1,3}(,\d{3})?\.\d{2}\t
\d{1,3}(,\d{3})?\.\d{2}\t
(?<Days>\d{2})
</pre>
:* Note: If your '''Data Columns''' have spaces in them, you must replace the spaces with an underscore (<code>_</code>) in the regex pattern.
|valign=top|
[[File:Pivot Row - Grooper Screenshots 08.png]]
|-
|valign=top|
The next "Pivot Row" is even easier.  The second row of this table details information about water production.  We only need a single piece of data from this row (and it happens to be in the first column of the table on the document).
# For the "Pivot Row Pattern - Water" '''Data Format'''
# We hone in on where the "Water" row is using the '''''Prefix Pattern''''' <code>water \(bbls\):\s</code>
# And for our '''''Value Pattern''''' we simply match data in the first column with a Named Group matching the '''Data Column''' named "Water Prod"
#* Note: We could have also named the '''Data Format''' "Water Prod" instead of using a Named Group.  Either way creates a named data instance that matches our '''Data Column'''!
|
[[File:Pivot Row - Grooper Screenshots 09.png]]
|-
|valign=top|
Lastly, the "Pivot Row Pattern - Gas" '''Data Format''' captures the data in the "Gas" row, using two Named Groups, one for the "Gas Prod" and "Gas Sales" '''Data Columns'''.
|
[[File:Pivot Row - Grooper Screenshots 10.png]]
|-
|valign=top|
We're almost done.  We just need to organize the results of our child '''Data Formats''' in a way the '''Data Table''' can use as a '''''Row Extractor'''''.  To do that, we need to collate this data using the '''''Collation''''' property of the parent '''Data Type'''.

# If we leave this '''Data Type's''' '''''Collation''''' property set to ''Individual''...
# You can see we get individual results for the various data elements we want to use to populate our table.  
#* But, we need to re-order it into results the '''Data Table's''' '''''Row Extractor''''' can use to create data instances out of each row, also known as "Row Instances".  These Row Instances will then pull the data instances created by our '''Data Format''' to populate the correspondingly named '''Data Columns'''.
|
[[File:Pivot Row - Grooper Screenshots 11.png]]
|-
|valign=top|
To do this we are going to use the ''Ordered Array'' Collation Provider in '''''Flow Layout'''''.  A [[Flow Collation|Flow]] collated ''Ordered Array'', will look for results of its child extractors in the order they are listed from top to bottom and return them as a single result, ''as long as they appear in the text flow in that order''.

So you're reading the text data like you would a book, left-to-right and top-to-bottom.  You see a result the "Well Name" data format extracts and keep reading through the text flow.  You get a result from the next child extractor, the "API" '''Data Format'''.  Good, that's the next extractor in the list.  You keep reading along and get one for the "Pivot Row Pattern - Oil" extractor, then the "Pivot Row Pattern - Water" and last the "Pivot Row Pattern - Gas" extractor.

As long as each subsequent child extractor result follows the one before it in the text flow of the document, you will get a single result containing all the data each extractor returns.  If it's out of order, or one element is missing, no luck.  But here, we get the result we're wanting.

# With the '''''Collation''''' property set to ''Ordered Array''...
# And the '''''Flow Layout''''' property set to ''Enabled''...
# And the '''''Separator Expression''''' property set to ''<code>.*</code>'', we return four results for the four sections of oil and gas production data that will return to our '''Data Table'''.
#* The '''''Separator Expressoin''''' or '''''Maximum Character Distance''''' properties are important aspects of how '''''Flow Layout''''' functions.  You must tell Grooper what you expect to find in between each element of the ordered array.  By setting the expression to <code>.*</code> we are essentially saying there can be any character (the <code>.</code> wildcard) of any length (the <code>*</code> quantifier) in between each child extractor's result in the text flow.
|valign=top|
[[File:Pivot Row - Grooper Screenshots 12.png]]
|-
|valign=top|
If then set this '''Data Type''' as our '''Data Table's''' '''''Row Extractor''''', we will successfully extract the data we want from this document, pivoting the sections and inverted table structure into the one we want.

But we're not ''quite'' done yet.  We have two extractors for our two documents.  We want a single extractor to use for our '''''Row Extractor'''''.
|
[[File:Pivot Row - Grooper Screenshots 13.png]]
|}
</tab>
<tab name="Put It All Together" style="margin:20px">
=== Put It All Together ===
{|cellpadding=10 cellspacing=5
|valign=top|
How do you create one extractor out of two different extractors producing different results on different documents?  Just make them both children of another '''Data Type'''.  The new parent '''Data Type''' will receive both sets of values for each child extractor.
|
[[File:Pivot Row - Grooper Screenshots 14.png]]
|-
|valign=top|
|
This single extractor now extracts the desired data for two very different kinds of documents.
|
[[File:Pivot Row - Grooper Screenshots 16.png]]
|-
|valign=top|
We've pivoted the data inside the repeating sections of this document, into the table structure that works well for the majority of documents in our document set.
|
[[File:Pivot Row - Grooper Screenshots 17.png]]
|}
</tab>
</tabs>

[[Category:Articles]]
[[Category:Version 2.90]]