CODEWITHSUNDEEP
pythonpython-3.6
2legit2quit
2legit2quit

Reputation: 21

Pythonic way to match multiple values in a list of lists to another list of lists and return a value

I'm trying to match two or more values from a list of lists to another list of lists and return a value from one of the lists. Much like SQL's on clause - on x.field = y.field and x.field = y.field.

Picture a list of transactions from your Amazon account. The ids are unique, but the names change (darn Amazon!). I want to use the last name/title, based on max date. I could probably do the below with the initial data set, but couldn't think of how. I'm reading in the rows as a list of lists.

I'm just working on a personal project combing through Amazon purchases, but could see this being very useful down the road. I have a solution, but I think it will run very long depending on the size of the data. I've seen people call out Pandas' dataframe as a solution, but I'm trying to learn Python's standard libraries first. It's my first question on Stack, I apologize and thank you in advance.

#Example data set comes from a csv I've read into different list of lists
#Fields in order are ID, date (max date from csv to id) -- data set is unique row count 140
X = [['b12', 8/1/2019], ['c34', 7/25/2018],..]
#Fields in order are ID, date, Name -- data set is unique, due to date, row count 1,231
Y = [['b12', 6/23/19, 'item 1'], ['b12', 7/21/19, 'item 1.0'], ['b12', 8/1/19, 'item 1.1'],..]

#Code that works, but I'm sure is 'expensive'
for i in X:
    for n in Y:
        if i[0] == n[0] and i[1] == n[1]:
           i.append(x[2])
        else: continue


#Result is either I append to X (like I have) or create a new list of lists all together
X
[['b12', 8/1/2019, 'item 1.1'], ['c34', 7/25/2019, 'item 2.8'],...]

Upvotes: 1

Views: 481

Answers (1)

Sunitha
Sunitha

Reputation: 12005

You can create a mapping dict from your list Y with (id, date) as key and the name as value. Then use a list comprehension to create a new list from list X with the the mapped value from the mapping dict

>>> X = [['b12', '8/1/2019'], ['c34', '7/25/2018']]
>>> Y = [['b12', '6/23/19', 'item 1'], ['b12', '7/21/19', 'item 1.0'], ['b12', '8/1/19', 'item 1.1'], ['c34', '7/25/18', 'item2.1']]
>>> 
>>> mapping = {(id, date):name for id,date,name in Y}
>>> res = [[id, date, mapping[(id, date.replace('/20', '/'))]] for id,date in X]
>>> 
>>> print (res)
[['b12', '8/1/2019', 'item 1.1'], ['c34', '7/25/2018', 'item2.1']]

Upvotes: 1

Related Questions