python pandas: Check if dataframe's column value is in another dataframe's column, then count and list it

Question

Learning Python here, and any help on this is much appreciated. I have a two-part problem, and although I have created a solution to the first part, there has to be a much more pythonic way to accomplish the goal. The second part, not so sure how to proceed.

I have columns of unique IDs in two separate dataframes. I want to count the number of times that a uid in df_2 uid column occurs in df_1's uid column, and then add that uid to a list if it is both. The following code example works for me, but i am worried there is a problem somewhere, and there has to be much better way.

data = {'uid':['uid1', 'uid2', 'uid3', 'uid4'], 'value': [1, 2, 3, 4]}
df = pd.DataFrame(data)

data1 = {'uid':['uid4', 'uid2', 'uid5'], 'value1': ["", 5, 6]}
df1 = pd.DataFrame(data1)

count_val_in_both_df = 0
list_val_in_both_df = []
for x in range(len(df1.iloc[:, 0])) :
    if df1.iloc[x, 0] in df.iloc[:, 0].values :
        count_val_in_both_df += 1
        list_val_in_both_df.append(df1.iloc[x, 0])        
print('count = ' + str(count_val_in_both_df))
print(list_val_in_both_df)

Which outputs:

df
    uid  value
0  uid1      1
1  uid2      2
2  uid3      3
3  uid4      4


df1
    uid value1
0  uid4       
1  uid2      5
2  uid5      6


count = 2
['uid4', 'uid2']

The second part is creating a column in df for a values in df1 and adding the value from df1. I am pretty lost on this part of it, but want an outcome like this:

{    uid  value value1
0  uid1      1       
1  uid2      2      5
2  uid3      3       
3  uid4      4       }

Vaishali · Accepted Answer · 2017-08-30 22:06:10Z

3

You can use merge

df.merge(df1, on = 'uid', how = 'left').fillna('')

    uid value   value1
0   uid1    1   
1   uid2    2   5
2   uid3    3   
3   uid4    4

For the first part of the question, you can use intersection

list_val_in_both_df  = list(set(df.uid).intersection(set(df1.uid)))

You get

['uid2', 'uid4']

edited Aug 30, 2017 at 22:06

answered Aug 30, 2017 at 21:58

Vaishali

38.5k5 gold badges62 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

python pandas: Check if dataframe's column value is in another dataframe's column, then count and list it

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related