I'm trying to find the same number of occurrences in both data frames
This is a follow-up question for my previous question
I got 2 data frames
df1=pd.DataFrame([[1,None],[1,None,],[1,None],[1,'item_a'],[2,'item_a'],[2,'item_b'],[2,'item_f'],[3,'item_e'],[3,'item_e'],[3,'item_g'],[3,'item_h']],columns=['id','A'])
df2=pd.DataFrame([[1,'item_a'],[1,'item_b'],[1,'item_c'],[1,'item_d'],[2,'item_a'],[2,'item_b'],[2,'item_c'],[2,'item_d'],[3,'item_e'],[3,'item_f'],[3,'item_g'],[3,'item_h']],columns=['id','A'])
df1
id A
0 1 None
1 1 None
2 1 None
3 1 item_a # id 1 has 1 occurrences in total in df1
4 2 item_a
5 2 item_b
6 2 item_f #id 2 has 3 occurrences in total in df1(id 2 has 3 occurrences here)
7 3 item_e
8 3 item_e
9 3 item_g
10 3 item_h #id3 has 4 ccurrences in total in df1
df2
id A
0 1 item_a
1 1 item_b
2 1 item_c
3 1 item_d
4 2 item_a
5 2 item_b
6 2 item_c
7 2 item_d
8 3 item_e
9 3 item_f
10 3 item_g
11 3 item_h
I got an answer on how to find similarities by using
previous result:
d=pd.merge(df1,df2,how='inner')
id A
3 1 item_a # id 1 has 1 occurrences in total in d
4 2 item_a
5 2 item_b # id 2 has 2 occurrences in total in d(id 2 has 2 occurrences here which does not match all the occurrences(3) in df1)
7 3 item_e
8 3 item_e
9 3 item_g
10 3 item_h #id 3 has 4 occurrences in total in d
What I've tried to find same number of occurrences in both data frames:
d[d['id'].value_counts()==df1['id'].value_counts()]
Which gave me an error:Can only compare identically-labeled Series objects
I've also tried different things using rename to put a column name for value_counts and merge them but failed.
Match: Count of occurrences in df1 for an id match count of occurrences in result data frame d
cnt_in_df1|cntin_d
for id1: 1 | 1 count #match => id 1 should be in the desired output.
for id2: 3 | 2 count #mismatch=> id 2 should not be in the desired output
for id3: 4 | 4 count #match => id 3 should be in the desired output.
My desired output for this question:
id count
0 1 1
1 3 4