LOOKUP using 2 dataframes in Python

I have 2 dataframes:

df1

Id     CategoryId
1       A
1       B
2       A
2       E
2       F


df2:

Id   A    B   C   D   E   F
1
2


I want to do a lookup which will help me fill up the values in df2 based on the values of df1

If df1 has id = 1 and CategoryId = A then I want df2 row 1 Column A to say Y else N

Final df2 should look like:

 Id   A    B   C   D   E   F
1    Y    Y   N   N   N   N
2    Y    N   N   N   Y   Y


I am not sure how to do this in Python. I would really appreciate any help on this!

Thanks

• How big are your two tables? What is the result of df1.shape and df2.shape? Commented Sep 10, 2018 at 21:50

To get the exact answer you provided, included entries for negative cases, you will have to create a dataframe in advance that is all possible Id and Categories values. No pain, no gain!

Let's walk through my solution, starting with imports:

In [1]: from itertools import product   # will compute Id/Category possibilities

In [2]: import pandas as pd


Create your example dataframe

In [3]: df1 = df1 = pd.DataFrame(data={'Id': [1, 1, 2, 2, 2], 'CategoryId': ['A', 'B', 'A', 'E', 'F
...: ']})[['Id', 'CategoryId']]

In [4]: df1
Out[4]:
Id CategoryId
0   1          A
1   1          B
2   2          A
3   2          E
4   2          F


Here you must provide the possible values for the "Id" and "CategoryId" columns

# the ids you showed
In [5]: ids = range(1, 3)            # gives [1, 2]

# Either manually create the category values...
In [6]: cats = ['A', 'B', 'C', 'D', 'E', 'F']

# Or get jiggy with some Python to be more flexible:
In [7]: cats = [chr(c) for c in range(ord('A'), ord('F') + 1)]


Knowing the possible values for each column, we can now compute all possible combinations of those:

In [8]: possibilities = list(product(ids, cats))

In [9]: possibilities
Out[9]:
[(1, 'A'),
(1, 'B'),
(1, 'C'),
(1, 'D'),
(1, 'E'),
(1, 'F'),
(2, 'A'),
(2, 'B'),
(2, 'C'),
(2, 'D'),
(2, 'E'),
(2, 'F')]


Next we can pre-allocate a results table using the possible Id and Category values:

In [8]: results = pd.DataFrame(index=ids, columns=cats).fillna(0)

In [9]: results
Out[9]:
A  B  C  D  E  F
1  0  0  0  0  0  0
2  0  0  0  0  0  0


I pre-filled the dataframe with 0 values – you could use 'N'.

Now it is a simple matter of checking to see if each possible combination appears or not and filling the coresponding cell in the results dataframe with your desired value (I use a 1 – you could make it 'Y')

In[10]: for i in list(df1.itertuples()):
if (i.Id, i.CategoryId) in possibilities:
results.loc[i.Id, i.CategoryId] = 1


Check the output:

In [11]: results
Out[11]:
A  B  C  D  E  F
1  1  1  0  0  0  0
2  1  0  0  0  1  1


That's a bingo!

• thank you so much! this is exactly what I was looking for. Worked perfectly Commented Sep 11, 2018 at 17:22

If you have a dataframe df1 with two columns Id and CategoryId you can chain get_dummies and groupby, e.g.

>>> df2 = df1['CategoryId'].str.get_dummies().groupby(df1['Id']).max()
>>> df2
A  B  E  F
Id
1   1  1  0  0
2   1  0  1  1


It's not quite the format you wanted but it avoids the lookup.

• After this one can replace all 1 with Y and all 0 with N using df.applymap. Commented Sep 11, 2018 at 5:02