0

I have df1

Ship_Date Price
07/15/2014 5
08/19/2015 9
09/20/2016 7

I also have df2

Ship_Date
08/19/2015
07/15/2014
09/20/2016
07/15/2014

I need the final output for df2 to be

Ship_Date Price
08/19/2015 9
07/15/2014 5
09/20/2016 7
07/15/2014 5

I also already added the 'Price' Column for df2. I need help setting the values in each cell for the 'Price' column to be the price corresponding to the date in df1

Patron Ng
  • 11

2 Answers2

1

You can do this a couple of ways.

merging the 2 dataframes on "Ship_Date"

new_df = df2.merge(df1, on="Ship_Date", how="left")

print(new_df)
    Ship_Date  Price
0  08/19/2015      9
1  07/15/2014      5
2  07/15/2014      5
3  09/20/2016      7

Using the map function to create a new column:

date_mappings = df1.set_index("Ship_Date")["Price"]
df2["Price"] = df2["Ship_Date"].map(date_mappings)

print(df2)
    Ship_Date  Price
0  08/19/2015      9
1  07/15/2014      5
2  09/20/2016      7
3  07/15/2014      5

Temporarily set df1.index to be "Ship_Date", then reindexing df1 so it takes the same shape as df2:

new_df = df1.set_index("Ship_Date").reindex(df2["Ship_Date"]).reset_index()

print(new_df)
    Ship_Date  Price
0  08/19/2015      9
1  07/15/2014      5
2  09/20/2016      7
3  07/15/2014      5
Cameron Riddell
  • 10,942
  • 9
  • 19
  • If I use merge, won't it leave null data? For example, I have two 07/15/2014 in df2 but only one 07/15/2014 in df1. – Patron Ng Jul 13 '21 at 22:57
  • It will simply repeat the data from `df1` (as shown in the output from the answer)- however if you have a date in `df1` that is not in `df2` this when you will generate nulls. If this is the case (or to be more cautious in general), you should use `df2.merge(df1, on="Ship_Date", how="left")` to ensure that you only output Ship_Dates that exist in df2. – Cameron Riddell Jul 13 '21 at 23:02
0

You can use the map function for the pandas series:

Map the df2['Ship_Date'] to the values of df1['Price'], and store the mapped series in your 'Price' column of the df2. The mapping argument has to be a pandas series with the index 'Ship_Date'.It can be done by setting the df1 index to the 'Ship_Date' and selecting the 'Price' column.

df2['Price'] = df2['Ship_Date'].map(df1.set_index('Ship_Date')['Price'])

Alpha3
  • 1
  • 1