LOOKUP using 2 dataframes in Python

Question

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

Answer 1

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!

Answer 2

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.