Pandas - Vlookup discrepancy when compared to excel

user_18950507 0
Viewed 23

Good Afternoon,

I want to do a vlookup between 2 dataframes so that they can be pivoted for a later task. The formula for this in excel is simple enough and is reliable.

=VLOOKUP(F2,$CU$2:$CV$2501,2,FALSE)

but when attempted with python this seems to cause an error somewhere and generates several hundred more records than should be expected.

What I am attempting to do is look at the marketing_event_id to see if it appears in the Details sheet and if it matches return the SrcCode in a new column.

rmNulls = pd.read_csv (r"File1.csv", converters={'marketing_event_id': lambda x: str(x)})
rmNulls['marketing_event_id'] = rmNulls['marketing_event_id'].replace('',0, inplace = True)
rmNulls["marketing_event_id"] = rmNulls["marketing_event_id"].astype(str)


details = pd.read_excel(r"DetailAttr.xlsx", "Details", dtype=str)

#Isolating the 2 required cols
refDetails = details[['SrcCode','New Minor Cat']].copy()
#Recasting code as str for merge
refDetails ["SrcCode"] = refDetails ["SrcCode"].astype(str)
print("Vlookup in Progress")
df3 = pd.merge(rmNulls,
         refDetails ,
         left_on= "marketing_event_id",
         right_on ='SrcCode',
         how ='left')

with pd.ExcelWriter('DW 150622.xlsx', date_format='DD-MM-YYYY', datetime_format='DD-MM-YYYY' ) as writer:
    df3.to_excel(writer)

I use another column called date_value to filter the pivot table, with the pivot done in excel it ends up with 381, whereas pandas returns 475. I feel like this is related to the way my merge function is formatted but even after playing with it this still throws up a larger number than expected.

I have stepped through the code as I manipulate the df rmNulls and just before the merge I can in excel pivot it to give the correct numbers, so I do not believe that the date fields are to blame at this point.

Have I missed a trick with this or is there something just fundementally wrong with the way I'm attempting this.

Any help would be greatly appreciated.

Regards, Ryan

pandas python
2 Answers

Do you have multiple IDs repeated in one or both of your DataFrames? merge is equivalent to a JOIN in SQL. Instead, VLOOKUP retrieves the value after the first value found.

|Foo|Bar|    |Foo|Baz|
|---+---|    |---+---|
| 1 | a |    | 1 | x |
| 2 | b |    | 1 | y |

i.e if to the left of the first table you add a 3rd column with a VLOOKUP to find Baz you'll end up having 2 rows.

|Foo|Bar|Baz|
|---+---+---|
| 1 | a | x |
| 2 | b |   |

If, instead you do pd.merge(left=FooBar, right=FooBaz, how='left', on='Foo') your result is

|Foo|Bar|Baz|
|---+---+---|
| 1 | a | x |
| 1 | a | y |
| 2 | b |   |
user_170196510

After some trail and error I ran the following lines after import the two datasets into df:

rmNulls = pd.read_csv (r"File1.csv", dtype=str)
details= pd.read_excel(r"File2.xlsx", "Details", dtype=str)
refinedD = details[["SrcCode","New Minor Cat"]]
new_df = refinedD.drop_duplicates(subset='SrcCode')
df3 = pd.merge(rmNulls,new_df,left_on= "marketing_event_id",right_on ='SrcCode',how ='left', validate='m:1').drop_duplicates()

It appears that for some reason duplicates were present in the df that was supposed to be unique and would appear so when looking through it.

This has been a rather good lesson to assume that data which you have been told meets certain standards should still be thoroughly checked to prove this to be true regardless, if you start encountering errors.

Thank you for helping point me in the right direction.

Regards, Ryan

user_189505070
Related