Pandas - How to find row values based on new column data and self-merge with multiple criteria

user_8911278 0
Viewed 41

I have a csv like this:

date,asin,ordered,forecast

2020-05-31,AAAAAA,300,1000

2020-05-31,BBBBBB,500,2000

...

2020-06-28,AAAAAA,980,1500

2020-06-28,BBBBBB,1900,2500

I want to find the date + 28 days and add a new column with the value in ordered. For example, adding 28 days to 2020-05-31 will give me 2020-06-28. For date = 2020-05-31 and asin = AAAAAA, there will be a new column new with the number 980 (from ordered column), which corresponds to the same asin but different date (2020-06-28):

date,asin,ordered,forecast,new

2020-05-31,AAAAAA,300,1000,980

2020-05-31,BBBBBB,500,2000,1900

...

2020-06-28,AAAAAA,980,1500, <this value will look for the date 28 days after 2020-06-28 and asin AAAAAA and get that ordered value>

2020-06-28,BBBBBB,1900,2500 <this value will look for the date 28 days after 2020-06-28 and asin BBBBBB and get that ordered value>

So far I have gotten the +28 days part by doing df['date'] + pd.DateOffset(days=28) but I don't know how to search for the new date and asin elsewhere in the dataframe and bring in the ordered value to the current row.

pandas dataframe python
1 Answers

Try this. I am using the 4 rows of the dataframe that are visible in your answer.

First we add the new date:

df['new_date'] = df['date'] + timedelta(days=28)

Then we merge df with itself on columns as indicated in the command. This basically matches up new_date to date (for each asin separately) and brings the corresponding ordered value to the right row. Then I clean it up a bit. You may want to do this step by step to understand what's going on

(df.merge(df[['date','asin','ordered']], 
            left_on = ['new_date','asin'], right_on=['date','asin'], how='left', suffixes = ('','_new'))
    .drop(columns = ['date_new'])
    .rename(columns = {'ordered_new':'new'})
)

output

    date                 asin      ordered    forecast  new_date               new
--  -------------------  ------  ---------  ----------  -------------------  -----
 0  2020-05-31 00:00:00  AAAAAA        300        1000  2020-06-28 00:00:00    980
 1  2020-05-31 00:00:00  BBBBBB        500        2000  2020-06-28 00:00:00   1900
 2  2020-06-28 00:00:00  AAAAAA        980        1500  2020-07-26 00:00:00    nan
 3  2020-06-28 00:00:00  BBBBBB       1900        2500  2020-07-26 00:00:00    nan
user_145514260
Related