How to separate a row with many repeating columns into multiple rows

HowTo
#1

Suppose you have Apple and Facebook stock data in a single row with many repeating columns per stock symbol like below.

image
image.png3078×1850 320 KB

image
image.png3064×1800 304 KB

So basically you want to separate this into 2 rows, one for Apple stock and the other for Facebook stock.

Here is the step to achieve this

  1. Select columns only related to Apple stock
  2. Gather Columns and create key and value column

image
image.png1124×836 58.7 KB

  1. Clean up Column names (remove AAPL.quote.)

image
image.png1052×774 49.3 KB

  1. Create a branch data frame from the source step
  2. On the branch, Select columns only related to Facebook stock.

image
image.png1112×798 57 KB

  1. Clean up Column names (remove FB.quote.)
  2. On the branch, Gather Columns and create key and value column

image
image.png1098×814 49.4 KB

  1. merge the main data frame (Apple) and the branch data frame (Facebook)

image
image.png1630×796 62.4 KB

  1. Spread the key and value

image
image.png3094×248 28.7 KB

10 Now you have two rows one for Apple stock and the other for Facebook

You can download EDF file from here to see Data Wrangling details.