solutions session 2

Exercises 3

replace all whitespace characters in the country columns of country_df with underscores (_)

# I saved country_df data.frame from the main qmd file and import it here. We will learn how this works in session 3
country_df <- readRDS("data/country_df.rds")

library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.2.1     ✔ readr     2.2.0
✔ forcats   1.0.1     ✔ stringr   1.6.0
✔ ggplot2   4.0.3     ✔ tibble    3.3.1
✔ lubridate 1.9.5     ✔ tidyr     1.3.2
✔ purrr     1.2.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

country_df |>
  mutate(
    # with mutate, we can change one of multiple columns or create new ones
    country = str_replace_all(country, "\\s", "_")
  )

# A tibble: 46 × 4
   country                inhabitants capital          founded   
   <chr>                        <int> <chr>            <date>    
 1 Albania                    8000000 Tirana           1912-11-28
 2 Andorra                      77000 Andorra la Vella 1278-09-08
 3 Austria                    9000000 Vienna           1918-11-12
 4 Belarus                    5000000 Minsk            1991-08-25
 5 Belgium                    5000000 Brussels         1830-10-04
 6 Bosnia_and_Herzegovina     3000000 Sarajevo         1992-03-01
 7 Bulgaria                   9000000 Sofia            1908-09-22
 8 Croatia                    4000000 Zagreb           1991-06-25
 9 Cyprus                     2000000 Nicosia          1960-08-16
10 Czech_Republic             7000000 Prague           1993-01-01
# ℹ 36 more rows

remove all punctuation from the data_input vector

data_input <- "
Albania is a country with 2.8 million inhabitants, its capital is Tirana, and it was founded on 28 November 1912.
Andorra is a country with 77,000 inhabitants, its capital is Andorra la Vella, and it was founded on 8 September 1278.
Austria is a country with 8.9 million inhabitants, its capital is Vienna, and it was founded on 12 November 1918.
Belarus is a country with 9.5 million inhabitants, its capital is Minsk, and it was founded on 25 August 1991.
Belgium is a country with 11.5 million inhabitants, its capital is Brussels, and it was founded on 4 October 1830.
Bosnia and Herzegovina is a country with 3.3 million inhabitants, its capital is Sarajevo, and it was founded on 1 March 1992.
Bulgaria is a country with 6.9 million inhabitants, its capital is Sofia, and it was founded on 22 September 1908.
Croatia is a country with 4 million inhabitants, its capital is Zagreb, and it was founded on 25 June 1991.
Cyprus is a country with 1.2 million inhabitants, its capital is Nicosia, and it was founded on 16 August 1960.
Czech Republic is a country with 10.7 million inhabitants, its capital is Prague, and it was founded on 1 January 1993.
Denmark is a country with 5.8 million inhabitants, its capital is Copenhagen, and it was founded on 5 June 1849.
Estonia is a country with 1.3 million inhabitants, its capital is Tallinn, and it was founded on 20 August 1991.
Finland is a country with 5.5 million inhabitants, its capital is Helsinki, and it was founded on 6 December 1917.
France is a country with 67 million inhabitants, its capital is Paris, and it was founded on 22 September 1792.
Germany is a country with 83 million inhabitants, its capital is Berlin, and it was founded on 3 October 1990.
Greece is a country with 10.4 million inhabitants, its capital is Athens, and it was founded on 25 March 1821.
Hungary is a country with 9.6 million inhabitants, its capital is Budapest, and it was founded on 23 October 1989.
Iceland is a country with 366,000 inhabitants, its capital is Reykjavik, and it was founded on 17 June 1944.
Ireland is a country with 5 million inhabitants, its capital is Dublin, and it was founded on 6 December 1922.
Italy is a country with 60 million inhabitants, its capital is Rome, and it was founded on 17 March 1861.
Kosovo is a country with 1.8 million inhabitants, its capital is Pristina, and it was founded on 17 February 2008.
Latvia is a country with 1.9 million inhabitants, its capital is Riga, and it was founded on 18 November 1918.
Liechtenstein is a country with 39,000 inhabitants, its capital is Vaduz, and it was founded on 23 January 1719.
Lithuania is a country with 2.8 million inhabitants, its capital is Vilnius, and it was founded on 11 March 1990.
Luxembourg is a country with 634,000 inhabitants, its capital is Luxembourg City, and it was founded on 9 June 1815.
Malta is a country with 514,000 inhabitants, its capital is Valletta, and it was founded on 21 September 1964.
Moldova is a country with 2.6 million inhabitants, its capital is Chișinău, and it was founded on 27 August 1991.
Monaco is a country with 39,000 inhabitants, its capital is Monaco, and it was founded on 8 January 1297.
Montenegro is a country with 622,000 inhabitants, its capital is Podgorica, and it was founded on 3 June 2006.
Netherlands is a country with 17.4 million inhabitants, its capital is Amsterdam, and it was founded on 26 July 1581.
North Macedonia is a country with 2.1 million inhabitants, its capital is Skopje, and it was founded on 8 September 1991.
Norway is a country with 5.4 million inhabitants, its capital is Oslo, and it was founded on 7 June 1905.
Poland is a country with 38 million inhabitants, its capital is Warsaw, and it was founded on 11 November 1918.
Portugal is a country with 10.3 million inhabitants, its capital is Lisbon, and it was founded on 5 October 1143.
Romania is a country with 19 million inhabitants, its capital is Bucharest, and it was founded on 1 December 1918.
Russia is a country with 144 million inhabitants, its capital is Moscow, and it was founded on 12 June 1990.
San Marino is a country with 34,000 inhabitants, its capital is San Marino, and it was founded on 3 September 301.
Serbia is a country with 6.7 million inhabitants, its capital is Belgrade, and it was founded on 5 June 2006.
Slovakia is a country with 5.4 million inhabitants, its capital is Bratislava, and it was founded on 1 January 1993.
Slovenia is a country with 2.1 million inhabitants, its capital is Ljubljana, and it was founded on 25 June 1991.
Spain is a country with 47 million inhabitants, its capital is Madrid, and it was founded on 6 December 1978.
Sweden is a country with 10.4 million inhabitants, its capital is Stockholm, and it was founded on 6 June 1523.
Switzerland is a country with 8.3 million inhabitants, its capital is Bern, and it was founded on 12 September 1848.
Ukraine is a country with 41 million inhabitants, its capital is Kyiv, and it was founded on 24 August 1991.
United Kingdom is a country with 67 million inhabitants, its capital is London, and it was founded on 1 January 1801.
Vatican City is a country with 825 inhabitants, its capital is Vatican City, and it was founded on 11 February 1929.
"

library(stringr)
str_remove_all(data_input, "[,.!@?]")

