Tidy your Data: Reshape Data

Session 8: Reshape Data

8.1 Tidying data

In order to work with your data more efficiently, you should store it as tidydata. In tidy data, each variable forms a column, and each observation forms a row. This is crucial for data manipulation, visualization and further analyses. If you keep all your data tidy, you will not have to spend hours transforming one format of data into another, in order to run analyses or to make graphics in R.

In the last 4 sessions, you will learn how to tidy your data with ease. For this session, you will familiarize yourself with the functions pivot_longer() and pivot_wider(), which are modern alternatives to gather() and spread(). With pivot_longer() and pivot_wider(), you can manipulate the layout of your data set.

Go through the Tidy Data Topic in RStudio Primer Reshape Data. Then, read the chapter Pivoting in R for Data Science and complete the exercises there.

 

Tip 1: The cheatsheet Data Wrangling with dplyr and tidyr has visual examples of what the tidying functions do to the data set, which will be of use in these last sessions.

Assignment

For this assignment, you will use the billboard dataset that comes with the tidyr() package. This dataset shows the billboard rank of songs in the year 2000.

  1. Look the the billboard data set. You will need to load tidyr() and dplyr() individually, because the data set is embedded in the packages.
  2. Using a pivot function, make a new dataset called billboard_longer, and store the weeks to a variable named week and the values to rank. Drop the missing values.
  3. Calculate the average rank of each song. Which three songs have the highest average rank? Did you know these songs already? You should get a table as such:
    track average
    Maria, Maria 10.50000
    Music 13.45833
    Bye Bye Bye 14.26087
    Hot Boyz 14.33333
    Independent Women Pa… 14.82143
    Be With You 15.85000
    Try Again 16.65625
    I Knew I Loved You 17.36364
    My Love Is Your Love 17.85714
    There U Go 18.62500
    Bent 18.64103
    Thong Song 19.17857
    What A Girl Wants 19.29167
    Doesn’t Really Matte… 19.41667
    This I Promise You 19.61538
    Case Of The Ex (What… 19.63333
    No More 19.92308
    Come On Over Baby (A… 19.95238
    Say My Name 20.87500
    He Wasn’t Man Enough 21.00000
    I Wanna Know 21.20455
    Everything You Want 21.43902
    Bring It All To Me 21.58621
    I Try 21.66667
    It’s Gonna Be Me 21.68000
    Most Girls 21.77778
    It Feels So Good 22.12500
    Jumpin’ Jumpin’ 22.93750
    Breathe 23.01887
    You Sang To Me 23.46875
    I Just Wanna Love U … 23.52381
    Show Me The Meaning … 23.75000
    I Turn To You 24.13636
    Incomplete 24.30769
    That’s The Way It Is 24.85714
    Gotta Tell You 25.09091
    Never Let You Go 25.86364
    Kryptonite 26.47170
    Absolutely (Story Of… 26.48148
    Amazed 26.72727
    He Loves U Not 26.89286
    Shape Of My Heart 28.05000
    Wifey 28.19048
    Get It On.. Tonite 28.43750
    Then The Morning Com… 28.76923
    Back Here 30.64516
    Blue 30.70000
    (Hot S**t) Country G… 30.88235
    Between Me And You 31.15000
    Wonderful 31.19048
    Big Pimpin’ 31.30000
    Let’s Get Married 31.37500
    Bag Lady 32.35000
    Separated 32.35000
    The Real Slim Shady 32.84211
    Shake Ya Ass 33.05000
    All The Small Things 33.30435
    With Arms Wide Open 33.82979
    Pinch Me 33.95238
    Take A Picture 34.15000
    He Can’t Love U 34.90000
    I Need You 35.60000
    Oops!.. I Did It Aga… 36.00000
    Otherside 36.18182
    I Wish 36.40000
    Higher 36.85965
    Meet Virginia 36.92593
    Thank God I Found Yo… 37.00000
    You’re A God 37.00000
    She Bangs 37.11111
    Don’t Think I’m Not 37.16667
    Give Me Just One Nig… 37.65000
    Desert Rose 38.00000
    What’s Your Fantasy 38.86364
    Only God Knows Why 39.00000
    Learn To Fly 39.66667
    Big Deal 40.80000
    Faded 41.58333
    Party Up (Up In Here… 41.76190
    American Pie 43.00000
    That’s The Way 43.20000
    Forgot About Dre 43.25000
    The Next Episode 43.25000
    Swear It Again 43.35000
    My Next Thirty Years 44.20000
    The Best Day 44.70000
    I Think I’m In Love … 44.87500
    How Do You Like Me N… 45.45000
    Bounce With Me 45.75000
    Yes! 45.80000
    Crash And Burn 46.55000
    Without You 47.10000
    Liar 47.25000
    Hanginaround 47.30000
    My Best Friend 47.33333
    Untitled (How Does I… 47.41176
    Best Of Intentions 47.81818
    My First Love 47.85000
    Lucky 48.18182
    What’Chu Like 48.40000
    Cowboy Take Me Away 48.45000
    Stay The Night 49.10000
    Another Dumb Blonde 49.42857
    A Puro Dolor (Purest… 49.50000
    What About Now 49.90000
    Dancin’ 49.90909
    Broadway 50.00000
    Goodbye Earl 50.60000
    Just Friends 50.65000
    Better Off Alone 51.25000
    It’s My Life 51.60000
    This Time Around 51.71429
    Auld Lang Syne (The … 51.80000
    I Don’t Wanna 52.05000
    I Like It 52.85000
    Graduation (Friends … 53.58333
    Shake Your Bon-Bon 53.61111
    From The Bottom Of M… 54.50000
    The Little Girl 54.50000
    Born To Fly 54.70000
    We Danced 54.90000
    You Can Do It 55.66667
    Shackles (Praise You… 55.90000
    I Learned From The B… 56.00000
    What Do You Say 56.05000
    Where I Wanna Be 56.08000
    Wobble Wobble 56.22222
    Just Another Day In … 57.15000
    Just Be A Man About … 57.15000
    Callin’ Me 57.92308
    It Must Be Love 58.10000
    Falls Apart 58.45000
    She’s More 58.80000
    Prayin’ For Daylight 59.00000
    I Lost It 59.45000
    Back At One 59.83333
    911 60.00000
    Dance Tonight 60.10526
    The One 60.20000
    Smile 60.30000
    Go On 60.70000
    Love Is Blind 60.87500
    Rhythm Divine 61.11765
    Couldn’t Last A Mome… 61.80000
    Crybaby 62.00000
    Got To Get It 62.05000
    Don’t Say You Love M… 62.33333
    Simple Kind Of Life 62.38462
    Who Let The Dogs Out 62.50000
    I Will Love Again 62.76471
    Whoa! 62.94118
    I Will.. But 63.05000
    Love’s The Only Hous… 63.15000
    Dear Lie 63.28571
    www.memory 63.40000
    One Night Stand 63.41176
    The Chain Of Love 63.50000
    Buy Me A Rose 63.90000
    I Wanna Be With You 64.17391
    One Voice 64.52632
    Been There 65.00000
    Some Things Never Ch… 65.00000
    Last Resort 65.25000
    Whatever 65.61111
    The Light 65.73684
    Lessons Learned 65.75000
    Emotional 66.20000
    Carlene 66.26316
    Aaron’s Party (Come … 66.26667
    Kiss This 66.95000
    Loser 67.10000
    The Great Beyond 67.57143
    Pop A Top 67.58824
    Open My Heart 67.75000
    Caught Out There 67.83333
    Because You Love Me 67.84615
    Stan 68.06667
    This Woman Needs 68.22222
    Unconditional 68.55000
    The Itch 68.55556
    What I Need To Do 69.00000
    Feels Like Love 69.15000
    None Of Ur Friends B… 69.26667
    Anything 69.37500
    Pop Ya Collar 69.42857
    Let’s Make Love 69.50000
    Country Comes To Tow… 69.60000
    The Way I Am 69.80000
    Feelin’ Good 70.29412
    There You Are 70.84615
    G’D Up 71.14286
    I Don’t Wanna Kiss Y… 71.20000
    Flowers On The Wall 71.30000
    The Best Of Me 71.47059
    I’ll Be 71.95000
    Where You Are 72.00000
    Your Everything 72.00000
    Could I Have This Ki… 72.26316
    Girls Dem Sugar 72.40000
    The Bad Touch 72.58333
    Best Friend 72.80000
    You Owe Me 73.25000
    That Other Woman 73.81818
    No More Rain (In Thi… 73.82353
    Deep Inside 74.00000
    I Think God Can Expl… 74.00000
    What You Want 74.60000
    Taking You Home 74.95000
    Someday Out Of The B… 75.20000
    Cold Day In July 75.30000
    Angels 75.42105
    As We Lay 75.62500
    That’s What I’m Look… 75.66667
    Do What You Gotta Do 75.83333
    Same Script, Differe… 76.11111
    Private Emotion 76.20000
    More 76.27273
    U Don’t Love Me 76.50000
    You’ll Always Be Lov… 76.84211
    No Me Dejes De Quere… 77.00000
    No Matter What They … 77.10000
    It Was 77.30000
    It’s Always Somethin… 77.42857
    Californication 77.68421
    Treat Her Like A Lad… 77.76471
    Tricky Tricky 78.66667
    I Need A Hot Girl 78.84615
    Rollin’ 78.94118
    Deck The Halls 79.00000
    Original Prankster 79.11111
    Nothing As It Seems 79.33333
    Mirror Mirror 79.66667
    Deep Inside Of You 79.83333
    You Should’ve Told M… 80.15000
    Breathe And Stop 80.20000
    Sexual 80.27586
    I Belong To You 80.44444
    Smoke Rings In The D… 80.60000
    Sleepwalker 81.00000
    Bad Boyz 81.06667
    Left & Right 81.20000
    Do It Again (Put Ya … 81.55556
    What’s My Name 82.00000
    My Baby You 82.05000
    If I Am 82.20000
    Change Your Mind 82.60000
    Can’t Fight The Moon… 82.95000
    Another Nine Minutes 83.12500
    Real Live Woman 84.16667
    That’s The Kind Of M… 84.21429
    Meanwhile Back At Th… 84.25000
    Put Your Hand In Min… 84.37500
    No Leaf Clover (Live… 84.40000
    Daddy Won’t Sell The… 84.50000
    Summer Rain 85.27273
    Give Me You 85.33333
    Mr. Too Damn Good 85.37500
    Baby Don’t Cry (Keep… 85.42857
    It’s So Hard 85.53333
    When You Need My Lov… 85.53333
    My Love Goes On And … 86.00000
    The Rockafeller Skan… 86.08333
    I Disappear 86.10000
    N 2 Gether Now 86.27273
    Tha Block Is Hot 86.36364
    The Best Man I Can B… 86.50000
    A Country Boy Can Su… 86.66667
    How Many Licks? 86.66667
    Sour Girl 86.76923
    Ryde or Die, Chick 86.85714
    Monica 87.55556
    Hey Papi 87.86667
    Stay Or Let It Go 88.00000
    Me Neither 88.50000
    You Won’t Be Lonely … 88.69231
    Whistle While You Tw… 88.85714
    Can’t Go For That 89.00000
    Left, Right, Left 89.38462
    Can’t Stay 89.75000
    A Little Gasoline 89.83333
    The Hardest Part Of … 90.00000
    Shut Up 90.09091
    Live, Laugh, Love 90.18182
    Got It All 90.20000
    What Means The World… 90.40000
    Take That 90.57143
    West Side Story 91.00000
    Straight Up 92.11111
    Don’t Call Me Baby 92.65000
    Re-Arranged 92.72222
    U Understand 93.16667
    Do You 93.57143
    If You Don’t Wanna L… 93.66667
    Still In My Heart 93.66667
    Baby U Are 93.80000
    3 Little Words 94.44444
    I’m Outta Love 94.66667
    Love Sets You Free 95.25000
    Riddle 95.25000
    The Quittin’ Kind 95.83333
    Pull Over 96.50000
    No Mercy 96.62500
    Dancing Queen 97.00000
    L.A. Song 97.16667
    All Good? 97.33333
    Cherchez LaGhost 98.00000
    Get Up 98.00000
    I Know 98.00000
    Souljas 98.00000
    Spanish Guitar 98.00000
    Ta Da 98.14286
    Imagine That 98.50000
    Freakin’ It 99.00000
    Got Beef 99.00000
    Kernkraft 400 99.00000
    Toca’s Miracle 99.00000