Skip to content

Latest commit

 

History

History
87 lines (62 loc) · 3.34 KB

README.md

File metadata and controls

87 lines (62 loc) · 3.34 KB

Untangling Panhandled Spreadsheets

For ease of interaction in a spreadsheet, people will often "join" together a parent dataset and a child dataset together in the same tab or sheet in a way that doesn't repeat parent data over and over. For example, let's say you have a parent dataset (e.g., companies) and a child dataset (e.g., contacts at those companies).

The Panhandled Dataset

Parent Dataset

A B
valueA1 valueB1
valueA2 valueB2

Child Dataset

A C D
valueA1 valueC1 valueD1
valueA2 valueC2 valueD2
valueA1 valueC3 valueD3
valueA1 valueC4 valueD4
valueA2 valueC5 valueD5
valueA2 valueC6 valueD6

Panhandling Them Together

  1. Join together with A as the common column
A B C D
valueA1 valueB1 valueC1 valueD1
valueA1 valueB1 valueC3 valueD3
valueA1 valueB1 valueC4 valueD4
valueA2 valueB2 valueC2 valueD2
valueA2 valueB2 valueC5 valueD5
valueA2 valueB2 valueC6 valueD6
  1. Drop redundant "parent" data
A B C D
valueA1 valueB1 valueC1 valueD1
valueC3 valueD3
valueC4 valueD4
valueA2 valueB2 valueC2 valueD2
valueC5 valueD5
valueC6 valueD6

My Motivation

I needed to import into Salesforce the data from a panhandled spreadsheet with company and contact information mixed together. For this, I needed a properly formed database (tidy) structure to import into the Account object and a separate one to import into the Contact object. I looked through the tidyr package, but didn't see anything that would do what I needed done.

Untangling the Dataset

This is what the script does.

Files

Input

  1. data/original.xlsx

Output

  1. data/parent.csv
  2. data/child.csv

Assumptions for Input File

  • The different "pans" (parent with children) can be separated by blank lines or not.
  • All information except the "pans" has been deleted.
  • The first column contains the keys for the parent and child datasets

Process

  1. Read in the spreadsheet as our original table to work with
  2. In the original table, clear any blank rows used to separate parent/child groupings
  3. In the original table, collect together the rows with first columns that aren't blank and store in the parent table
  4. From the original table, fill the blanks cells in column 1 with the last non-blank value in that column (working down)
  5. Save just column 1 into a one-column (key) table we will use later
  6. From the original table, create a temporary table consisting of only the rows that are blank in the first column
  7. Using this temporary table, get the name of the first column in this table to figure out which number column is the beginning of the child columns in the original (where the panhandle meets the pan)
  8. Use this information to drop the columns that aren' t child columns and bind the remaining columns with the single key column created above to make a child table
  9. Return to the parent table to drop the columns that aren't parent columns
  10. Save out the the two new tables