How to merge two dataframes panda

Question

I have two data frames that I am trying to join yet not successfully. Here is the data frame:

 df1
     symbol count
 0.  AAL      20
 1.  BBY      34
 2.  CLL      23
 3.  DKG      12
 4.  LMND     20

 df2
     symbol TYPE count
 0.  AAL    CALL  11
 1.  AAL    PUT   9
 2.  BBY    CALL  30
 3.  BBY    PUT   4
 4.  CLL    CALL  23
 5.  CLL    PUT   3
 6.  DKG    CALL  6
 7.  DKG    PUT   6
 8.  LMND   CALL  10
 9.  LMND   PUT   10

Expected out put

 symbol  TotalCount  Put  Call
 0.  AAL    20         9    11
 1.  BBY    34         4    30 
 2.  CLL    26         3    23
 3.  DKG    12         6    6
 4.  LMND   20         10   10

Here is my attempt: newdf = pd.merge(df1,df2, how='left') yet this is not marging it right. What am I missing?

Answer 1

You can use a combination of groupby and unstack:

df1.merge(
    df2.groupby(['symbol', 'TYPE'])['count'].max().unstack('TYPE'),
    how='left', left_on='symbol', right_on='symbol'
)

  symbol  count  CALL  PUT
0    AAL     20    11    9
1    BBY     34    30    4
2    CLL     23    23    3
3    DKG     12     6    6
4   LMND     20    10   10

Answer 2

You need to create the Call and Put columns first (which can be achieved by pivoting) before merging:

(
    df1.merge(df2.pivot("symbol", "TYPE", "count"), on="symbol", how="left")
    .assign(TotalCount=lambda x: x['CALL'] + x['PUT'])
    .drop(columns="count")
)

  symbol  CALL  PUT  TotalCount
0    AAL    11    9          20
1    BBY    30    4          34
2    CLL    23    3          26
3    DKG     6    6          12
4   LMND    10   10          20