Transferring specific data from one excel file to another using python
Question: I just started learning Python and I need help with a script my internship
asked me to write.

I have a csv file (sheet1.csv) and I need to extract data from only two of the
columns which have the headers referenceID and PartNumber that correspond to
each other. I need to update a separate csv file called sheet2.csv which also
contains the two columns referenceID and PartNumber however many of the
PartNumber cells are empty.

Basically I need to fill in the “PartNumber” field with the values from
sheet1. From the research I’ve done I’ve decided using dictionaries are a
solid approach to writing this script (I think). So far I have been able to
read the files and create two dictionaries with the referenceIDs as the keys
and the PartNumber as values… Here is what I have showing an example of what
the dictionaries look like.

    
    
    import csv 
    a = open('sheet1.csv', 'rU')
    b = open('sheet2.csv', 'rU')
    csvReadera = csv.DictReader(a)
    csvReaderb = csv.DictReader(b)
    a_dict = {}
    b_dict = {}
    
    for line in csvReadera:
        a_dict[line["ReferenceID"]] = line["PartNumber"]
    print(a_dict)
    
    for line in csvReaderb:
        b_dict[line["ReferenceID"]] = line["PartNumber"]
    print(b_dict)
    
    a_dict = {'R150': 'PN000123', 'R331': 'PN000873', 'C774': 'PN000064', 'L7896': 'PN000447', 'R0640': 'PN000878', 'R454': 'PN000333'}
    b_dict = {'C774': '', 'R331': '', 'R454': '', 'L7896': 'PN000000', 'R0640': '', 'R150': 'PN000333'}
    

How can I compare the two dictionaries and fill in/overwrite the missing
values for b-dict and then write to sheet2? Certainly, there must be more
efficient methods than what I have come up with, but I have never used Python
before so please forgive my pitiful attempt!


Answer: have a look at the pandas library.

    
    
    import padas as pd
    
    #this is how you read
    dfa = pd.read_csv("sheet1.csv")
    dfb = pd.read_csv("sheet2.csv")
    

let s jus take the dicts you defined as testdata

    
    
    a_dict = {'R150': 'PN000123', 'R331': 'PN000873', 'C774': 'PN000064', 'L7896': 'PN000447', 'R0640': 'PN000878', 'R454': 'PN000333'}
    b_dict = {'C774': '', 'R331': '', 'R454': '', 'L7896': 'PN000000', 'R0640': '', 'R150': 'PN000333'}
    dfar = pd.DataFrame(a_dict.items(), columns = ['ReferenceID', 'PartNumber'])
    dfbr = pd.DataFrame(b_dict.items(), columns = ['ReferenceID', 'PartNumber'])
    dfa = dfar[['ReferenceID', 'PartNumber']]
    dfa.columns = ['ReferenceIDA', 'PartNumberA']
    dfb = dfbr[['ReferenceID', 'PartNumber']]
    dfb.columns = ['ReferenceIDB', 'PartNumberB']
    

you get this

    
    
      In [97]: dfa
    Out[97]: 
      ReferenceIDA PartNumberA
    0         R331    PN000873
    1         R454    PN000333
    2        L7896    PN000447
    3         R150    PN000123
    4         C774    PN000064
    5        R0640    PN000878
    
    In [98]: dfb
    Out[98]: 
      ReferenceIDB PartNumberB
    0         R331            
    1         R454            
    2        R0640            
    3         R150    PN000333
    4         C774            
    5        L7896    PN000000
    

now

    
    
        In [67]: cd = pd.concat([dfa,dfb], axis=1)
    
        In [68]: cd
        Out[68]: 
      ReferenceIDA PartNumberA ReferenceIDB PartNumberB
    0         R331    PN000873         R331            
    1         R454    PN000333         R454            
    2        L7896    PN000447        R0640            
    3         R150    PN000123         R150    PN000333
    4         C774    PN000064         C774            
    5        R0640    PN000878        L7896    PN000000
    
    
    
    
    cd["res"] = cd.apply(lambda x : x["PartNumberB"] if x["PartNumberB"] else x["PartNumberA"], axis=1)
    
     cd
    Out[106]: 
      ReferenceIDA PartNumberA ReferenceIDB PartNumberB       res
    0         R331    PN000873         R331              PN000873
    1         R454    PN000333         R454              PN000333
    2        L7896    PN000447        R0640              PN000447
    3         R150    PN000123         R150    PN000333  PN000333
    4         C774    PN000064         C774              PN000064
    5        R0640    PN000878        L7896    PN000000  PN000000
    

this is what you wanted

just set

    
    
    dfbr['PartNumber'] = cd['res']
    

and dump to csv

    
    
    dfbr.to_csv('sheet2.csv')
    

