Merging two tsv files in two directories with same name,if the file name matches the merge them based on rows in the first file using python /R

Question

file A

phy     44  
chem    46 
maths   44  
biol    42
his     38
comm    40

file B

        RaK   John
Phy    45   23
chem    43  45
maths   41   90
His     38   25
Comm    48  12
geo     49   42

Expected output

            RaK
phy    44   45
chem    46  43
maths   44  41
biol    42  0
his     38  38
comm    40  48

I need somthing like join(-a1 a,b) but in python which should be able to handle huge amount of data

Answer 1

1. lower strings in B's index to match A's '0' column

2. merge on index and column, use left-join (or right-join) to keep non overlapping cases and fill NA's with zeros:

   import pandas as pd
   
   A = pd.read_clipboard(header=None)
   
   B = pd.read_clipboard()
   
   B.index = [x.lower() for x in B.index]
   
   A.merge(B[['RaK']],left_on=0,right_index=True,how='left').fillna(0)
   
          0    1   RaK
   0   phy     44  45
   1   chem    46  43
   2   maths   44  41
   3   biol    42  0
   4   his     38  38
   5   comm    40  48