0

enter image description here

I want date new column (Output) from df2['Date'].

Condition: vlookup DEVICE_SN and first over date from df2['Date'].

Take simple dataframe by dates.

wjandrea
  • 28,235
  • 9
  • 60
  • 81
Lucas Tornike
  • 13
  • 2
  • 1
    Welcome back to Stack Overflow! Please take the [tour]. SO is a Q&A site, but this isn't really a question. Please read [ask]. And [don't post pictures of text](https://meta.stackoverflow.com/q/285551/4518341); instead, copy the text itself and use the formatting tools like [code formatting](/editing-help#code). See also [How to make good reproducible pandas examples](/q/20109391/4518341). – wjandrea Feb 24 '23 at 15:17
  • Please provide the dataset(s) in a reproducible format! – mozway Feb 24 '23 at 15:37
  • 1
    @TornikeKharitonishvili are you and the question asker the same person? – mozway Feb 24 '23 at 15:37
  • @mozway Yes, unfortunately I can't post. The answer below solved the problem. – Tornike Kharitonishvili Feb 24 '23 at 15:38
  • @TornikeKharitonishvili in a very inefficient way, if you're interested in a potentially better way please provide a sample dataset (it can be dummy) ;) – mozway Feb 24 '23 at 15:39
  • Yes :), I need an efficient way. Take any base with date. – Tornike Kharitonishvili Feb 24 '23 at 15:41
  • Please provide enough code so others can better understand or reproduce the problem. – Community Feb 24 '23 at 17:24

1 Answers1

1

Here I have used two lambda first to get the list of greater than dates of each ID, in next lambda I am selecting first dates of each list, in case if will empty list will return 'no'

Code:

#Change column type str to date, so you can Compare
df1['Date']= pd.to_datetime(df1['Date'])
df2['Date']= pd.to_datetime(df2['Date'])

df1['output'] = df1.apply(lambda r: [d for d in df2.loc[df2['ID']==r.ID]['Date'] if d > r.Date], axis=1).apply(lambda x: x[0] if x else 'no')
df1

Output:

    ID      Date            output
0   11  2022-05-27  2022-06-11 00:00:00
1   22  2022-01-12  no
2   33  2022-03-01  2022-06-28 00:00:00
3   44  2018-07-19  no
4   55  2018-10-11  no
R. Baraiya
  • 1,490
  • 1
  • 4
  • 17