Pandas Merge Multiple Columns

Question

I am struggling to merge two pandas dataframes to replicate a vlookup function using two columns as lookup value.

The first dataframe df has 6 columns including three columns: perf, ticker and date. The perf column is empty and this is the one I would like to see populated. The second dataframe u includes the same three columns, including values in the perf column but only for a specific date.

I have tried this: df=pd.merge(df,u,how='left',on=['ticker_and_exch_code', 'date'])

But the result I get is a dataframe with new perf columns instead of populating the one existing perf column. Would really appreciate insights into what I am missing, thanks!

Vincent

Did you assign the new column to your existing dataframe? df["perf"] = pd.merge(df,u,how='left',on=['ticker_and_exch_code', 'date'])["perf"]? — Andre S.
– Andre S., Commented Nov 10, 2020 at 14:04
Tried that but I get an error, Key Error 'perf', indexer = self.columns.get_loc(key) — Vinny B
– Vinny B, Commented Nov 10, 2020 at 14:23

Clément Moliné · Accepted Answer · 2020-11-10 14:04:39Z

1

If the 'perf' column is empty in the first DataFrame, may I suggest removing it before merging the two DataFrames?

df=pd.merge(
    df.drop(columns='perf'),
    u,
    how='left',
    on=['ticker_and_exch_code', 'date'],
)

answered Nov 10, 2020 at 14:04

Clément Moliné

835 bronze badges

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

3 Comments

Vinny B Over a year ago

Tried but this does not solve the issue. The merge creates new columns perf_x and perf_y every time the merge operation is performed.

yulGM Over a year ago

Can you double check before running this that you don't have any other columns stored in df? the code above should work if at running time df has only one 'perf' column. do print(df.columns) just before merge to check. if you working in jupyter notebooks it's possible that your df already stored some 'perf_x' or 'perf_y' columns from previous runs.

Clément Moliné Over a year ago

Are you perhaps conducting the same merging operation multiple times in a row? therefore re-creating the 'perf' column before the next merge? If so, we will need a more advanced solution.

Collectives™ on Stack Overflow

Pandas Merge Multiple Columns

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related