[1] "\nAlbania is a country with 28 million inhabitants its capital is Tirana and it was founded on 28 November 1912\nAndorra is a country with 77000 inhabitants its capital is Andorra la Vella and it was founded on 8 September 1278\nAustria is a country with 89 million inhabitants its capital is Vienna and it was founded on 12 November 1918\nBelarus is a country with 95 million inhabitants its capital is Minsk and it was founded on 25 August 1991\nBelgium is a country with 115 million inhabitants its capital is Brussels and it was founded on 4 October 1830\nBosnia and Herzegovina is a country with 33 million inhabitants its capital is Sarajevo and it was founded on 1 March 1992\nBulgaria is a country with 69 million inhabitants its capital is Sofia and it was founded on 22 September 1908\nCroatia is a country with 4 million inhabitants its capital is Zagreb and it was founded on 25 June 1991\nCyprus is a country with 12 million inhabitants its capital is Nicosia and it was founded on 16 August 1960\nCzech Republic is a country with 107 million inhabitants its capital is Prague and it was founded on 1 January 1993\nDenmark is a country with 58 million inhabitants its capital is Copenhagen and it was founded on 5 June 1849\nEstonia is a country with 13 million inhabitants its capital is Tallinn and it was founded on 20 August 1991\nFinland is a country with 55 million inhabitants its capital is Helsinki and it was founded on 6 December 1917\nFrance is a country with 67 million inhabitants its capital is Paris and it was founded on 22 September 1792\nGermany is a country with 83 million inhabitants its capital is Berlin and it was founded on 3 October 1990\nGreece is a country with 104 million inhabitants its capital is Athens and it was founded on 25 March 1821\nHungary is a country with 96 million inhabitants its capital is Budapest and it was founded on 23 October 1989\nIceland is a country with 366000 inhabitants its capital is Reykjavik and it was founded on 17 June 1944\nIreland is a country with 5 million inhabitants its capital is Dublin and it was founded on 6 December 1922\nItaly is a country with 60 million inhabitants its capital is Rome and it was founded on 17 March 1861\nKosovo is a country with 18 million inhabitants its capital is Pristina and it was founded on 17 February 2008\nLatvia is a country with 19 million inhabitants its capital is Riga and it was founded on 18 November 1918\nLiechtenstein is a country with 39000 inhabitants its capital is Vaduz and it was founded on 23 January 1719\nLithuania is a country with 28 million inhabitants its capital is Vilnius and it was founded on 11 March 1990\nLuxembourg is a country with 634000 inhabitants its capital is Luxembourg City and it was founded on 9 June 1815\nMalta is a country with 514000 inhabitants its capital is Valletta and it was founded on 21 September 1964\nMoldova is a country with 26 million inhabitants its capital is Chișinău and it was founded on 27 August 1991\nMonaco is a country with 39000 inhabitants its capital is Monaco and it was founded on 8 January 1297\nMontenegro is a country with 622000 inhabitants its capital is Podgorica and it was founded on 3 June 2006\nNetherlands is a country with 174 million inhabitants its capital is Amsterdam and it was founded on 26 July 1581\nNorth Macedonia is a country with 21 million inhabitants its capital is Skopje and it was founded on 8 September 1991\nNorway is a country with 54 million inhabitants its capital is Oslo and it was founded on 7 June 1905\nPoland is a country with 38 million inhabitants its capital is Warsaw and it was founded on 11 November 1918\nPortugal is a country with 103 million inhabitants its capital is Lisbon and it was founded on 5 October 1143\nRomania is a country with 19 million inhabitants its capital is Bucharest and it was founded on 1 December 1918\nRussia is a country with 144 million inhabitants its capital is Moscow and it was founded on 12 June 1990\nSan Marino is a country with 34000 inhabitants its capital is San Marino and it was founded on 3 September 301\nSerbia is a country with 67 million inhabitants its capital is Belgrade and it was founded on 5 June 2006\nSlovakia is a country with 54 million inhabitants its capital is Bratislava and it was founded on 1 January 1993\nSlovenia is a country with 21 million inhabitants its capital is Ljubljana and it was founded on 25 June 1991\nSpain is a country with 47 million inhabitants its capital is Madrid and it was founded on 6 December 1978\nSweden is a country with 104 million inhabitants its capital is Stockholm and it was founded on 6 June 1523\nSwitzerland is a country with 83 million inhabitants its capital is Bern and it was founded on 12 September 1848\nUkraine is a country with 41 million inhabitants its capital is Kyiv and it was founded on 24 August 1991\nUnited Kingdom is a country with 67 million inhabitants its capital is London and it was founded on 1 January 1801\nVatican City is a country with 825 inhabitants its capital is Vatican City and it was founded on 11 February 1929\n"

Extract the date from the string and turn it into a proper date vector:

string <- "Military defeats following the outbreak of the French Revolutionary Wars resulted in the insurrection of 10 August 1792. The monarchy was abolished and replaced by the French First Republic one month later."

dates <- str_extract_all(string, "\\d{1,2} [A-z]+ \\d{3,4}")
lubridate::dmy(dates)

[1] "1792-08-10"

Check which of the lines in data_input have the word “million” (hint: you need to split the string into a vector with str_split_1() first)

lines <- str_split_1(data_input, "\n")
str_detect(lines, "million")

 [1] FALSE  TRUE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE
[13]  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE  TRUE  TRUE FALSE
[25]  TRUE FALSE FALSE  TRUE FALSE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE
[37]  TRUE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE FALSE

str_which(lines, "million")

 [1]  2  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 20 21 22 23 25 28 31 32 33
[26] 34 35 36 37 39 40 41 42 43 44 45 46

Now save a subset of the lines into a new object

There are many ways you could solve this. I would suggest to build on the last question and take the subset of lines that have the word “million’ in them:

lines_million <- str_subset(lines, "million")

Alternatively, you could make a data.frame first, as I have done in class and then use filter together with str_detect.

From the following vector, I want you to write code that identifies the URLs from the German Wikipedia that do not use the secure Hypertext Transfer Protocol (HTTPS):

vec <- c(
  "https://en.wikipedia.org/wiki/France",
  "https://en.wikipedia.org/wiki/Germany",
  "https://de.wikipedia.org/wiki/Deutschland",
  "http://de.wikipedia.org/wiki/Frankreich",
  "https://de.wikipedia.org/wiki/Belgien",
  "https://de.wikipedia.org/wiki/Bulgarien",
  "http://de.wikipedia.org/wiki/K%C3%B6nigreich_D%C3%A4nemark",
  "https://de.wikipedia.org/wiki/Slowenien",
  "https://de.wikipedia.org/wiki/Rum%C3%A4nien"
)

