I have three different dfs.
df1 contains the type of trees per grid for each year, df2 contains min-max temperature per grid for each year, and df3 contains temperature limits for each type of tree.
I am trying to create a new df that includes df1 plus a new column "possible" by going through df2 and df3 and checking.
if df2['tmax'] > df3['tLimitMax']: #for the three consecutive years
type = False #for all the subsequent years
else:
type = True
I tried
df_data = df1.merge(df3 , on=[ 'type', 'index'],how='left')
df_data = df_data.merge(df2 , on=[ 'col', 'row','year'],how='left') # Join and Merge datasets
df1['possible'] = df_data['tLimitMax'] <= df_data['tmax'] #Implement condition
However, this still results in True/False for each type each in each year. The logic here is that we only want to eliminate a tree type if the temperatures exceed type limit contained in df3 for a consecutive number of years in this case (3 years).
I can't figure out how to implement the consecutive part, but the rest of the code works fine.
I also tried counting consecutive and implementing the solution provided [here] (Pandas: Get rows with consecutive column values).
Here's the [full 3 dfs merged dataset] (https://1drv.ms/u/s!Asw4WW2nEbsaiNtAd2CeDz5d9fr1rQ?e=ENww3V)
example mini-dataset below
df1: Tree type
col row type index year
-125 42.5 BF 4 2015
-125 42.5 MTF 8 2015
-125 42.5 BF 4 2016
-125 42.5 MTF 8 2016
-125 42.5 BF 4 2017
-125 42.5 MTF 8 2017
-125 42.5 BF 4 2018
-125 42.5 MTF 8 2018
-125 42.5 BF 4 2019
-125 42.5 MTF 8 2019
-125 42.5 BF 4 2020
-125 42.5 MTF 8 2020
-125 32.5 BF 4 2015
-125 32.5 MTF 8 2015
-125 32.5 BF 4 2016
-125 32.5 MTF 8 2016
-125 32.5 BF 4 2017
-125 32.5 MTF 8 2017
-125 32.5 BF 4 2018
-125 32.5 MTF 8 2018
-125 32.5 BF 4 2019
-125 32.5 MTF 8 2019
-125 32.5 BF 4 2020
-125 32.5 MTF 8 2020
df2: Min, Max Temperature
col row tmin tmax year
-125 42.5 7.1 18.7 2015
-125 42.5 7.1 40.7 2016
-125 42.5 7.1 32.7 2017
-125 42.5 7.1 23.7 2018
-125 42.5 7.1 56.7 2019
-125 42.5 7.1 21.7 2020
-125 32.5 7.1 18.7 2015
-125 32.5 7.1 20.7 2016
-125 32.5 7.1 14.7 2017
-125 32.5 7.1 19.7 2018
-125 32.5 7.1 16.7 2019
-125 32.5 7.1 12.7 2020
df3 contains temperature limits for each type of tree in df1.
type index tLimitMin tLimitMax
BF 4 -60 21
MTF 8 -5 23
An example output would be df1 and a new column with True/False, i.e.:
col row type index year possible
-125 42.5 BF 4 2015 True
-125 42.5 MTF 8 2015 True
-125 42.5 BF 4 2016 True
-125 42.5 MTF 8 2016 True
-125 42.5 BF 4 2017 True
-125 42.5 MTF 8 2017 True
-125 42.5 BF 4 2018 True
-125 42.5 MTF 8 2018 True
-125 42.5 BF 4 2019 False
-125 42.5 MTF 8 2019 False
-125 42.5 BF 4 2020 False
-125 42.5 MTF 8 2020 False
-125 32.5 BF 4 2015 True
-125 32.5 MTF 8 2015 True
-125 32.5 BF 4 2016 True
-125 32.5 MTF 8 2016 True
-125 32.5 BF 4 2017 True
-125 32.5 MTF 8 2017 True
-125 32.5 BF 4 2018 True
-125 32.5 MTF 8 2018 True
-125 32.5 BF 4 2019 True
-125 32.5 MTF 8 2019 True
-125 32.5 BF 4 2020 True
-125 32.5 MTF 8 2020 True