CODEWITHSUNDEEP
pythonpandasjoinmerge
Varun
Varun

Reputation: 135

vlookup on text field using pandas

I need to use vlookup functionality in pandas.

DataFrame 2: (FEED_NAME has no duplicate rows)

+-----------+--------------------+---------------------+
| FEED_NAME |        Name        |       Source        |
+-----------+--------------------+---------------------+
| DMSN      | DMSN_YYYYMMDD.txt  | Main hub            |
| PCSUS     | PCSUS_YYYYMMDD.txt | Basement            |
| DAMJ      | DAMJ_YYYYMMDD.txt  | Effiel Tower router |
+-----------+--------------------+---------------------+

DataFrame 1:

+-------------+
| SYSTEM_NAME |
+-------------+
| DMSN        |
| PCSUS       |
| DAMJ        |
| :           |
| :           |
+-------------+

DataFrame 1 contains lot more number of rows. It is acutally a column in much larger table. I need to merger df1 with df2 to make it look like:

+-------------+--------------------+---------------------+
| SYSTEM_NAME |        Name        |       Source        |
+-------------+--------------------+---------------------+
| DMSN        | DMSN_YYYYMMDD.txt  | Main Hub            |
| PCSUS       | PCSUS_YYYYMMDD.txt | Basement            |
| DAMJ        | DAMJ_YYYYMMDD.txt  | Eiffel Tower router |
| :           |                    |                     |
| :           |                    |                     |
+-------------+--------------------+---------------------+

in excel I just would have done VLOOKUP(,,1,TRUE) and then copied the same across all cells.

I have tried various combinations with merge and join but I keep getting KeyError:'SYSTEM_NAME'

Code:

_df1 = df1[["SYSTEM_NAME"]]
_df2 = df2[['FEED_NAME','Name','Source']]
_df2.rename(columns = {'FEED_NAME':"SYSTEM_NAME"})
_df3 = pd.merge(_df1,_df2,how='left',on='SYSTEM_NAME')
_df3.head()

Upvotes: 2

Views: 191

Answers (1)

Simon Delecourt
Simon Delecourt

Reputation: 1599

You missed the inplace=True argument in the line _df2.rename(columns = {'FEED_NAME':"SYSTEM_NAME"}) so the _df2 columns name haven't changed. Try this instead :

_df1 = df1[["SYSTEM_NAME"]]
_df2 = df2[['FEED_NAME','Name','Source']]
_df2.rename(columns = {'FEED_NAME':"SYSTEM_NAME"}, inplace=True)
_df3 = pd.merge(_df1,_df2,how='left',on='SYSTEM_NAME')
_df3.head()

Upvotes: 2

Related Questions