German Wikipedia artilces start with “de.wikipedia” instead of “en.wikipedia”. The URLs that do not se HTTPS use “http” in the beginning. So all URLs that start with “http://de” fir the condition:

str_detect(vec, "^http://de")

[1] FALSE FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE FALSE

Exercises 4

From the airports table in the nycflights13 package, access all airports that are in the Los Angeles time zone:

nycflights13::airports

# A tibble: 1,458 × 8
   faa   name                             lat    lon   alt    tz dst   tzone    
   <chr> <chr>                          <dbl>  <dbl> <dbl> <dbl> <chr> <chr>    
 1 04G   Lansdowne Airport               41.1  -80.6  1044    -5 A     America/…
 2 06A   Moton Field Municipal Airport   32.5  -85.7   264    -6 A     America/…
 3 06C   Schaumburg Regional             42.0  -88.1   801    -6 A     America/…
 4 06N   Randall Airport                 41.4  -74.4   523    -5 A     America/…
 5 09J   Jekyll Island Airport           31.1  -81.4    11    -5 A     America/…
 6 0A9   Elizabethton Municipal Airport  36.4  -82.2  1593    -5 A     America/…
 7 0G6   Williams County Airport         41.5  -84.5   730    -5 A     America/…
 8 0G7   Finger Lakes Regional Airport   42.9  -76.8   492    -5 A     America/…
 9 0P2   Shoestring Aviation Airfield    39.8  -76.6  1000    -5 U     America/…
10 0S9   Jefferson County Intl           48.1 -123.    108    -8 A     America/…
# ℹ 1,448 more rows

I would approach this by first using View() on the table. Some people struggles with that, as the table was not in the environment and you can’t click on it in the Environment tab of RStudio. So let’s fix that first:

airports <- nycflights13::airports

Using the search function in the table viewer, we can look for “Los Angeles”. We can then see that there is a column called tzone that has the value “America/Los_Angeles”. This is the Los Angeles time zone. So we can use filter to access these

airports |>
  filter(tzone == "America/Los_Angeles")

# A tibble: 176 × 8
   faa   name                           lat   lon   alt    tz dst   tzone       
   <chr> <chr>                        <dbl> <dbl> <dbl> <dbl> <chr> <chr>       
 1 0S9   Jefferson County Intl         48.1 -123.   108    -8 A     America/Los…
 2 1RL   Point Roberts Airpark         49.0 -123.    10    -8 A     America/Los…
 3 38W   Lynden Airport                49.0 -122.   106    -8 A     America/Los…
 4 49X   Chemehuevi Valley             34.5 -114.   638    -8 A     America/Los…
 5 55S   Packwood                      46.4 -121.  1057    -8 A     America/Los…
 6 6S2   Florence                      44.0 -124.    51    -8 A     America/Los…
 7 ACV   Arcata                        41.0 -124.   221    -8 A     America/Los…
 8 ALW   Walla Walla Regional Airport  46.1 -118.  1191    -8 A     America/Los…
 9 APC   Napa County Airport           38.2 -122.    35    -8 A     America/Los…
10 AST   Astoria Regional Airport      46.2 -124.    15    -8 A     America/Los…
# ℹ 166 more rows

If you took question literally, you might want to have just the airport names:

airports |>
  filter(tzone == "America/Los_Angeles") |>
  pull(name)

  [1] "Jefferson County Intl"                      
  [2] "Point Roberts Airpark"                      
  [3] "Lynden Airport"                             
  [4] "Chemehuevi Valley"                          
  [5] "Packwood"                                   
  [6] "Florence"                                   
  [7] "Arcata"                                     
  [8] "Walla Walla Regional Airport"               
  [9] "Napa County Airport"                        
 [10] "Astoria Regional Airport"                   
 [11] "Avalon"                                     
 [12] "Beale Afb"                                  
 [13] "Boeing Fld King Co Intl"                    
 [14] "Meadows Fld"                                
 [15] "Blythe Airport"                             
 [16] "Bellingham Intl"                            
 [17] "Bob Hope"                                   
 [18] "Borrego Valley Airport"                     
 [19] "Bicycle Lake Aaf"                           
 [20] "Blakely Island Airport"                     
 [21] "Buchanan Field Airport"                     
 [22] "Del Norte County Airport"                   
 [23] "Chico Muni"                                 
 [24] "McClellan-Palomar Airport"                  
 [25] "William R Fairchild International Airport"  
 [26] "Chehalis-Centralia"                         
 [27] "Pappy Boyington"                            
 [28] "Corvallis Muni"                             
 [29] "Chatsworth Station"                         
 [30] "Calexico Intl"                              
 [31] "Deer Harbor Seaplane"                       
 [32] "Yolo County Airport"                        
 [33] "Ocean Ridge Airport"                        
 [34] "Pangborn Field"                             
 [35] "Edwards Afb"                                
 [36] "Elko Regional Airport"                      
 [37] "Ely Airport"                                
 [38] "Orcas Island Airport"                       
 [39] "Mahlon Sweet Fld"                           
 [40] "Fresno Yosemite Intl"                       
 [41] "Friday Harbor Seaplane Base"                
 [42] "Friday Harbor Airport"                      
 [43] "Fullerton Municipal Airport"                
 [44] "Spokane Intl"                               
 [45] "Gray Aaf"                                   
 [46] "Jack Northrop Fld Hawthorne Muni"           
 [47] "Portland Hillsboro"                         
 [48] "Bowerman Field"                             
 [49] "Henderson Executive Airport"                
 [50] "Hayward Executive Airport"                  
 [51] "Creech Afb"                                 
 [52] "Imperial Co"                                
 [53] "Inyokern Airport"                           
 [54] "Kenmore Air Harbor Inc Seaplane Base"       
 [55] "Kelso Longview"                             
 [56] "Furnace Creek"                              
 [57] "Big Bear City"                              
 [58] "Oceano County Airport"                      
 [59] "Mc Carran Intl"                             
 [60] "Los Angeles Intl"                           
 [61] "Long Beach"                                 
 [62] "Lincoln Regional Airport Karl Harder Field" 
 [63] "Kenmore Air Harbor Seaplane Base"           
 [64] "Klamath Falls Airport"                      
 [65] "Lompoc Airport"                             
 [66] "Lopez Island Airport"                       
 [67] "Nellis Afb"                                 
 [68] "Livermore Municipal"                        
 [69] "Lewiston Nez Perce Co"                      
 [70] "Desert Aire"                                
 [71] "Madera Municipal Airport"                   
 [72] "Mc Clellan Afld"                            
 [73] "Merced Municipal Airport"                   
 [74] "Castle"                                     
 [75] "Rogue Valley Intl Medford"                  
 [76] "Sacramento Mather"                          
 [77] "Mojave"                                     
 [78] "Mammoth Yosemite Airport"                   
 [79] "Mc Minnville Muni"                          
 [80] "Modesto City Co Harry Sham"                 
 [81] "MariposaYosemite"                           
 [82] "Monterey Peninsula"                         
 [83] "Grant Co Intl"                              
 [84] "Yuba County Airport"                        
 [85] "Fallon Nas"                                 
 [86] "NAS Alameda"                                
 [87] "China Lake Naws"                            
 [88] "El Centro Naf"                              
 [89] "Miramar Mcas"                               
 [90] "Lemoore Nas"                                
 [91] "Port Angeles Cgas"                          
 [92] "Point Mugu Nas"                             
 [93] "Moffett Federal Afld"                       
 [94] "Whidbey Island Nas"                         
 [95] "Twentynine Palms Eaf"                       
 [96] "El Toro"                                    
 [97] "North Island Nas"                           
 [98] "Oakdale Airport"                            
 [99] "Metropolitan Oakland Intl"                  
