Compare 2 dataframes in pandas based on column value

Question

I have 2 data frames. The first one "named x" has the following format:

             ISBN  Age Category  Count
20     000649840X  Middle Adult      6
21     000649840X   Young Adult     16

Where The same ISBN have multiple rows with different categories.

The other dataframe "named y" has the one row for each ISBN value. I want to create two column in the last dataframe one for each category count where the final result should look like:

ISBN      Middle Adult Count       Young Adult Count

I tried

y["Young Adult Count"] = x[(x['Age Category'] == 'Young Adult') & (y['ISBN] == x['ISBN])]['Count']

But it did not work

So `I have 2 data frames. ` means do you have second empty DataFrame and need create second from first? I am a bit confused — jezrael, Jan 14 '18 at 08:30

score 2 · Answer 1 · answered Jan 14 '18 at 08:09

2

groupby + sum + unstack should work.

df.groupby(['ISBN', 'Age Category'])\
  .Count.sum()\
  .unstack()\
  .add_suffix(' Count')

Age Category  Middle Adult Count  Young Adult Count
ISBN
000649840X                     6                 16

If you want to explicitly filter on those two categories, then filter first, with isin -

cat = ['Middle Adult', 'Young Adult']
df = df[df['Age Category'].isin(cat)]

Now, perform the groupby.

answered Jan 14 '18 at 08:09

cs95

379,657
97
704
746

Worked perfectly. Thanks a lot – Omar Amr Jan 14 '18 at 08:23
@OmarAdel Sure, don't forget to upvote both our answers, as both are good :) – cs95 Jan 14 '18 at 08:23
1

@OmarAdel - so another DataFrame is not important? – jezrael Jan 14 '18 at 08:23
@jezrael You may have misunderstood the question, I guess. OP just had an empty dataframe they wanted to populate... heheh happens a lot. English is the biggest barrier. – cs95 Jan 14 '18 at 08:24
1

hmmm, but then it is only pivot dupe :( – jezrael Jan 14 '18 at 08:25
@jezrael Huh... now that you mention it... might be! – cs95 Jan 14 '18 at 08:27
@cᴏʟᴅsᴘᴇᴇᴅ - I answer only because 2 dataFrames and I think it is not pivot dupe, so why you answer? I dont understand, because your answer is clear dupe. – jezrael Jan 14 '18 at 08:28
@jezrael Sorry dude, sometimes I make mistakes. Anyway, I have signed it now. Nothing more to do... please don't be angry. – cs95 Jan 14 '18 at 08:30
Thanks, for you too. – jezrael Jan 14 '18 at 08:32
@OmarAdel You can only accept one answer buddy, not both. – cs95 Jan 14 '18 at 08:34
It's more preferable for my case to be in a new frame. – Omar Amr Jan 14 '18 at 08:34
@OmarAdel Whatever works for you, but I am a bit confused since you said my answer worked perfectly. – cs95 Jan 14 '18 at 08:38

jezrael · Accepted Answer · 2018-01-14T08:22:24.343

I think you need pivot_table with join (default left join, so if some ISBN missing get NaNs rows) another DataFrame:

print (df1)
         ISBN  Age Category  Count
0  000649840X  Middle Adult      6
1  000649840X   Young Adult     16


print (df2)
         ISBN
0  000649840X
1  0001111111

df3 = df1.pivot_table(index='ISBN', 
                      columns='Age Category', 
                      values='Count', 
                      aggfunc='sum')
df = df2.join(df3.add_suffix(' Count'), on='ISBN')
print (df)
         ISBN  Middle Adult Count  Young Adult Count
0  000649840X                 6.0               16.0
1  0001111111                 NaN                NaN

For inner join use parameter how='inner':

df = df2.join(df3.add_suffix(' Count'), on='ISBN', how='inner')
print (df)
         ISBN  Middle Adult Count  Young Adult Count
0  000649840X                   6                 16

Well, if OP accepted your answer then I guess it's not a dupe, so I reopened it. — cs95, Jan 14 '18 at 08:39

Compare 2 dataframes in pandas based on column value

2 Answers2