<jupyter_start><jupyter_text>Data NormalizationLet's take a look at few rows from some visits to a doctor in the `tidy/doctor_visits.csv` file.<jupyter_code>import pandas as pd
import numpy as np
dv = pd.read_csv('../data/tidy/doctor_visits.csv')
dv<jupyter_output><empty_output><jupyter_text>The information is easy to readAll the information presented in this table is easy to read. Any question that is asked about the visits can be quickly answered. Yet, something is wrong, what is it?Although the table of data is sufficient to address our questions, it repeats much of the data and will not scale well as more patients come into the system. It will also be difficult to update historical data, such as if the patient's address changes or a clinic changes names. A short intro into data normalizationModern databases attempt to reduce the amount of replication in the data by a process called **normalization**. It involves separating data into tables to minimize replication and increase data accuracy.From [wikipedia](https://en.wikipedia.org/wiki/Database_normalization):> Database Normalization, or simply normalization, is the process of organizing the columns (attributes) and tables (relations) of a relational database to reduce data redundancy and improve data integrity. Normalization is also the process of simplifying the design of a database so that it achieves the optimum structure. It reduces and eliminates redundant data. In normalization, data integrity is assured. It was first proposed by Dr. <NAME>, as an integral part of a relational model.There is much, much more to data normalization. The following example is just a brief overview.  Which values are always the same for each visit?When looking through the table, you should notice that certain values will repeat for every single visit. For instance, the patient name, address, and birth date are going to be the same. There isn't a need to keep repeating all these values for every visit. Separating patient values into a distinct tableLet's select all the patient columns in a single DataFrame. The **`copy`** method is called to ensure that this is new DataFrame is a completely different DataFrame and not referring to the same data as the original.<jupyter_code>patient = dv[['patient_name', 'patient_address', 'patient_birthdate']]
patient<jupyter_output><empty_output><jupyter_text>Drop duplicate rowsThere is no need to store duplicate values of each row. Let's keep only the unique rows.<jupyter_code>patient = patient.drop_duplicates()
patient<jupyter_output><empty_output><jupyter_text>Create a primary key to uniquely identify each rowWhen we normalize our data, and separate it into new tables, an additional column is added to the table to uniquely identify each row. The unique value that identifies each row is called a **primary key**. Let's add a primary key to the patient table. Note: If you get the **`SettingWithCopyWarning`**, ignore it.<jupyter_code>patient['patient_id'] = np.arange(len(patient))
patient<jupyter_output><empty_output><jupyter_text>Rearrange the columns so that `patient_id` is firstIt's best to always put the primary key as the first column.<jupyter_code>cols = ['patient_id', 'patient_name', 'patient_address', 'patient_birthdate']
patient = patient[cols]
patient<jupyter_output><empty_output><jupyter_text>We just created a dimensionIn database terminology, the patient table would be considered a **dimension**. Dimensions are things that exist in your data that are independent of any event taking place. They tend to be static and do not change often (such as your name or birth date. Create tables for all the other dimensionsThere are several other dimensions in our original table. Let's create new dimension tables for each one of the following:* clinic* doctor* procedureWe will select each of the columns unique to each dimension, drop the replicated rows and add a primary key as the first column to uniquely identify each row.  Clinic Dimension<jupyter_code>cols = ['clinic_name', 'clinic_address']
clinic = dv[cols].drop_duplicates()
clinic
clinic['clinic_id'] = np.arange(len(clinic))
clinic
cols = ['clinic_id', 'clinic_name', 'clinic_address']
clinic = clinic[cols]
clinic.head()<jupyter_output><empty_output><jupyter_text>Doctor Dimension<jupyter_code>cols = ['doctor_name', 'doctor_specialty']
doctor = dv[cols].drop_duplicates()
doctor
doctor['doctor_id'] = np.arange(len(doctor))
doctor
cols = ['doctor_id', 'doctor_name', 'doctor_specialty']
doctor = doctor[cols]
doctor.head()<jupyter_output><empty_output><jupyter_text>Procedure DimensionHere, the primary key is already given to us with the procedure code. We will keep it as is.<jupyter_code>cols = ['procedure_code', 'procedure_name']
procedure = dv[cols].drop_duplicates()
procedure<jupyter_output><empty_output><jupyter_text>Replacing original data with primary keysWe can now revisit our original DataFrame and replace all columns in each dimension with a single column, the primary key of that dimension. Join original table to dimension tablesTo make the replacement, we will join our original table to each one of our dimension tables. The **`merge`** method joins tables together in Pandas. We can specify how the tables will join with the **`on`** parameter. We will join on all the non-primary key columns. Below, we join the **`patient`** table. The result is one extra column at the end of the DataFrame, the **`patient_id`**.<jupyter_code>dv_fact = dv.merge(patient, on=['patient_name', 'patient_address', 'patient_birthdate'])
dv_fact<jupyter_output><empty_output><jupyter_text>Drop the dimension columnsWe can now drop all the original patient columns as the **`patient_id`** now refers to them.<jupyter_code>dv_fact = dv_fact.drop(columns=['patient_name', 'patient_address', 'patient_birthdate'])
dv_fact<jupyter_output><empty_output><jupyter_text>Replace all the other dimensions with primary key columns  Doctor Dimension<jupyter_code>dv_fact = dv_fact.merge(doctor, on=['doctor_name', 'doctor_specialty'])
dv_fact = dv_fact.drop(columns=['doctor_name', 'doctor_specialty'])
dv_fact
clinic<jupyter_output><empty_output><jupyter_text>Clinic Dimension<jupyter_code>dv_fact = dv_fact.merge(clinic, on=['clinic_name', 'clinic_address'])
dv_fact = dv_fact.drop(columns=['clinic_name', 'clinic_address'])
dv_fact<jupyter_output><empty_output><jupyter_text>Procedure DimensionSince the primary key is already in the table, we can just drop the **`procedure_name`** column<jupyter_code>dv_fact = dv_fact.drop(columns=['procedure_name'])
dv_fact<jupyter_output><empty_output><jupyter_text>Rearrange columns with foreign keys firstWhen a primary key from table is found in another table, it is called a **foreign key**. Foreign keys can repeat in the table they are in. Primary keys, however, can never repeat in the tables they are in. This is a very important property. Foreign keys are<jupyter_code>cols = ['patient_id', 'clinic_id', 'doctor_id', 
        'procedure_code', 'visit_date', 'cost']
dv_fact = dv_fact[cols]
dv_fact<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07891387356809504, 'fraction_numerical': 0.0, 'mean_word_length': 6.428571428571429, 'pattern_counts': {'":': 0, '<': 58, '<?xml version=': 0, '>': 59, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '85397', 'license': 'MIT', 'n_tokens_mistral': 2011, 'n_tokens_neox': 1907, 'n_words': 835, 'path': 'jupyter_notebooks/pandas/mastering_data_analysis/10. Joining Data/04. Data Normalization.ipynb', 'repo_name': 'manual123/Nacho-Jupyter-Notebooks', 'size': '7078'}