How Can I choose count the data in one column based on other column?

Question

I have two dataframe as below:

df1 = DataFrame({'a': np.random.randint(10, size=2)})
df2 = DataFrame({'a': np.random.randint(10, size=100)})

There is two numbers in df1, and I want to count the two numbers' amount in df2. The answer is in the right of df1['a'].

I use for in, but there is a error:Length of values does not match length of ' 'index.

Anyone can tell me how to slove this question?

I use df2['a'].isin(df1['a']).sum(), but it give me the result that the amount of two numbers together.

I want the result like:

No  Amount
8   3
1   2

instead of :

No  Amount
8   5
1   5

score 2 · Accepted Answer · answered Nov 22 '17 at 00:04

2

df2.a.value_counts().reindex(df1.a)
Out[369]: 
a
4    11
5     5
Name: a, dtype: int64

Add sum

df2.a.value_counts().reindex(df1.a).sum()
Out[370]: 16

answered Nov 22 '17 at 00:04

BENY

Thanks for your answer. but there still has a problem, the two amount is same, one is correct one is wrong – yuchen huang Nov 22 '17 at 00:27
@yuchenhuang check the first line of my code.. – BENY Nov 22 '17 at 01:16
it perfectly solves my problem, REALLY REALLY thanks for your time!!!!!! – yuchen huang Nov 22 '17 at 01:53
@yuchenhuang if that is the case , can you accept it? – BENY Nov 22 '17 at 01:59
I am trying to used in my real case, but as least I know the function, I think I can solve this question finally. Thanks and thanks, this question just spend my whole afternoon time ! – yuchen huang Nov 22 '17 at 02:01
@yuchenhuang yw you can click the check mark – BENY Nov 22 '17 at 02:02

score 1 · Answer 2 · answered Nov 22 '17 at 00:04

1

If I understand correctly:

In [22]: df2['a'].isin(df1['a']).sum()
Out[22]: 18

answered Nov 22 '17 at 00:04

Thanks for your answer. I use your method, but the result is not correct. The problem is that two amount is same, one is correct but another is not correct and the number is same. – yuchen huang Nov 22 '17 at 00:25

2 Answers2