Write a program to create a dataframe that stores the merged dataframes emp and dept

Q. Consider the dataframes emp and dept. Write a program to create a dataframe that stores the merged dataframes emp and dept, joined on the basis of dept_id and conatin all the rows of both the dataframes.

DataFrame emp :-

    emp_id emp_name   hire date  dept_id
0       1     Rina  2001-05-01      4.0
1       2     Mina  2002-07-15      1.0
2       3     Tina  2005-10-18      5.0
3       4     Dina  2007-01-03      3.0
4       5     Nina  2008-06-24      NaN

DataFrame dept:-

    dept_id         dept_name
0        1    Administration
1        2  Customer Service
2        3           Finance
3        4   Human Resources
4        5             Sales

Answer :-

Since outer join contains all the rows from both the data-frames, we shall merge using outer join.

import pandas as pd

y = [ {'emp_id': 1, 'emp_name': 'Rina', 'hire date': '2001-05-01', 'dept_id': 4.0},
      {'emp_id': 2, 'emp_name': 'Mina', 'hire date': '2002-07-15', 'dept_id': 1.0},
      {'emp_id': 3, 'emp_name': 'Tina', 'hire date': '2005-10-18', 'dept_id': 5.0},
      {'emp_id': 4, 'emp_name': 'Dina', 'hire date': '2007-01-03', 'dept_id': 3.0},
      {'emp_id': 5, 'emp_name': 'Nina', 'hire date': '2008-06-24', 'dept_id': np.NaN}]

x = [ {'dept_id': 1, 'dept_name': 'Administration'},
      {'dept_id': 2, 'dept_name': 'Customer Service'},
      {'dept_id': 3, 'dept_name': 'Finance'},
      {'dept_id': 4, 'dept_name': 'Human Resources'},
      {'dept_id': 5, 'dept_name': 'Sales'}]

emp = pd.DataFrame (x)
dept = pd.DataFrame (y)

mdf = pd.merge (emp, dept, on = 'dept_id', how = 'outer')
print("Merged dataframe is :-\n")
print (mdf)

Output :-

Merged dataframe is :-

   dept_id         dept_name  emp_id emp_name   hire date
0      1.0    Administration     2.0     Mina  2002-07-15
1      2.0  Customer Service     NaN      NaN         NaN
2      3.0           Finance     4.0     Dina  2007-01-03
3      4.0   Human Resources     1.0     Rina  2001-05-01
4      5.0             Sales     3.0     Tina  2005-10-18
5      NaN               NaN     5.0     Nina  2008-06-24
>>>