[100] "Marina Muni"                                
[101] "Olympia Regional Airpor"                    
[102] "San Diego Old Town Transit Center"          
[103] "Newport Municipal Airport"                  
[104] "Ontario Intl"                               
[105] "Southwest Oregon Regional Airport"          
[106] "Anacortes Airport"                          
[107] "Oxnard - Ventura County"                    
[108] "Snohomish Co"                               
[109] "Palo Alto Airport of Santa Clara County"    
[110] "Eastern Oregon Regional Airport"            
[111] "Portland Intl"                              
[112] "Palmdale Rgnl Usaf Plt 42"                  
[113] "Brackett Field"                             
[114] "Tri Cities Airport"                         
[115] "Palm Springs Intl"                          
[116] "Pullman-Moscow Rgnl"                        
[117] "Bremerton National"                         
[118] "Ferry County Airport"                       
[119] "Riverside Muni"                             
[120] "French Valley Airport"                      
[121] "Roche Harbor Seaplane Base"                 
[122] "Redding Muni"                               
[123] "Roberts Fld"                                
[124] "Redlands Municipal Airport"                 
[125] "Flabob Airport"                             
[126] "Rancho Murieta"                             
[127] "March Arb"                                  
[128] "Ramona Airport"                             
[129] "Reno Tahoe Intl"                            
[130] "Renton"                                     
[131] "Rosario Seaplane Base"                      
[132] "Lebanon State"                              
[133] "Prosser"                                    
[134] "Sacramento Executive"                       
[135] "San Diego Intl"                             
[136] "Santa Barbara Muni"                         
[137] "San Bernardino International Airport"       
[138] "San Luis County Regional Airport"           
[139] "Stockton Metropolitan"                      
[140] "Brown Field Municipal Airport"              
[141] "Seattle Tacoma Intl"                        
[142] "Gillespie"                                  
[143] "Felts Fld"                                  
[144] "San Francisco Intl"                         
[145] "Norman Y Mineta San Jose Intl"              
[146] "Fairchild Afb"                              
[147] "McNary Field"                               
[148] "Sacramento Intl"                            
[149] "Santa Monica Municipal Airport"             
[150] "Santa Maria Pub Cpt G Allan Hancock Airport"
[151] "John Wayne Arpt Orange Co"                  
[152] "Scappoose Industrial Airpark"               
[153] "Silver Springs Airport"                     
[154] "San Carlos Airport"                         
[155] "Charles M Schulz Sonoma Co"                 
[156] "Travis Afb"                                 
[157] "Mc Chord Afb"                               
[158] "Tacoma Narrows Airport"                     
[159] "Truckee-Tahoe Airport"                      
[160] "Tonopah Test Range"                         
[161] "Zamperini Field Airport"                    
[162] "Jacqueline Cochran Regional Airport"        
[163] "Portland Troutdale"                         
[164] "Lake Tahoe Airport"                         
[165] "Jefferson County Intl"                      
[166] "Bermuda Dunes Airport"                      
[167] "Vandenberg Afb"                             
[168] "Southern California Logistics"              
[169] "North Las Vegas Airport"                    
[170] "Visalia Municipal Airport"                  
[171] "Van Nuys"                                   
[172] "Ocean Shores Municipal"                     
[173] "Whiteman Airport"                           
[174] "Wishram Amtrak Station"                     
[175] "Westsound Seaplane Base"                    
[176] "Yakima Air Terminal McAllister Field"

Access the faa of the airport at latitude 40.87522 and longitude -74.28136

I had assume this would work:

airports |>
  filter(lat == 40.87522, lon == -74.28136) |>
  pull(faa)

character(0)

Unfortunately, I hadn’t checked the results. It is a little tricky to make exact comparisons between numeric values. So the only direct solution would have been something like this:

airports |>
  filter(lat > 40.87521, lat < 40.87523, lon > -74.28137, lon < -74.28135) |>
  pull(faa)

[1] "CDW"

A good workaround that some of you cam up with is to use View(airports) again and then use the search function to look for the numbers. Then you know which aiport is meant:

airports |>
  filter(name == "Caldwell Essex County Airport") |>
  pull(faa)

[1] "CDW"

The Las Vegas International Aiport (LAS) has been renamed since the dataset was created. Update the name to “Harry Reid International”

You can basically just copy the code from the slides:

France <- tibble(
  country = "France",
  inhabitants = 67000000L,
  capital = "Paris",
  founded = as.Date("843-08-10")
)
country_df |>
  rows_update(France, by = "country") |>
  filter(founded < "1300-01-01")

# A tibble: 5 × 4
  country    inhabitants capital          founded   
  <chr>            <int> <chr>            <date>    
1 Andorra          77000 Andorra la Vella 1278-09-08
2 France        67000000 Paris            843-08-10 
3 Monaco           39000 Monaco           1297-01-08
4 Portugal       3000000 Lisbon           1143-10-05
5 San Marino       34000 San Marino       301-09-03

And change it to fit the new task

las <- tibble(
  faa = "LAS",
  name = "Harry Reid International",
  lat = 36.080056,
  lon = -115.15225,
  alt = 2141,
  tz = -8,
  dst = "A",
  tzone = "America/Los_Angeles"
)
airports |>
  rows_update(las, by = "faa")

