<jupyter_start><jupyter_text>Practicing with pandas - 2 Merging, Joining and Null Values  Prepare data<jupyter_code>import pandas as pd
#Create dataframes
#Creating dataframes for employees, departments, and branch offices
# used a random name generator for employees names - current employees, no missing values in this dataframe
currentemployees=pd.DataFrame({'employeeId': ['e1', 'e2', 'e3', 'e4', 'e5', 'e6', 'e7', 'e8', 'e9', 'e10', 'e11', 'e12', 'e13', 'e14', 'e15', 'e16', 'e17', 'e18', 'e19', 'e20', 'e21', 'e22', 'e23', 'e24', 'e25', 'e26', 'e27', 'e28', 'e29', 'e30'], \
                        'name': ['<NAME>', '<NAME>', '<NAME>', '<NAME>',\
                                 '<NAME>', '<NAME>', '<NAME>', '<NAME>', \
                                 '<NAME>', '<NAME>', '<NAME>', \
                                 '<NAME>', '<NAME>', '<NAME>', '<NAME>', \
                                 '<NAME>', '<NAME>', '<NAME>', '<NAME>', \
                                 '<NAME>', '<NAME>', '<NAME>', '<NAME>', '<NAME>', \
                                 '<NAME>', '<NAME>', '<NAME>', '<NAME>', '<NAME>', \
                                 '<NAME>'], \
                        'departmentId': ['d1', 'd2', 'd3', 'd4', 'd5', 'd1', 'd2', 'd3','d4', 'd5', 'd1', 'd2', 'd3', 'd4', 'd5', 'd1', 'd2', 'd3', 'd4', 'd5', 'd1', 'd2', 'd3', 'd4', 'd5', 'd1', 'd2', 'd3', 'd4', 'd5'], \
                        'branchId':['b1', 'b2', 'b3', 'b1', 'b2', 'b3', 'b1', 'b2', 'b3', 'b1', 'b2', 'b3', 'b1', 'b2', 'b3', 'b1', 'b2', 'b3', 'b1', 'b2', 'b3', 'b1', 'b2', 'b3', 'b1', 'b2', 'b3', 'b1', 'b2', 'b3']})
departments=pd.DataFrame({'departmentId':['d1', 'd2', 'd3', 'd4', 'd5', 'd6'], 'departmentName':['IT', 'Sales', 'HR', 'Customer Support', 'Finance', 'Manufacturing']})
branchoffices=pd.DataFrame({'branchId':['b1', 'b2', 'b3', 'b4'], 'branchName':['Downtown', 'Uptown', 'Midtown', 'Middle of Nowhere']})

#Check the content of the currentemployees data frame
currentemployees
#Add retired and/or newly hired employees (= Not currently assigned to any departments or branch offices) to the otheremployees dataframe
otheremployees=pd.DataFrame({'employeeId': ['e31', 'e32', 'e33'], 'name': ['<NAME>', 'New Hire', 'Missing Employee']})
#construct the full employees dataframe
#concatenate row with different columns
employees = pd.concat([currentemployees, otheremployees], ignore_index=True, sort=False)
#Check resulting full employees dataframe
employees
#Check content of the departments dataframe
departments
#Display the content of the branchoffices dataframe
branchoffices<jupyter_output><empty_output><jupyter_text>Join and Merge<jupyter_code>#Left join - employees and departments tables
pd.merge(employees, departments, on='departmentId', how='left')<jupyter_output><empty_output><jupyter_text>The above output includes all records from the employees table (including past/newly hired unassigned employees), but only those departments that have employees assigned to them (e.g., there is no manufacturing department on the list).<jupyter_code>#Right join - employees and branchoffices table
pd.merge(employees, branchoffices, on='branchId', how='right')<jupyter_output><empty_output><jupyter_text>The output above includes all branch offices from the branchoffices table (including branch with the Id 'b4' -"Middle of Nowhere" that does not have any employees assigned to it), but only current employees (e.g. employees without a branch assignment are excluded).<jupyter_code>#Outer join
pd.merge(employees, departments, on='departmentId', how='outer')<jupyter_output><empty_output><jupyter_text>The outer join produced the output that includes all records from the employees table and all records from the departments table (including unassigned employees and the Manufacturing department that currently does not have any employees in it).<jupyter_code>#Inner join
pd.merge(employees, departments, how='inner')<jupyter_output><empty_output><jupyter_text>The inner join resulted in a set of records that have matches in both tables (all current employees assigned to departments and only departments that have employees).<jupyter_code>#Show a list of current employees, their corresponding departments and  branch offices
pd.merge(pd.merge(employees, departments, how='inner'), branchoffices, how='inner')<jupyter_output><empty_output><jupyter_text>The above output shows all current employees and their corresponding departments and branch offices.<jupyter_code>#Alternative solution with additional sort by department, then by branch
employees.merge(departments, how='inner').merge(branchoffices, how='inner').sort_values(by=['departmentId', 'branchId'])
#Display full information only for employees assigned to a particular department (e.g., IT) using a boolean variable
deptIt= employees['departmentId'] == 'd1'
employees[deptIt].merge(departments, how='inner').merge(branchoffices, how='inner').sort_values(by=['departmentId', 'branchId'])<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16783778357759485, 'fraction_numerical': 0.026099176872114032, 'mean_word_length': 4.813302217036172, 'pattern_counts': {'":': 0, '<': 60, '<?xml version=': 0, '>': 60, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 7, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '341254', 'license': 'MIT', 'n_tokens_mistral': 1602, 'n_tokens_neox': 1572, 'n_words': 489, 'path': 'Data_Collection_and_Preparation_Examples/MSDS_Practicing_with_pandas_2.ipynb', 'repo_name': 'nweakly/MSDS_class_projects_python', 'size': '5218'}