Receiving KeyError when merging two DataFrames

Viewed 24

I'm creating two DataFrames; one from an Oracle SQL query, one from a SSMS query and merging them together with join specification.

It is returning: KeyError: "None of [Index(['BI.BookingItemID'], dtype='object')] are in the [columns]"

Any help is appreciated.

Here is the code with identifiers masked:

#Define Save Location
    fileName = 'C:\\Users\Me\OneDrive - ' + timeStr + '.xlsx'

    #Import Password
    with open("C:\\Users\\Me\\OneDrive Password","r") as pwr:
        pw = pwr.read()

    print("Connecting to Oracle")

    #Connecting - Gathering Data
    Oconnection = cx_Oracle.connect("Me", pw, "Oracle Path/ORAPRD", encoding = "UTF-8", nencoding = "UTF-8")

    print("Oracle Connected!")

    #Defining Query
    query1 = open("C:\\Users\Me\Oracle Query [py].sql","r")

    print("Running Oracle Query")

    #Creating Dataframe/Executing Query
    Odata = pd.read_sql(query1.read(), Oconnection)

    print("Creating OData Data Frame")

    #Closing Connection
    Oconnection.close()
    query1.close()

    print("Closing Oracle Connection")

    #Connecting to SSMS
    SSMSconnection = ("Driver={SQL Server Native Client 11.0};"
                "Server=SSMS;"
                "Database=DatabaseQuery;"
                "Trusted_Connection=yes;")

    cnxn = pyodbc.connect(SSMSconnection)

    print("Setting Up SSMS Connection")


    query2 = open("C:\\Users\\Me\\SQL Query [py].sql","r")

    print("Running SSMS Query")

    SSMSData = pd.read_sql(query2.read(), cnxn)

    print("Creating SSMS Dataframe")

    del cnxn

    print("Closing SSMS SQL Connection")

    #Merging Data Frames
    merge = pd.merge(Odata,
                    SSMSData[['BI.BookingItemID']],
                    left_on = 'BI.BookingItemID',
                    right_on = 'CTLA.ATTRIBUTE12',
                    how = 'left')


    writer = pd.ExcelWriter(fileName,engine='xlsxwriter')
    merge.to_excel(writer,index=False,sheet_name="Export")

    print("Joining Odata & SSMS Data Frames")

    writer.save()

    print("Saving Merged Data Frame")
1 Answers

The right and left keys of your pandas.merge are reversed.

Replace this :

merge = pd.merge(Odata,
                SSMSData[['BI.BookingItemID']],
                left_on = 'BI.BookingItemID',
                right_on = 'CTLA.ATTRIBUTE12',
                how = 'left')

By :

merge = pd.merge(Odata,
                SSMSData[['BI.BookingItemID']],
                left_on = 'CTLA.ATTRIBUTE12',
                right_on = 'BI.BookingItemID',
                how = 'left')
Related