0
$\begingroup$
import pandas as pd

df1 = pd.DataFrame({'Name': ['Juan','Marti', 'Rober'], 'Car': ['yes','no', 'yes'], 'Number': [1, 2, 3]})
df2 = pd.DataFrame({'Name': ['Victor','Marti', 'Robert'], 'Car': ['yes','yes', 'no'], 'Number': [3, 4, 5]})

print df1
print df2



   Car   Name    Number
0  yes   Juan       1
1   no  Marti       2
2  yes  Rober       3

   Car    Name  Number
0  yes  Victor       3
1  yes   Marti       4
2   no  Pepe       5

I need compare dataframe 1 with 2.

for i in df1:
  for u in df2:
    if i['Name'] ==u['Name']:
      i['Car'] == u['Car']
print df2

The result final is:

    Car    Name  Number
0  yes  Victor       3
1  no   Marti       4
2   no  Pepe       5

But being fit with a large dataframe is inefficient. How could I do it?

Cheers

$\endgroup$
  • $\begingroup$ Hi Doctor. Could you maybe describe more verbose, how exactly you want to compare your dataframes? I can't imagine, that you want to compare each row of df1 with each row of df2 no matter if you found a match before or not. What exactly should the line i['Car'] == u['Car'] do? Like it is written, it would do a comparison producing a boolean as result, which dissapears in nirvana right after beeing produced, because notihing is done with it. $\endgroup$ – jottbe Sep 21 '19 at 20:10
  • $\begingroup$ @jottbe I want to compare the first column of df1 with df2, and if it is the same, copy the second column of df1 to df2. $\endgroup$ – DoctorPython Sep 21 '19 at 20:26
  • $\begingroup$ Hi Doctor, that is easy. You don't even need merge for that. $\endgroup$ – jottbe Sep 22 '19 at 9:52
0
$\begingroup$

It seems to me what you need to do is merge the two dataframes such that the Car column from df2 should only be copied if the Name columns from df1 and df2 are the same:

# One liner
result = pd.merge(df1.loc[:, ['Name']], df2.loc[:, ['Name', 'Car']], how='left', on='Name')

More info here: https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html#database-style-dataframe-or-named-series-joining-merging

What the code shows is merge the right dataframe (df2) onto the left dataframe (df1), where the column Name is the same (on='Name') on both.

As we're interested in only 'copying' the Car column across then df2 has been filtered with the columns of interest (`df2.loc[:, ['Name', 'Car']]').

| improve this answer | |
$\endgroup$
0
$\begingroup$

If you just need to match one column, you can use map. Only if you need to take several columns from the second dataframe, you should switch to merge or join.

import pandas as pd

df1 = pd.DataFrame({'Name': ['Juan','Marti', 'Rober'], 'Car': ['yes','no', 'yes'], 'Number': [1, 2, 3]})
df2 = pd.DataFrame({'Name': ['Victor','Marti', 'Robert'], 'Car': ['yes','yes', 'no'], 'Number': [3, 4, 5]})

df2_map= df2[['Name', 'Car']].set_index(['Name'])
df1['Car2']= df1['Name'].map(df2_map['Car'])

# if you want to take the value of df1 if df2 does not contain a match, you can 
# alternatively do it like this (of course, you could also plug the result back
# to column Car directly instead of Car3:    
ser_car_map= df1['Name'].map(df2_map['Car'])
df1['Car3']= df1['Name'].map(df2_map['Car']).where(~df1['Name'].map(df2_map['Car']).isna(), df1['Car'])

The result is:

    Name  Car  Number Car2 Car3
0   Juan  yes       1  NaN  yes
1  Marti   no       2  yes  yes
2  Rober  yes       3  NaN  yes

If you rather want to merge more infos. E.g. if you also want the number to be updated, you can do it as above a second time for the second column, or you use merge and where in combination. That would look like this:

# merge the dataframes fully assigning suffix _df2 to the
# columns merged from df2
result = pd.merge(df1, df2, how='left', on='Name', suffixes=['', '_df2'])
columns_to_match= ['Car', 'Number']
columns_df2= [f'{col}_df2' for col in columns_to_match]
# loop over the column pairs to consolidate the content
for col, col2 in zip(columns_to_match, columns_df2):
    ser_col2= result[col2]
    result[col]= ser_col2.where(~ser_col2.isna(), result[col])
# remove the columns merged from df2
result.drop(columns_df2, axis='columns', inplace=True)

You only have to make sure, df2 contains each Name only once, otherwise the number of result rows will be larger than the number of rows in your source dataframe of course. With the match method this doesn't happen.

| improve this answer | |
$\endgroup$
0
$\begingroup$
idx = df1[df1['Name'] == df2['Name']].index
df1.loc[idx, 'Car'] = df2.loc[idx, 'Car']
df1

    Car Name    Number
0   yes Juan    1
1   yes Marti   2
2   yes Rober   3
| improve this answer | |
$\endgroup$

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.