Thursday, September 24, 2009

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.

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;
else do;
input @12 rank;
datetime = compress(date||month||year||"/"||time);
salestime = input(datetime,datetime18.);

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

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

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


Anonymous said...

Hey guys, I don't think you need this complicated IF structure in SAS. The following should work fine (SAS will skip to look for the remaining variables in the 2nd line by default):

input day $ Month $ year time $ edt $ year salesrank $ rank;

Ken Kleinman said...

Sweet! That never would have occurred to me.

The kind of testing/holding a line used about is still good to know about, though!