Hello I would like to create a new column with a counter based on the condition of Tag1 column.
I have this:
| Time | Tag1 |
|---|---|
| 1 | 0 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 1 |
| 9 | 1 |
| 10 | 1 |
| 11 | 0 |
| 12 | 0 |
And I would like this:
| Time | Tag1 | Counter |
|---|---|---|
| 1 | 0 | 0 |
| 2 | 1 | 1 |
| 3 | 1 | 2 |
| 4 | 1 | 3 |
| 5 | 0 | 0 |
| 6 | 0 | 0 |
| 7 | 1 | 1 |
| 8 | 1 | 2 |
| 9 | 1 | 3 |
| 10 | 1 | 4 |
| 11 | 0 | 0 |
| 12 | 0 | 0 |
I tried with function.when(df.Tag1 == 1, function.lag(df.Tag1)+1).otherwise(0) but doesn't work.
Any idea?
Thanks a lot
Window function
new = (df.withColumn('Counter',(col('Tag1')=='0'))#Create Bool
.withColumn('Counter', F.sum(F.col('Counter').cast('integer')).over(Window.partitionBy().orderBy().rowsBetween(-sys.maxsize, 0)))# Create Group by summing bool
.withColumn('Counter', when(col('Tag1')==0, col('Tag1')).otherwise(F.sum('Tag1').over(Window.partitionBy('Counter').orderBy().rowsBetween(-sys.maxsize, 0))))#Conditionally add
)
new.show()
+----+----+-------+
|Time|Tag1|Counter|
+----+----+-------+
| 1| 0| 0|
| 2| 1| 1|
| 3| 1| 2|
| 4| 1| 3|
| 5| 0| 0|
| 6| 0| 0|
| 7| 1| 1|
| 8| 1| 2|
| 9| 1| 3|
| 10| 1| 4|
| 11| 0| 0|
| 12| 0| 0|
+----+----+-------+