# A tibble: 1,458 × 8
   faa   name                             lat    lon   alt    tz dst   tzone    
   <chr> <chr>                          <dbl>  <dbl> <dbl> <dbl> <chr> <chr>    
 1 04G   Lansdowne Airport               41.1  -80.6  1044    -5 A     America/…
 2 06A   Moton Field Municipal Airport   32.5  -85.7   264    -6 A     America/…
 3 06C   Schaumburg Regional             42.0  -88.1   801    -6 A     America/…
 4 06N   Randall Airport                 41.4  -74.4   523    -5 A     America/…
 5 09J   Jekyll Island Airport           31.1  -81.4    11    -5 A     America/…
 6 0A9   Elizabethton Municipal Airport  36.4  -82.2  1593    -5 A     America/…
 7 0G6   Williams County Airport         41.5  -84.5   730    -5 A     America/…
 8 0G7   Finger Lakes Regional Airport   42.9  -76.8   492    -5 A     America/…
 9 0P2   Shoestring Aviation Airfield    39.8  -76.6  1000    -5 U     America/…
10 0S9   Jefferson County Intl           48.1 -123.    108    -8 A     America/…
# ℹ 1,448 more rows

Delete all aiports that have NA as their timezone

To delete rows, I would always suggest that you create a new objects, which does not contain the rows you do not want:

airports_clean <- airports |>
  filter(!is.na(tzone))

Side note: it would be logical to do something likes this:

airports |>
  filter(!tzone == NA)

# A tibble: 0 × 8
# ℹ 8 variables: faa <chr>, name <chr>, lat <dbl>, lon <dbl>, alt <dbl>,
#   tz <dbl>, dst <chr>, tzone <chr>

But that will not work in R because all comparisons to NA result in NA. You can test this with:

1 == NA

[1] NA

Exercises 5

Look at the table billboard that is included in tidyr. What is not tidy about it?

library(tidyr)
billboard

# A tibble: 317 × 79
   artist     track date.entered   wk1   wk2   wk3   wk4   wk5   wk6   wk7   wk8
   <chr>      <chr> <date>       <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
 1 2 Pac      Baby… 2000-02-26      87    82    72    77    87    94    99    NA
 2 2Ge+her    The … 2000-09-02      91    87    92    NA    NA    NA    NA    NA
 3 3 Doors D… Kryp… 2000-04-08      81    70    68    67    66    57    54    53
 4 3 Doors D… Loser 2000-10-21      76    76    72    69    67    65    55    59
 5 504 Boyz   Wobb… 2000-04-15      57    34    25    17    17    31    36    49
 6 98^0       Give… 2000-08-19      51    39    34    26    26    19     2     2
 7 A*Teens    Danc… 2000-07-08      97    97    96    95   100    NA    NA    NA
 8 Aaliyah    I Do… 2000-01-29      84    62    51    41    38    35    35    38
 9 Aaliyah    Try … 2000-03-18      59    53    38    28    21    18    16    14
10 Adams, Yo… Open… 2000-08-26      76    76    74    69    68    67    61    58
# ℹ 307 more rows
# ℹ 68 more variables: wk9 <dbl>, wk10 <dbl>, wk11 <dbl>, wk12 <dbl>,
#   wk13 <dbl>, wk14 <dbl>, wk15 <dbl>, wk16 <dbl>, wk17 <dbl>, wk18 <dbl>,
#   wk19 <dbl>, wk20 <dbl>, wk21 <dbl>, wk22 <dbl>, wk23 <dbl>, wk24 <dbl>,
#   wk25 <dbl>, wk26 <dbl>, wk27 <dbl>, wk28 <dbl>, wk29 <dbl>, wk30 <dbl>,
#   wk31 <dbl>, wk32 <dbl>, wk33 <dbl>, wk34 <dbl>, wk35 <dbl>, wk36 <dbl>,
#   wk37 <dbl>, wk38 <dbl>, wk39 <dbl>, wk40 <dbl>, wk41 <dbl>, wk42 <dbl>, …

this is wide or messy or bad table (whichever name you prefer) because the observations (song rankings by week 1, 2, 3, and so on) are stored in columns rather than rows
if you want to learn more about this table, you can use ?billboard or look at it using View(billboard)

Tidy the data

since the format is wide and we want a long (or tidy) table, we need pivot_longer

billboard |>
  pivot_longer(
    cols = starts_with("wk"), # which columns should be made longer
    names_to = "week", # in which column should the former column names be stored
    values_to = "rank" # in which column should the values be stored
  )

# A tibble: 24,092 × 5
   artist track                   date.entered week   rank
   <chr>  <chr>                   <date>       <chr> <dbl>
 1 2 Pac  Baby Don't Cry (Keep... 2000-02-26   wk1      87
 2 2 Pac  Baby Don't Cry (Keep... 2000-02-26   wk2      82
 3 2 Pac  Baby Don't Cry (Keep... 2000-02-26   wk3      72
 4 2 Pac  Baby Don't Cry (Keep... 2000-02-26   wk4      77
 5 2 Pac  Baby Don't Cry (Keep... 2000-02-26   wk5      87
 6 2 Pac  Baby Don't Cry (Keep... 2000-02-26   wk6      94
 7 2 Pac  Baby Don't Cry (Keep... 2000-02-26   wk7      99
 8 2 Pac  Baby Don't Cry (Keep... 2000-02-26   wk8      NA
 9 2 Pac  Baby Don't Cry (Keep... 2000-02-26   wk9      NA
10 2 Pac  Baby Don't Cry (Keep... 2000-02-26   wk10     NA
# ℹ 24,082 more rows

The new table contains many missing values (NAs) explain why and if you should delete them

Looking at the song “Dancing Queen”, we see that it was on the very last place of the top 100 for 5 weeks. Afterwards it apparently dropped out of the top 100. Since other songs remained there much longer, whoever created the table needed many extra columns that are empty for this song. Since we pivoted all values into the long format, the missing values remained
I think the NAs should be removed as they add no values. If we wanted to count how long a song was in the top 100, we could still do so. The information that it was not in the top 100 in week 40 or 47 is redundant

Independently from whether it is the right thing to do or now: delete the NAs

billboard |>
  pivot_longer(
    cols = starts_with("wk"), # which columns should be made longer
    names_to = "week", # in which column should the former column names be stored
    values_to = "rank" # in which column should the values be stored
  ) |>
  filter(!is.na(rank)) # we remove rows where the function is.na() returns TRUE for the rank column

# A tibble: 5,307 × 5
   artist  track                   date.entered week   rank
   <chr>   <chr>                   <date>       <chr> <dbl>
 1 2 Pac   Baby Don't Cry (Keep... 2000-02-26   wk1      87
 2 2 Pac   Baby Don't Cry (Keep... 2000-02-26   wk2      82
 3 2 Pac   Baby Don't Cry (Keep... 2000-02-26   wk3      72
 4 2 Pac   Baby Don't Cry (Keep... 2000-02-26   wk4      77
 5 2 Pac   Baby Don't Cry (Keep... 2000-02-26   wk5      87
 6 2 Pac   Baby Don't Cry (Keep... 2000-02-26   wk6      94
 7 2 Pac   Baby Don't Cry (Keep... 2000-02-26   wk7      99
 8 2Ge+her The Hardest Part Of ... 2000-09-02   wk1      91
 9 2Ge+her The Hardest Part Of ... 2000-09-02   wk2      87
