Getting weekly and daily averages of timestamp data

Question

I currently have data on a Spark data frame that is formatted as such:

Timestamp    Number
.........    ......
M-D-Y        3
M-D-Y        4900

The timestamp data is in no way uniform or consistent (i.e., I could have one value that is present on March 1, 2015, and the next value in the table be for the date September 1, 2015 ... also, I could have multiple entries per date).

So I wanted to do two things

Calculate the number of entries per week. So I would essentially want a new table that represented the number of rows in which the timestamp column was in the week that the row corresponded to. If there are multiple years present, I would ideally want to average the values per each year to get a single value.
Average the number column for each week. So for every week of the year, I would have a value that represents the average of the number column (0 if there is no entry within that week).

Sounds reasonable. Now what have you tried? As far as I remember there are different variants of this question already on SO. — zero323, Jun 16 '16 at 18:01
@zero323 I am currently trying to do various commands using GroupBy partitions. I have created new columns that specify the week number, etc. and getting the averages. However, the variants I have tried are currently not compiling so I am working on that. — Eric Staner, Jun 16 '16 at 18:08
So you need two things: 1) ability to select corresponding week or day (covered by built in date functions) 2) left join with dense data (basic arithmetics + built in date functions) — zero323, Jun 16 '16 at 18:48
@zero323 I am looking into the date functions. However, would you mind elaborating on the left join? I'm not too familiar with what to do there. — Eric Staner, Jun 16 '16 at 20:50

score 1 · Accepted Answer · answered Jun 17 '16 at 13:23

Parsing date is relatively easy using built-in functions by combining unix_timestamp and simple type casting:

sqlContext.sql(
  "SELECT CAST(UNIX_TIMESTAMP('March 1, 2015', 'MMM d, yyyy') AS TIMESTAMP)"
).show(false)

// +---------------------+
// |_c0                  |
// +---------------------+
// |2015-03-01 00:00:00.0|
// +---------------------+

With DataFrame DSL equivalent code would be something like this:

import org.apache.spark.sql.functions.unix_timestamp

unix_timestamp($"date", "MMM d, yyyy").cast("timestamp")

To fill missing entries you can use different tricks. The simplest approach is to use the same parsing logic as above. First let's create a few helpers:

def leap(year: Int) = {
  ((year % 4 == 0) && (year % 100 != 0)) || (year % 400 == 0)
}
def weeksForYear(year: Int) = (1 to 52).map(w => s"$year $w")
def daysForYear(year: Int) = (1 to { if(leap(2000)) 366 else 366 }).map(
  d => s"$year $d"
)

and example reference data (here for weeks but you can do the same thing for days):

import org.apache.spark.sql.functions.{year, weekofyear}'

val exprs = Seq(year($"date").alias("year"), weekofyear($"date").alias("week"))

val weeks2015 = Seq(2015)
  .flatMap(weeksForYear _)
  .map(Tuple1.apply)
  .toDF("date")
  .withColumn("date", unix_timestamp($"date", "yyyy w").cast("timestamp"))
  .select(exprs: _*)

Finally you can transform the original data:

val df = Seq(
  ("March 1, 2015", 3), ("September 1, 2015", 4900)).toDF("Timestamp", "Number")

val dfParsed = df
  .withColumn("date", unix_timestamp($"timestamp", "MMM d, yyyy").cast("timestamp"))
  .select(exprs :+ $"Number": _*)

merge and aggregate:

weeks2015.join(dfParsed, Seq("year", "week"), "left")
  .groupBy($"year", $"week")
  .agg(count($"Number"), avg($"Number"))
  .na.fill(0)

Getting weekly and daily averages of timestamp data

1 Answers1