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.
- Look the the
billboarddata set. You will need to loadtidyr()anddplyr()individually, because the data set is embedded in the packages. - Using a pivot function, make a new dataset called
billboard_longer, and store the weeks to a variable namedweekand the values torank. Drop the missing values. - 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 |