Filling pandas dataframe with foreign key

Question

I have 3 tables: master, summary, controller.

master contains the columns: asset_id(unique), controller_name

controller contains the columns: controller_id which is a primary key generated based on unique controller_names

summary contains the columns: asset_id(primary key) and controller_id(empty)

I need to find a way to fill the controller_id table based on it's asset id.

For Example:

If the Master table looked like this:

Master

This would be the corresponding Controller table:

Controller

This is what I want the Summary table to look like

Summary

Thanks in advance for any help!

Hey Buddy, welcome to SO, can you create a viable example for us? please add in an example subset of your tables (in text not pictures) and your expected outcome, along with what you've tried. — Umar.H, May 22 '19 at 15:20
Possible duplicate of [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) merge on `controller_id` — anky, May 22 '19 at 15:22

score 1 · Answer 1 · answered May 22 '19 at 15:25

1

You can use pandas merge and select the columns of interest

summary = master.merge(
    controller,
    on='controller_name',
    how='left'
)[['asset_id','controller_id']]

answered May 22 '19 at 15:25

iamchoosinganame

1 Answers1