Insert Date from One Dataframe to Another with respect to Invoice Number

Question

So basically, I want to insert the Date from one dataframe to another based on invoiceNo. The problem is that first Dataframe has single entries for each invoice and the later one has multiple entries for the invoice number. Let me show you.

df=pd.read_csv('/content/Sells.csv')

df_details=pd.read_csv('/content/Sells_Detail.csv')

df[df['InvoiceNo']==3]

This is the row that shows up

df_details[df_details['InvoiceNo']==3]

This shows up from the second for the 2nd dataframe

Now, you can understand me. I want to pick the date from the first dataframe for a specific InvoiceNo and the add a new column in 2nd dataframe where that Date will be inserted against that specific InvoiceNo. And it should happen for every InvoiceNo. Thanks!!

post your input dataframes, not jpg links. and show us your expected output — sygneto, Jul 23 '20 at 06:04
can Invoice no have multiple dates? or unique dates for each invoice number — Pygirl, Jul 23 '20 at 06:04
1 invoiceNo will be unique to a date, but there can be multiple InvoiceNo generated on a single date, like what happens in a POS system. And I am really sorry, the dataset is confidential :( — NoobScorpio, Jul 23 '20 at 06:11
@sygneto I just want to append a column in the 2nd pic with Date relevant to the given InvoiceNo. — NoobScorpio, Jul 23 '20 at 06:12

Pygirl · Accepted Answer · 2020-07-23T06:14:39.823

You can use merge.

Dummy example:

df:

    id  val1    price
0   1   21      20
1   2   31      40
2   3   20      46
3   4   51      31

df1:

    id  sell_price
0   1   10
1   2   20
2   1   16
3   4   31
4   2   12

df1.merge(df[['id', 'val1']], on = 'id')

    id  sell_price  val1
0   1   10          21
1   1   16          21
2   2   20          31
3   2   12          31
4   4   31          51

Here id will be your InvoiceNo and val1 will be your InvoiceDate

Insert Date from One Dataframe to Another with respect to Invoice Number

1 Answers1