1
$\begingroup$

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

$\endgroup$
  • $\begingroup$ How big are your two tables? What is the result of df1.shape and df2.shape? $\endgroup$ – n1k31t4 Sep 10 '18 at 21:50
0
$\begingroup$

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!

| improve this answer | |
$\endgroup$
  • $\begingroup$ thank you so much! this is exactly what I was looking for. Worked perfectly $\endgroup$ – TigSh Sep 11 '18 at 17:22
  • $\begingroup$ @PSnh - you're very welcome! Please consider voting up the answer or marking ione as the accepted answer! $\endgroup$ – n1k31t4 Sep 11 '18 at 19:57
2
$\begingroup$

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.

| improve this answer | |
$\endgroup$
  • $\begingroup$ After this one can replace all 1 with Y and all 0 with N using df.applymap. $\endgroup$ – Ankit Seth Sep 11 '18 at 5:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.