10 2Ge+her The Hardest Part Of ... 2000-09-02   wk3      92
# ℹ 5,297 more rows

Why does this code fail?

table4a |>
  pivot_longer(c(1999, 2000), names_to = "year", values_to = "cases")

Error in `pivot_longer()`:
! Can't select columns past the end.
ℹ Locations 1999 and 2000 don't exist.
ℹ There are only 3 columns.

This might become a little clearer when adding the missing argument name. c(1999, 2000) indicates which columns should be turned longer. But column names can’t be numeric or integer in R. If they consist of numbers, they must be turned into character:

table4a |>
  pivot_longer(cols = c("1999", "2000"), names_to = "year", values_to = "cases")

# A tibble: 6 × 3
  country     year   cases
  <chr>       <chr>  <dbl>
1 Afghanistan 1999     745
2 Afghanistan 2000    2666
3 Brazil      1999   37737
4 Brazil      2000   80488
5 China       1999  212258
6 China       2000  213766

However, the cols argument can deal with numbers. Then they indicate which column, starting to count from the first column on the left, should be treated. What the code above did was to say turn columns 1999 and 2000 places from the left into the long format. Since the table only has 3 columns, the code failed.

Make this into a tidy table

preg <- tribble(
  ~pregnant , ~male , ~female ,
  "yes"     , NA    ,      10 ,
  "no"      ,    20 ,      12
)

Maybe te hardest thing here is to think what would be cases here and what are variables, since there is no ID. We need to consider what the table shows. We have counts of females and males who are pregnant. So pregnant, gender and how often they appear together are variables, I would say:

preg |>
  pivot_longer(cols = male:female, names_to = "gender", values_to = "n")

# A tibble: 4 × 3
  pregnant gender     n
  <chr>    <chr>  <dbl>
1 yes      male      NA
2 yes      female    10
3 no       male      20
4 no       female    12

Exercises 6

The object below is a nested list with Game of Thrones characters. Tidy the data into a data.frame that contains at least name, gender, culture and birthday of each character

got_characters <- repurrrsive::got_chars

My first approach would be to just test if bind_rows knows how to solve this automagically:

got_characters |> 
  bind_rows()

Error in `vctrs::data_frame()`:
! Can't recycle `titles` (size 2) to match `aliases` (size 4).

unfortunatly, the list elements are not easily coerced into data.frames, so we need to write a special function and map over the list elements
I usually start with the first element of the list and develop code to get this into a data.frame

# select first element
got_characters_1 <- got_characters |> pluck(1)
# transform information into a tibble/data.frame
tibble(
  name = got_characters_1$name,
  gender = got_characters_1$gender,
  culture = got_characters_1$culture,
  born = got_characters_1$born
  # you can extend the list, but this is the minimum the exercises asked for
)

# A tibble: 1 × 4
  name          gender culture  born                        
  <chr>         <chr>  <chr>    <chr>                       
1 Theon Greyjoy Male   Ironborn In 278 AC or 279 AC, at Pyke

Now we can wrap this in a function, map over all list elements with it, and combine into one data.frame

got_characters |> 
  map(function(element) {
    tibble(
      name = element$name,
      gender = element$gender,
      culture = element$culture,
      born = element$born
    )
  }) |> 
  bind_rows()

# A tibble: 30 × 4
   name               gender culture    born                                    
   <chr>              <chr>  <chr>      <chr>                                   
 1 Theon Greyjoy      Male   "Ironborn" "In 278 AC or 279 AC, at Pyke"          
 2 Tyrion Lannister   Male   ""         "In 273 AC, at Casterly Rock"           
 3 Victarion Greyjoy  Male   "Ironborn" "In 268 AC or before, at Pyke"          
 4 Will               Male   ""         ""                                      
 5 Areo Hotah         Male   "Norvoshi" "In 257 AC or before, at Norvos"        
 6 Chett              Male   ""         "At Hag's Mire"                         
 7 Cressen            Male   ""         "In 219 AC or 220 AC"                   
 8 Arianne Martell    Female "Dornish"  "In 276 AC, at Sunspear"                
 9 Daenerys Targaryen Female "Valyrian" "In 284 AC, at Dragonstone"             
10 Davos Seaworth     Male   "Westeros" "In 260 AC or before, at King's Landing"
# ℹ 20 more rows

From the got_characters object, also extract in which season(s) they appear, so that the data frame contains one row per season appearance (and still the same character information as above)

When you look at the seasons field, you noticed that this is a nested column

got_characters_1$tvSeries

[1] "Season 1" "Season 2" "Season 3" "Season 4" "Season 5" "Season 6"

Yet, as long as you only have one nested field, you can use a behaviour of R: automatic recycling of lenght 1 elements:

tibble(
  name = got_characters_1$name,
  gender = got_characters_1$gender,
  culture = got_characters_1$culture,
  born = got_characters_1$born,
  seasons = got_characters_1$tvSeries
)

# A tibble: 6 × 5
  name          gender culture  born                         seasons 
  <chr>         <chr>  <chr>    <chr>                        <chr>   
1 Theon Greyjoy Male   Ironborn In 278 AC or 279 AC, at Pyke Season 1
2 Theon Greyjoy Male   Ironborn In 278 AC or 279 AC, at Pyke Season 2
3 Theon Greyjoy Male   Ironborn In 278 AC or 279 AC, at Pyke Season 3
4 Theon Greyjoy Male   Ironborn In 278 AC or 279 AC, at Pyke Season 4
5 Theon Greyjoy Male   Ironborn In 278 AC or 279 AC, at Pyke Season 5
6 Theon Greyjoy Male   Ironborn In 278 AC or 279 AC, at Pyke Season 6

This is similar to unnesting: the rows of length 1 are recycled (duplicated) until all columns have the same length

got_characters |> 
  map(function(element) {
    tibble(
      name = element$name,
      gender = element$gender,
      culture = element$culture,
      born = element$born,
      seasons = element$tvSeries
    )
  }) |> 
  bind_rows()

