#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0

    Join two arrays based upon a common value (like SQL Left Join


    My goal is to join two arrays based on a common value using Python 2.7.
    I start with a database query to return the current status of my ambulances. There are four columns that come out of my database:
    -) Ambulance
    -) time stamp
    -) Unit status
    -) disposition

    It looks like this as it is output into an array:
    ('M01', '13:35:53', 'USOS', 'OutofService')
    ('M02', '13:46:45', 'CM', 'NA')
    ('M03', '13:37:29', 'USRQ', 'Free')

    In my program I have hard-coded the x y coordinates of my ambulances stations in an array.
    My ambulance stations coordinates look like this:
    Stations = [['M01', 446, 267], ['M16', 28, 45], ['M37', 504, 546]]
    My goal now is to join the two arrays such that my ambulance dispositions and my ambulance stations are joined based on the ambulance as my attribute.
    -) Ambulance
    -) time stamp
    -) Unit status
    -) disposition
    -) Ambulance
    -) X coordinate
    -) Y coordinate

    My goal is to have my data in an array look like this at the end of the program:
    ('M01', '13:35:53', 'USOS', 'OutofService', 'M01', 446, 267 )
    ('M02', '13:46:45', 'CM', 'NA', 'M02', 452, 176)
    ('M03', '13:37:29', 'USRQ', 'Free', 'M03', 474, 155)

    think of it as an SQL LEFT OUTER JOIN ON the ambulance as my common key.
    Or it could even be INNER JOIN.

    Here is my computer program:


    import pyodbc

    cnxn = pyodbc.connect('connection information goes here')
    cursor = cnxn.cursor()
    AmbulanceStatus = """
    this query goes to my database and returns the most recent status of my each of my ambulances
    """

    cursor.execute(AmbulanceStatus)
    Dispositions = []
    Dispositions = cursor.fetchall()
    cursor.close()

    #===============================================================================
    # This query outputs the following four columns into an array:
    # 1) Ambulance,
    # 2) TimeStamp,
    # 3) Unit Status,
    # 4) Disposition
    # example:
    # ('M01', '13:35:53', 'USOS', 'OutofService')
    # ('M02', '13:46:45', 'CM', 'NA')
    # ('M03', '13:37:29', 'USRQ', 'Free')
    #===============================================================================

    for item in Dispositions:
    print item
    print 'end of database output'

    # grid coordinates of ambulance stations
    Stations = [['M01', 446, 267], ['M16', 28, 45], ['M37', 504, 546], ['M13', 514, 491], ['M06', 589, 538],
    ['M04', 353, 264], ['M30', 372, 216], ['M14', 359, 125], ['M28', 319, 164], ['M26', 519, 138],
    ['M02', 452, 176], ['M03', 474, 155], ['M23', 536, 221], ['M18', 478, 212], ['M11', 546, 112],
    ['M21', 534, 431], ['M31', 430, 95], ['M15', 347, 341], ['M08', 419, 331], ['M20', 409, 278],
    ['M27', 375, 327], ['M12', 387, 161], ['M22', 338, 190], ['M40', 446, 245], ['M10', 179, 207],
    ['M09', 497, 381], ['M33', 459, 346], ['M25', 543, 394], ['M05', 224, 417], ['M19', 520, 85],
    ['M24', 359, 125], ['MT01', 370, 178], ['FBA31', 370, 178],
    ['FBA32', 363, 365], ['FBA33', 415, 444], ['D1', 442, 191],
    ['M07', 431, 145], ['M17', 404, 63], ['LAR104', 659, 216],
    ['LAR103', 612, 204], ['LAR102', 700, 231], ['LAR101', 738, 170]]

    #===============================================================================
    # My goal is to join the database output with the ambulance stations.
    # Very similar to an SLQ Left Outer Join
    # take M01 information from the database and join it to M01 station location coordinates.
    # My goal is to have an array with the following columns:
    # 1) Ambulance,
    # 2) TimeStamp,
    # 3) Unit Status,
    # 4) Disposition
    # 5) Ambulance
    # 6) X coordinate
    # 7) Y coordinate
    #
    # Example output
    # ('M01', '13:35:53', 'USOS', 'OutofService', 'M01', 446, 267 )
    # ('M02', '13:46:45', 'CM', 'NA', 'M02', 452, 176)
    # ('M03', '13:37:29', 'USRQ', 'Free', 'M03', 474, 155)
    #===============================================================================
    END OF PROGRAM

    I have tried a few other techniques and posted a similar question to StackOverflow.com. Google "concatenate two arrays based on a common value", and it will lead you to my previous question.

    NOTE: Sometimes I will have an ambulance that is in my Dispositions array but not in my Stations array. Similarly I will have an ambulance that is a Station, but does not have a disposition. In this case it would like an SQL INNER JOIN.

    But nothing works. I would like to wipe the slate clean and start all over again.
  2. #2
  3. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,625
    Rep Power
    4247
    Code:
    #!/usr/bin/python
    items = [
        ('M01', '13:35:53', 'USOS', 'OutofService'),
        ('M02', '13:46:45', 'CM', 'NA'),
        ('M03', '13:37:29', 'USRQ', 'Free'),
    ]
    stations = [['M01', 446, 267], ['M02', 28, 45], ['M03', 504, 546]]
    
    # First convert stations to a dict using the first element as key
    stations_dict = dict([ (item[0], item[1:]) for item in stations])
    
    output_items = []
    for item in items:
        output_item = list(item) + stations_dict.get(item[0], [])
        output_items.append(output_item)
    
    print output_items
    If you want it to behave more like an inner join, just write your loop like this:
    Code:
    for item in items:
        if stations_dict.has_key(item[0]):
            output_item = list(item) + stations_dict.get(item[0])
            output_items.append(output_item)
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0

    thank you, this works perfectly


    Thank you very much. This works exactly as I want it to.
  6. #4
  7. Contributing User
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Aug 2011
    Posts
    4,854
    Rep Power
    481
    I appreciate this thread. It caused me to finally start learning about database joins.
    [code]Code tags[/code] are essential for python code and Makefiles!

IMN logo majestic logo threadwatch logo seochat tools logo