Pandas lookup, mapping one column in a dataframe to another in a different dataframe

python database pandas lookup
wolfsatthedoor picture wolfsatthedoor · Sep 1, 2014 · Viewed 11.7k times · Source

I have two pandas dataframes: df1 and df2.

df1 has columns X and Y and weeknum. df2 has columns Z, weeknum, and datetime.

I want to basically keep df1 and have an extra column in it that is corresponding datetime for weeknum.

I can use merge but there must be a cleaner way, without having to drop column Z.

Answer

Mike picture Mike · Sep 1, 2014

You can grab the columns you want in the merge syntax

df1 = df1.merge(df2[['weeknum', 'datetime']], on=['weeknum'])

This will make sure you don't have any unwanted columns of df2 in your result, but you don't have to delete those columns from your second DataFrame in the process.

Related questions

ValueError: Must pass DataFrame with boolean values only

Question In this datafile, the United States is broken up into four regions using the "REGION" column. Create a query that finds the counties that belong to regions 1 or 2, whose name starts with 'Washington', and whose POPESTIMATE2015 was greater than …

Read More
how to transform pandas dataframe for insertion via executemany() statement?

I have a fairly big pandas dataframe - 50 or so headers and a few hundred thousand rows of data - and I'm looking to transfer this data to a database using the ceODBC module. Previously I was using pyodbc and …

Read More
Add a sequence number to each element in a group using python

I have a dataframe of individuals who each have multiple records. I want to enumerate the record in the sequence for each individual in python. Essentially I would like to create the 'sequence' column in the following table: patient date …

Read More