max in window functions

Question

Input DF:

id .  sub_id .   id_created .  id_last_modified   sub_id_created . lead_
1 .    10          12:00         7:00               12:00 .        1:00
1 .    20 .        12:00         7:00                1:00 .        2:30
1 .    30 .        12:00         7:00                2:30 .        7:00
1 .    40          12:00         7:05                7:00          null

Use case, I am trying to create a new_column "time", where:

1. For: (id, max(sub_id)) : id_last_modified - sub_id_created
2. otherwise:  sub_id_created - lead_

Code:

window = Window.partitionBy("id").orderBy("sub_id")

I am getting the expected op for all the rows except for the combination of:

(id, max(sub_id))

for which I am getting null

Any suggestions on where am I going wrong will be helpful. Thanks.

and how does unix_timestamp converts formats as 7:00 to valid timestamp? as you say its partially working — Anahcolus
– Anahcolus, Commented Jun 27, 2018 at 5:17

Vijay Dahiya · Accepted Answer · 2019-04-24 19:09:49Z

1

Guess this might work

df = df.withColumn("time",
when($"sub_id"===max($"sub_id").over(window), 
(unix_timestamp($"id_last_modified")- 
unix_timestamp($"sub_id_created"))/3600.0).otherwise( 
(unix_timestamp($"sub_id_created") - 
unix_timestamp(lead($"sub_id_created", 1).over(window)))/3600.0))

answered Apr 24, 2019 at 19:09

Vijay Dahiya

1387 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

data_person · Accepted Answer · 2019-05-07 21:38:15Z

0

import pandas_datareader as web
import datetime
start = datetime.datetime(2018, 5, 1)
end = datetime.datetime(2019, 5, 31)
df = web.DataReader("goog", 'yahoo', start, end)

answered May 7, 2019 at 21:38

data_person

4,59010 gold badges48 silver badges77 bronze badges

Collectives™ on Stack Overflow

max in window functions

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related