# A tibble: 102 × 5
   name             gender culture    born                         seasons 
   <chr>            <chr>  <chr>      <chr>                        <chr>   
 1 Theon Greyjoy    Male   "Ironborn" In 278 AC or 279 AC, at Pyke Season 1
 2 Theon Greyjoy    Male   "Ironborn" In 278 AC or 279 AC, at Pyke Season 2
 3 Theon Greyjoy    Male   "Ironborn" In 278 AC or 279 AC, at Pyke Season 3
 4 Theon Greyjoy    Male   "Ironborn" In 278 AC or 279 AC, at Pyke Season 4
 5 Theon Greyjoy    Male   "Ironborn" In 278 AC or 279 AC, at Pyke Season 5
 6 Theon Greyjoy    Male   "Ironborn" In 278 AC or 279 AC, at Pyke Season 6
 7 Tyrion Lannister Male   ""         In 273 AC, at Casterly Rock  Season 1
 8 Tyrion Lannister Male   ""         In 273 AC, at Casterly Rock  Season 2
 9 Tyrion Lannister Male   ""         In 273 AC, at Casterly Rock  Season 3
10 Tyrion Lannister Male   ""         In 273 AC, at Casterly Rock  Season 4
# ℹ 92 more rows

Bonus: it would be slightly differnt if you wanted to include another nested field too
the only way to create a tibble now is to store the columns as lists

tibble(
  name = got_characters_1$name,
  gender = got_characters_1$gender,
  culture = got_characters_1$culture,
  born = got_characters_1$born,
  seasons = list(got_characters_1$tvSeries),
  titles = list(got_characters_1$titles)
)

# A tibble: 1 × 6
  name          gender culture  born                         seasons   titles   
  <chr>         <chr>  <chr>    <chr>                        <list>    <list>   
1 Theon Greyjoy Male   Ironborn In 278 AC or 279 AC, at Pyke <chr [6]> <chr [2]>

to get the whole table, you would need to do this for all elements, then unnest the nested columns to get a tidy data.frame of the person-season-title observation

got_characters |> 
  map(function(element) {
    tibble(
      name = element$name,
      gender = element$gender,
      culture = element$culture,
      born = element$born,
      seasons = list(element$tvSeries),
      titles = list(element$titles)
    )
  }) |> 
  bind_rows() |> 
  unnest(seasons) |> 
  unnest(titles)

# A tibble: 221 × 6
   name          gender culture  born                         seasons  titles   
   <chr>         <chr>  <chr>    <chr>                        <chr>    <chr>    
 1 Theon Greyjoy Male   Ironborn In 278 AC or 279 AC, at Pyke Season 1 Prince o…
 2 Theon Greyjoy Male   Ironborn In 278 AC or 279 AC, at Pyke Season 1 Lord of …
 3 Theon Greyjoy Male   Ironborn In 278 AC or 279 AC, at Pyke Season 2 Prince o…
 4 Theon Greyjoy Male   Ironborn In 278 AC or 279 AC, at Pyke Season 2 Lord of …
 5 Theon Greyjoy Male   Ironborn In 278 AC or 279 AC, at Pyke Season 3 Prince o…
 6 Theon Greyjoy Male   Ironborn In 278 AC or 279 AC, at Pyke Season 3 Lord of …
 7 Theon Greyjoy Male   Ironborn In 278 AC or 279 AC, at Pyke Season 4 Prince o…
 8 Theon Greyjoy Male   Ironborn In 278 AC or 279 AC, at Pyke Season 4 Lord of …
 9 Theon Greyjoy Male   Ironborn In 278 AC or 279 AC, at Pyke Season 5 Prince o…
10 Theon Greyjoy Male   Ironborn In 278 AC or 279 AC, at Pyke Season 5 Lord of …
# ℹ 211 more rows

Sometimes, it makes sense to keep nested information around for later. The got_characters object contains aliases for the characters. Store them along the character name in a nested format, so that each row looks like this:

tibble(
  name = "Theon Greyjoy",
  aliases = list(c(
    "Prince of Fools",
    "Theon Turncloak",
    "Reek",
    "Theon Kinslaye"
  ))
)

# A tibble: 1 × 2
  name          aliases  
  <chr>         <list>   
1 Theon Greyjoy <chr [4]>

we did something similar in the bonus above. The code is essentially the same, but I’m not unnesting this time

got_characters |> 
  map(function(element) {
    tibble(
      name = element$name,
      gender = element$gender,
      culture = element$culture,
      born = element$born,
      seasons = list(element$tvSeries),
      aliases = list(element$aliases)
    )
  }) |> 
  bind_rows()

# A tibble: 30 × 6
   name               gender culture    born                     seasons aliases
   <chr>              <chr>  <chr>      <chr>                    <list>  <list> 
 1 Theon Greyjoy      Male   "Ironborn" "In 278 AC or 279 AC, a… <chr>   <chr>  
 2 Tyrion Lannister   Male   ""         "In 273 AC, at Casterly… <chr>   <chr>  
 3 Victarion Greyjoy  Male   "Ironborn" "In 268 AC or before, a… <chr>   <chr>  
 4 Will               Male   ""         ""                       <chr>   <chr>  
 5 Areo Hotah         Male   "Norvoshi" "In 257 AC or before, a… <chr>   <chr>  
 6 Chett              Male   ""         "At Hag's Mire"          <chr>   <chr>  
 7 Cressen            Male   ""         "In 219 AC or 220 AC"    <chr>   <chr>  
 8 Arianne Martell    Female "Dornish"  "In 276 AC, at Sunspear" <chr>   <chr>  
 9 Daenerys Targaryen Female "Valyrian" "In 284 AC, at Dragonst… <chr>   <chr>  
10 Davos Seaworth     Male   "Westeros" "In 260 AC or before, a… <chr>   <chr>  
# ℹ 20 more rows

The object below contains someone’s music collection. Create a tidy table of the artist this person listens to:

music_collection <- repurrrsive::discog

I tried bind_rows, which does not error, but somehow produces a data.frame with 1,705 rows from just 155 list elements!

music_collection |> 
  bind_rows()

# A tibble: 1,705 × 5
   instance_id date_added                basic_information      id rating
         <int> <chr>                     <named list>        <int>  <int>
 1   354823933 2019-02-16T17:48:59-08:00 <list [1]>        7496378      0
 2   354823933 2019-02-16T17:48:59-08:00 <int [1]>         7496378      0
 3   354823933 2019-02-16T17:48:59-08:00 <NULL>            7496378      0
 4   354823933 2019-02-16T17:48:59-08:00 <list [1]>        7496378      0
 5   354823933 2019-02-16T17:48:59-08:00 <int [1]>         7496378      0
 6   354823933 2019-02-16T17:48:59-08:00 <chr [1]>         7496378      0
 7   354823933 2019-02-16T17:48:59-08:00 <chr [1]>         7496378      0
 8   354823933 2019-02-16T17:48:59-08:00 <list [1]>        7496378      0
 9   354823933 2019-02-16T17:48:59-08:00 <chr [1]>         7496378      0
