2 Acquiring data

2.1 Reading tabular data

You can probably figure out a way to read just about any tabular data into R. We’ll look here at a couple of very straightforward but useful ones, and one slightly more clever one - but if you encounter messier data, be persistent.

We’ve loaded the dplyr package above, and we’ll add the readxl, googlesheets4 packages here. They do exactly what you’d expect from the names. Since you’re already familiar with read.csv() and read.delim from Base R, we’ll skip those here. As it is wont to do, the tidyverse reinvents the wheel and adds a read_csv() function in the readr package. It it similar to the Base R function most of the time, but sometimes better at parsing files, and by default produces a tibble, which is a tidyverse version of a data frame. For a guide to data import, see this cheatsheet.

You’ll find an .xlsx version of a table modified from Drennan’s (2009) Table 6-1 here, and a google sheet version here. If you examine the spreadsheet versions, you’ll note that some information is stored on a second sheet; you’ll want that also, so we’ll import two objects (drennan6.1b and drennan6.1b_2). Try importing both .xlsx and google sheet just to see that it’s possible to do either, but then pick one - you only need one version each of drennan6.1b and drennan6.1b_2.

Note also that some information is encoded by highlighting cells. How might you alter the spreadsheet to make that information capturable [you needn’t do this; just think about it]?

To note:

  • .csv is the most easily and reliably readable tabular format, but will not save formulas, colored cells, multiple sheets, etc…but when you read an .xlsx file you can’t easily capture that info anyway…so it’s not good practice to store information that way.
  • For an .xlsx or a google sheet, you may to have specify which range of cells and/or which sheet you want.

For googlesheets4::read_sheet() to work, you may have to authenticate. If you don’t get prompted and you get authentication errors, try running gs4_auth() in the console.

library(readxl)
library(googlesheets4)

drennan6.1b <- read_xlsx("data/Drennan_2009_Table6-1_b.xlsx") #defaults to sheet 1
drennan6.1b_2 <- read_xlsx("data/Drennan_2009_Table6-1_b.xlsx", sheet = 2) 
 
#uncomment the below if you want the googlesheet version
# drennan6.1b <- read_sheet("https://docs.google.com/spreadsheets/d/1bnZ7QSc1hjQ1HYQwXFNUHY3Yf4DLiNw4B68gl3texJc/edit?usp=sharing")
# drennan6.1b_2 <- read_sheet("https://docs.google.com/spreadsheets/d/1bnZ7QSc1hjQ1HYQwXFNUHY3Yf4DLiNw4B68gl3texJc/edit?usp=sharing", sheet = 2)

Pick your favorite of the versions of this table and write it to an object. Because these data are clean, there’s no need to fuss with them much - but we may want to rearrange them a bit, and probably will find it convenient to convert some of the columns into factors for that reason. See Cleaning data.

It’s a bit beyond our scope here, but one of common side-effects of working with spreadsheet data is rage against the author of the spreadsheet (even - or maybe especially - if that author is your past self). There are better and worse ways to use spreadsheets; see for example Broman and Woo ((2018)).

2.2 Scraping .pdfs

Tabula

We’ll look at pages 589-592 from Eerkens and colleagues 2007 paper (Eerkens et al. 2007), which contains three tables of interest. Scrape all three - you can find a .pdf of the relevant pages here - using tabula (there is a tabulapdf package that provides an interface through R…but it generally doesn’t work as well). Save the three resulting .csv files in your ‘data’ folder. Examine the results, and you’ll find that the scraping isn’t perfect: characters may be misinterpreted (empty spaces and placeholders tend to cause trouble) and not everything winds up exactly where it should. This is generally easiest to fix by opening the .csv in MSExcel or something like it and editing there, comparing to the .pdf table to make sure you get accurate output.

Once you have the three tables looking as they should, read them in with read.csv() or read_csv(). If Tabula isn’t cooperating and/or you want to learn less, you can download .csv versions (Table 1, Table 2, and Table 3).

library(readr)

eerk_tab1 <- read_csv("data/Eerkens_2007_Table1.csv")
eerk_tab2 <- read_csv("data/Eerkens_2007_Table2.csv")
eerk_tab3 <- read_csv("data/Eerkens_2007_Table3.csv")

It’s always a good idea to have a look at your data before you do anything.

eerk_tab1
## # A tibble: 5 × 10
##   `Source Artifact`         `Casa Diablo` `Mono GlassMtn.` Queen `Fish Springs`
##   <chr>                             <dbl>            <dbl> <dbl>          <dbl>
## 1 FormalT ools                         51               16    13              5
## 2 Large Flakes                        166               65    10             18
## 3 Small Non-pressure Flakes            24               10     2              1
## 4 Small Pressure Flakes                 7                3     7              1
## 5 Totals                              248               94    32             25
## # ℹ 5 more variables: `Mono Craters` <chr>, `Bodie Hills` <chr>,
## #   `Mount Hicks` <chr>, Coso <chr>, Totals <dbl>

It’s immediately clear that we have some work to do. There are two different ways of indicating no data (“NA” and “-”), there are still a few typos, and it’s not obvious which site these data are from.

What we want to do is tidy these tables up, and combine them into a single table that includes a variable for site (the captions indicate that Table 1 is material from Sherwin Summit, Table 2 from Mohawk Valley, and Table 3 from Bone Cave). Those desires leads us to…