Merging a Table With Itself, Week 7

Sometimes the lookup you need is in the same table. An employee list stores each person's manager as an id, and that id points at another row of the same list. Finding the manager's name means merging the table with itself. The same move finds pairs of rows that share something, which is how a lot of controls tests start. This page works on a seven-person org chart.

The org chart

emp_id	name	dept	manager_id
101	Rosa	Finance	(none)
102	Ken	Finance	101
103	Liz	Finance	101
104	Omar	AP	101
105	Tess	AP	104
106	Vic	AP	104
107	Wes	AP	104

Two copies of one table. In a self-merge both sides have the same column names, so pandas adds suffixes to tell them apart. Choose suffixes that say which side is which, like _mgr, or you will be reading name_x and name_y and guessing.

The notebook

Run the cells top to bottom. Each one uses names from the cells above it, so skipping one gives a NameError further down. Edit any cell and run it again.

Check yourself

Two self-merge functions. Inside these boxes emp is the org chart above, and the tests also run them on a different chart.