Reindex Pandas Dataframe Based On Uneven Dates And Then Groupby And Blank Fill Certain Values

I have a dataframe that looks something like this: df Name date season binary Apple 2016-04-24 MAM 1 Banana 2016-05-01 MAM 1 Berry 2016-05-01 MAM

Solution 1:

We using merge_asof

df['Year']=df['date'].dt.year
df1['Year']=df1['date'].dt.year
A=[]
for x in range(len(df)):
    Temp=pd.merge_asof(df1.sort_values('date'),df.sort_values('date').iloc[[x],:],on='date',by=['Year','season'],direction='forward',allow_exact_matches =False).dropna().drop('Week',1)
    Temp.binary=np.nan
    A.append(Temp)
A.append(df)
target=pd.concat(A).sort_values(['Name','date'])


target
Out[262]: 
      Name  Year  binary       date season
0    Apple  2016     NaN 2016-03-28    MAM
1    Apple  2016     NaN 2016-04-04    MAM
2    Apple  2016     NaN 2016-04-11    MAM
3    Apple  2016     NaN 2016-04-18    MAM
0    Apple  2016     1.0 2016-04-24    MAM
0   Banana  2016     NaN 2016-03-28    MAM
1   Banana  2016     NaN 2016-04-04    MAM
2   Banana  2016     NaN 2016-04-11    MAM
3   Banana  2016     NaN 2016-04-18    MAM
4   Banana  2016     NaN 2016-04-25    MAM
1   Banana  2016     1.0 2016-05-01    MAM
0    Berry  2016     NaN 2016-03-28    MAM
1    Berry  2016     NaN 2016-04-04    MAM
2    Berry  2016     NaN 2016-04-11    MAM
3    Berry  2016     NaN 2016-04-18    MAM
4    Berry  2016     NaN 2016-04-25    MAM
2    Berry  2016     1.0 2016-05-01    MAM
47   Berry  2017     NaN 2017-10-02    OND
48   Berry  2017     NaN 2017-10-09    OND
49   Berry  2017     NaN 2017-10-16    OND
50   Berry  2017     NaN 2017-10-23    OND
51   Berry  2017     NaN 2017-10-30    OND
3    Berry  2017     1.0 2017-11-05    OND
20    Kiwi  2017     NaN 2017-03-13    MAM
21    Kiwi  2017     NaN 2017-03-20    MAM
5     Kiwi  2017     1.0 2017-03-27    MAM
12  Orange  2016     NaN 2016-10-03    OND
13  Orange  2016     NaN 2016-10-10    OND
14  Orange  2016     NaN 2016-10-17    OND
15  Orange  2016     NaN 2016-10-24    OND
16  Orange  2016     NaN 2016-10-31    OND
17  Orange  2016     NaN 2016-11-07    OND
18  Orange  2016     NaN 2016-11-14    OND
19  Orange  2016     NaN 2016-11-21    OND
4   Orange  2016     1.0 2016-11-27    OND