Exploratory Community

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

#1

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

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

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

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

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

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

  1. Spread the key and value

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.