Pandas how to merge a parent dataframe with 2 child dataframes

Question

I have a dataframe named invoice (which is an invoice), as follows:

id      invoice_id  date_invoice    user_id       invoice_line_ids
5449      5449        2019-07-12   Administrator   [5758, 5759]

And another dataframe named invoice_lines(which are the invoice lines of that particular invoice) as follows:

id     invoice_id   product_id     product_name
5758    5449        1          Amazon Registration
5759    5449        19         Premium SMART Monthly

This is what I expected

But Here is what I got:

Here is what I tried:

result = pd.merge(invoice_dataframe, invoice_line_dataframe_transpose, how='outer', on='invoice_id')

Question is how to merge the dataframes together, so that the first invoice line adds up to the invoice dataframe, and second invoice line appears as having no parent invoice, just under first invoice line?

so your problem is replacing duplicates in the first 4 cols with blank spaces? by the looks of it that is what it appears — anky, Jul 12 '19 at 09:29

score 1 · Accepted Answer · answered Jul 12 '19 at 09:54

Use something like the below:

final=pd.merge(invoice_dataframe, invoice_line_dataframe_transpose, how='outer',
                                             on='invoice_id',suffixes=('','_y'))
s=(final.assign(invoice_line_ids=final.invoice_line_ids.apply(tuple))
   .duplicated(invoice_dataframe.columns)) #apply tuple is required
final.loc[s,invoice_dataframe.columns]=''
print(final)

Pandas how to merge a parent dataframe with 2 child dataframes

1 Answers1