Example 7.13: Read a file with two lines per observation

In example 7.6 we showed how to retrieve the Amazon sales rank of a book. A cron job on one of our machines grabs the sales rank hourly. We’d like to use this data to satisfy our curiosity about when and how often a book sells.

A complication is that the result of the cron job is a file with the time of the sales rank retrieval on one line and the rank retrieved on the following line. Here are some example lines frome the file:

Sun Aug  9 14:38:13 EDT 2009
salesrank= 141913 
Sun Aug  9 14:40:04 EDT 2009
salesrank= 141913 
Sun Aug  9 15:00:05 EDT 2009
salesrank= 149556 
Sun Aug  9 16:00:05 EDT 2009
salesrank= 158132 
Sun Aug  9 17:00:05 EDT 2009
salesrank= 166742 

In the example below, we show how to read this data into SAS or R so that there is one observation (or row) per time. In a later entry, we'll build a graphic to display it.

SAS
In SAS, we use the infile statement (section 1.1.2) to read the data, as this affords the most control. As in section 6.4.1, we read a key character from the start of the line, holding that line with the trailing ”@” character. Then, dependent on the value of that character, we use different input statements to read in the date and time values on the one hand, or the rank. If the line does not contain the rank, we read in character values containing the day and time in formats which SAS can interpret, then convert this value to a SAS date-time value using an informat (section A.6.4) applied to a new character string made up of the date and time information. We keep these variable values using the retain statement (as in section 6.4.1). If the line has a rank, we read in the rank and write out the line using the output statement. Note that we do not use either the day of week or the time zone. Finally, we check the data by printing a few lines.

data sales;
infile "c:\data\sales.dat";
retain day month date time edt year;
input @1 type $1 @;
if type ne 's' then do;
   input @1 day $ Month $ year time $ edt $ year;
end;
else do;
   input @12 rank;
   datetime = compress(date||month||year||"/"||time);
   salestime = input(datetime,datetime18.);
   output;
end;
run;

proc print data=sales (obs=6);
   var datetime salestime rank;
run;

Obs        datetime          salestime     rank

  1    9Aug2009/14:38:13    1565447893    141913
  2    9Aug2009/14:40:04    1565448004    141913
  3    9Aug2009/15:00:05    1565449205    149556
  4    9Aug2009/16:00:05    1565452805    158132
  5    9Aug2009/17:00:05    1565456405    166742
  6    9Aug2009/18:00:05    1565460005    175812

R
In R, we begin by reading the file (this is done relative to the current working directory (see getwd(), section 1.7.3). We then calculate the number of entries by dividing the file's length by two.

Next, we create two empty vectors of the correct length and type to store the data in, once we extract it from the file.

Once this preparatory work is completed, we loop (section 1.1.11) through the file, reading in the odd-numbered lines (lines (i-1)*2+1), some date/time values from the Eastern US time zone, with daylight savings applied. The gsub() function (also used in section 6.4.1 and related to the grep() function in 1.4.6) is used to replace matches determined by regular expression matching. In this situation, it is used to remove the time zone from the line before this processing. These date/time values are read into the timeval vector. Even-numbered lines (lines i*2) are read into the rank vector, after removing the string "salesrank=" (again using gsub()).

Finally, we make a data frame (section B.4.5) from the two vectors and display the first few lines using the head() function (section 1.13.1).

file <- readLines("sales.dat")
n <- length(file)/2
rank <- numeric(n)
timeval <- as.POSIXlt(rank, origin="1960-01-01")
for (i in 1:n) {
   timeval[i] <- as.POSIXlt(gsub('EST', '', 
      gsub('EDT', '', file[(i-1)*2+1])), 
      tz="EST5EDT", format="%a %b %d %H:%M:%S %Y")
   rank[i] <- as.numeric(gsub('salesrank= ','',file[i*2]))
}
timerank <- data.frame(timeval, rank)

We can review the results:

> head(timerank)
              timeval   rank
1 2009-08-09 14:38:13 141913
2 2009-08-09 14:40:04 141913
3 2009-08-09 15:00:05 149556
4 2009-08-09 16:00:05 158132
5 2009-08-09 17:00:05 166742
6 2009-08-09 18:00:05 175812