10   354823933 2019-02-16T17:48:59-08:00 <chr [1]>         7496378      0
# ℹ 1,695 more rows

so we take the same approach as before

music_collection |> 
  map(function(element) {
    tibble(
      instance_id = element$instance_id,
      date_added = element$date_added,
      id = element$id,
      rating = element$rating,
      basic_information = list(element$basic_information)
    )
  }) |> 
  bind_rows()

# A tibble: 155 × 5
   instance_id date_added                      id rating basic_information
         <int> <chr>                        <int>  <int> <list>           
 1   354823933 2019-02-16T17:48:59-08:00  7496378      0 <named list [11]>
 2   354092601 2019-02-13T14:13:11-08:00  4490852      0 <named list [11]>
 3   354091476 2019-02-13T14:07:23-08:00  9827276      0 <named list [11]>
 4   351244906 2019-02-02T11:39:58-08:00  9769203      0 <named list [11]>
 5   351244801 2019-02-02T11:39:37-08:00  7237138      0 <named list [11]>
 6   351052065 2019-02-01T20:40:53-08:00 13117042      0 <named list [11]>
 7   350315345 2019-01-29T15:48:37-08:00  7113575      0 <named list [11]>
 8   350315103 2019-01-29T15:47:22-08:00 10540713      0 <named list [11]>
 9   350314507 2019-01-29T15:44:08-08:00 11260950      0 <named list [11]>
10   350314047 2019-01-29T15:41:35-08:00 11726853      0 <named list [11]>
# ℹ 145 more rows

Now we still need some information from the basic_information column (artist name)
We can again use the first instance to look for the name

basic_information_1 <- music_collection[[1]]$basic_information
basic_information_1 |> 
  lobstr::tree()

<list>
├─labels: <list>
│ └─<list>
│   ├─name: "Tobi Records (2)"
│   ├─entity_type: "1"
│   ├─catno: "TOB-013"
│   ├─resource_url: "https://api.discogs.com/labels/6..."
│   ├─id: 633407
│   └─entity_type_name: "Label"
├─year: 2015
├─master_url: <NULL>
├─artists: <list>
│ └─<list>
│   ├─join: ""
│   ├─name: "Mollot"
│   ├─anv: ""
│   ├─tracks: ""
│   ├─role: ""
│   ├─resource_url: "https://api.discogs.com/artists/..."
│   └─id: 4619796
├─id: 7496378
├─thumb: "https://img.discogs.com/vEVegHrM..."
├─title: "Demo"
├─formats: <list>
│ └─<list>
│   ├─descriptions: <list>
│   │ └─"Numbered"
│   ├─text: "Black"
│   ├─name: "Cassette"
│   └─qty: "1"
├─cover_image: "https://img.discogs.com/EmbMh7vs..."
├─resource_url: "https://api.discogs.com/releases..."
└─master_id: 0

So the artist’s name lives under basic_information_1$artists[[1]]$name, but the [[1]] indicates there might be more than 1 artist at a time, which we can get with

purrr::map_chr(basic_information_1$artists, function(x) pluck(x, "name"))

[1] "Mollot"

now we can do this for the entire list

music_collection |> 
  map(function(element) {
    tibble(
      instance_id = element$instance_id,
      date_added = element$date_added,
      id = element$id,
      rating = element$rating,
      basic_information = list(element$basic_information),
      # once again, the other values are recycled if there is more than 1 artist name
      artist_name = purrr::map_chr(element$basic_information$artists, function(x) pluck(x, "name"))
    )
  }) |> 
  bind_rows()

# A tibble: 167 × 6
   instance_id date_added                id rating basic_information artist_name
         <int> <chr>                  <int>  <int> <list>            <chr>      
 1   354823933 2019-02-16T17:48:59-… 7.50e6      0 <named list [11]> Mollot     
 2   354092601 2019-02-13T14:13:11-… 4.49e6      0 <named list [11]> Una Bèstia…
 3   354091476 2019-02-13T14:07:23-… 9.83e6      0 <named list [11]> S.H.I.T. (…
 4   351244906 2019-02-02T11:39:58-… 9.77e6      0 <named list [11]> Rata Negra 
 5   351244801 2019-02-02T11:39:37-… 7.24e6      0 <named list [11]> Ivy (18)   
 6   351052065 2019-02-01T20:40:53-… 1.31e7      0 <named list [11]> Tashme     
 7   350315345 2019-01-29T15:48:37-… 7.11e6      0 <named list [11]> Desgraciad…
 8   350315103 2019-01-29T15:47:22-… 1.05e7      0 <named list [11]> Phantom He…
 9   350314507 2019-01-29T15:44:08-… 1.13e7      0 <named list [11]> Sub Space …
10   350314047 2019-01-29T15:41:35-… 1.17e7      0 <named list [11]> Small Man …
# ℹ 157 more rows

Now add the title of the songs to the artists table

music_collection |> 
  map(function(element) {
    tibble(
      instance_id = element$instance_id,
      date_added = element$date_added,
      id = element$id,
      rating = element$rating,
      basic_information = list(element$basic_information),
      # once again, the other values are recycled if there is more than 1 artist name
      artist_name = purrr::map_chr(element$basic_information$artists, function(x) pluck(x, "name")),
      song_title = element$basic_information$title
    )
  }) |> 
  bind_rows()

# A tibble: 167 × 7
   instance_id date_added     id rating basic_information artist_name song_title
         <int> <chr>       <int>  <int> <list>            <chr>       <chr>     
 1   354823933 2019-02-1… 7.50e6      0 <named list [11]> Mollot      Demo      
 2   354092601 2019-02-1… 4.49e6      0 <named list [11]> Una Bèstia… Observant…
 3   354091476 2019-02-1… 9.83e6      0 <named list [11]> S.H.I.T. (… I         
 4   351244906 2019-02-0… 9.77e6      0 <named list [11]> Rata Negra  Oído Abso…
 5   351244801 2019-02-0… 7.24e6      0 <named list [11]> Ivy (18)    A Cat's C…
 6   351052065 2019-02-0… 1.31e7      0 <named list [11]> Tashme      Tashme    
 7   350315345 2019-01-2… 7.11e6      0 <named list [11]> Desgraciad… Demo      
 8   350315103 2019-01-2… 1.05e7      0 <named list [11]> Phantom He… Let The M…
 9   350314507 2019-01-2… 1.13e7      0 <named list [11]> Sub Space … Sub Space 
10   350314047 2019-01-2… 1.17e7      0 <named list [11]> Small Man … Demo      
# ℹ 157 more rows