如何获取半小时之前每行的计数，该计数在某列中为1？ [英] How to get the count for each row before half hour period having the value of 1 in a column?

问题描述

我的数据框如下：

id        time                  type     day
___       _____                 _____    ____
 1   2016-10-12 01:45:01          1       3
 1   2016-10-12 01:48:01          0       3
 1   2016-10-12 01:50:01          1       3
 1   2016-10-12 01:52:01          1       3
 2   2016-10-12 01:53:01          1       3
 2   2016-10-12 02:10:01          1       3
 3   2016-10-12 01:45:01          1       3
 3   2016-10-12 01:48:01          1       3

从这个数据框中，我想计算出每行半小时之前该ID中类型1的出现。
例如，如果我们采用第一行

From this data frame I want to calculate to the occurences of type 1 in that id before half hour for each row. For example if we take the first row

1   2016-10-12 01:45:01          1       3

从这里我要计算 2016-10-12类型1出现的次数01:45:01 到 2016-10-12 01:15:01 ，该ID最终为0，因为它是第一条记录。

From this I want to count the type 1 occurences from 2016-10-12 01:45:01 to 2016-10-12 01:15:01 in that id which is eventually 0 since it is the first record.

       id        time                  type     day     count_of_type1
      ___       _____                 _____    ____    ______________   
        1   2016-10-12 01:45:01         1       3              0

如果我们取第三行

 1   2016-10-12 01:50:01          1       3

由此我想计算从 2016-10-12 01:50:01 到 2016-10的类型1出现次数-12 01:20:01 ，其ID最终为2。

From this I want to count the type 1 occurences from 2016-10-12 01:50:01 to 2016-10-12 01:20:01 in that id which is eventually 2.

   id        time                  type     day     count_of_type1
  ___       _____                 _____    ____    ______________   
  1   2016-10-12 01:50:01           1       3              2

我阅读了以下数据框，并且也知道如何进行计数，但是我不确定的部分是如何分别为每一行添加该列：

I read the data frame as below and also know how to take count but the part i am not sure is how to append the column for each rows individually:

   val df  = sqlContext.read.format("com.databricks.spark.csv").option("header", "true").load("hdfs:///user/rkr/datafile.csv")

我们将不胜感激。

推荐答案

您可以使用自联接获取具有基于时间戳的条件的行。

You can use self-join to obtain rows with timestamp-based condition.

val ds = Seq(( 1,   "2016-10-12 01:45:01",          1,       3),
  ( 1,   "2016-10-12 01:48:01",          0,       3),
  ( 1,   "2016-10-12 01:50:01",          1,       3),
  ( 1,   "2016-10-12 01:52:01",          1,       3),
  ( 2,   "2016-10-12 01:53:01",          1,       3),
  ( 2,   "2016-10-12 02:10:01",          1,       3),
  ( 3,   "2016-10-12 01:45:01",          1,       3),
  ( 3,   "2016-10-12 01:45:01",          1,       3)).
  toDF("id", "time", "type", "day")
  .withColumn("timestamp", unix_timestamp($"time", "yyyy-MM-dd HH:mm:ss"))

val happenBeforeHalfHour = ds.as("left").join(ds.as("right"), $"left.id" === $"right.id" && $"right.type" === 1 &&
  $"left.timestamp" > $"right.timestamp" && $"left.timestamp" - $"right.timestamp" <= 1800)
  .select($"left.id", $"left.time", $"left.type", $"left.day",$"left.timestamp")
happenBeforeHalfHour.groupBy("id", "time", "type", "day", "timestamp").count.show(false)


+---+-------------------+----+---+----------+-----+
|id |time               |type|day|timestamp |count|
+---+-------------------+----+---+----------+-----+
|1  |2016-10-12 01:48:01|0   |3  |1476211681|1    |
|2  |2016-10-12 02:10:01|1   |3  |1476213001|1    |
|1  |2016-10-12 01:52:01|1   |3  |1476211921|2    |
|1  |2016-10-12 01:50:01|1   |3  |1476211801|1    |
+---+-------------------+----+---+----------+-----+

这篇关于如何获取半小时之前每行的计数，该计数在某列中为1？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！