It is not always beneficial to make use of pre-existing datasets in the development of data products. To test a system under development, here are some problems that may come along:
Data pipelines could be complicated entities that need to be tested thoroughly. The steps in such pipelines are not only for machine learning purposes but are often much broader, converting massive data sources, combining them, and presenting them for machine learning activities.
The amount of data in existing data sets may not be enough.
When testing for data cleaning and preprocessing functionality, there might be a need to test for specific conditions and anomalies that might not be sufficiently represented in an existing data set.
Confidentiality of data might be a real constraint. One example of this is intelectual property (IP) that needs to remain confidential. There are also items like social security numbers, credit card numbers, medical information, and other personal information.
Existing data sets might be enhanced and adapted but this approach could be cost prohibitive. There is also the challenge to locate a starting data set to work from.
To address many of these needs one approach that comes to mind is to make up the data making use of a process called generation or synthesis. Such data is referred to as synthetic data. This approach has its own set of challenges but sometimes these are much less than searching for a good enough data set. A number of software systems exist that will generate very usable data. Such data might not be as ideal for machine learning purposes than data generated by real systems, but in the areas described above, may actually be more ideal.
In this project we will make use of the python faker package to help generate synthetic data. Not only do we want to generate ‘clean’ data, but we might choose to also introduce some anomalies and noise to make the synthetic data more realistic. This also gives us good control over the testing requirements of the system under construction.
From the faker package’s documentation site, https://faker.readthedocs.io/, we see that:
Faker is a Python package that generates fake data for you. Whether you need to bootstrap your database, create good-looking XML documents, fill-in your persistence to stress test it, or anonymize data taken from a production service, Faker is for you.
In our present context, we are going to generate synthetic data to populate a database representing the operations of a simple company that processes medical insurance claims. Figure 1 shows the UML diagram of the data model used by this company.
!pip install fakerRequirement already satisfied: faker in /usr/local/lib/python3.7/dist-packages (13.6.0)
Requirement already satisfied: typing-extensions>=3.10.0.2 in /usr/local/lib/python3.7/dist-packages (from faker) (4.2.0)
Requirement already satisfied: python-dateutil>=2.4 in /usr/local/lib/python3.7/dist-packages (from faker) (2.8.2)
Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.7/dist-packages (from python-dateutil>=2.4->faker) (1.15.0)import fakerimport pandas as pd
pd.__version__'1.3.5'import random
import datetime
import numpy as np
np.__version__'1.21.6'We will generate data in dataframes and store each dataframe to a .csv file. These files can then be used to setup tables in a SQL database. During generation/synthesis we will make use of: * faker functionality * pre-arranged data sources, e.g. list of insurance payers, and list of mappings between diseases and drugs
After each table generation/synthesis, the table will be corrupted by * introducing some missing values randomly * duplicating some rows randomly
#
# setup Faker
fake = faker.Faker()#
# corruption functionality
def corrupt_with_missing_values(df=None, sample_cols=None, missing_entry='N/A', prob=0.01):
missing_vals = 0
for index,row in df.iterrows():
if random.uniform(0, 1) < prob:
df.loc[index, random.choice(sample_cols)] = missing_entry
missing_vals += 1
print(f"Number of rows affected: {missing_vals}")
return df
def corrupt_with_duplicate_rows(df=None, prob=0.01):
dups = pd.DataFrame()
for index,row in df.iterrows():
if random.uniform(0, 1) < prob:
dups = dups.append(df.loc[index, :])
#dups = pd.concat([dups, df.loc[index, :]])
print(f"Number of duplicates added: {len(dups)}")
return pd.concat([df, dups])df_dis_drg = pd.read_csv(f"{base_dir}/DISEASE_DRUG_MAPPINGS.csv")
df_dis_drg| api_num | disease | drug | |
|---|---|---|---|
| 0 | 0 | Alkylating Agent Cystitis | sodium bicarbonate |
| 1 | 1 | Alkylating Agent Cystitis | citric acid / sodium citrate |
| 2 | 2 | Abdominal Distension | bethanechol |
| 3 | 3 | Abdominal Distension | pamabrom |
| 4 | 4 | Abdominal Distension | bethanechol |
| ... | ... | ... | ... |
| 14678 | 14677 | Zollinger-Ellison Syndrome | lansoprazole |
| 14679 | 14678 | Zollinger-Ellison Syndrome | ranitidine |
| 14680 | 14679 | Zollinger-Ellison Syndrome | rabeprazole |
| 14681 | 14680 | Zollinger-Ellison Syndrome | cimetidine |
| 14682 | 14681 | Zollinger-Ellison Syndrome | esomeprazole |
14683 rows × 3 columns
df_dia = pd.DataFrame(df_dis_drg['disease'].unique())
df_dia| 0 | |
|---|---|
| 0 | Alkylating Agent Cystitis |
| 1 | Abdominal Distension |
| 2 | Abdominal Distension Prior to Abdominal X-ray |
| 3 | Abdominal Radiological Procedure |
| 4 | Abnormal Uterine Bleeding |
| ... | ... |
| 1503 | Xerostomia |
| 1504 | X-Linked Hypophosphatemia |
| 1505 | Yellow Fever Prophylaxis |
| 1506 | Zinc Deficiency |
| 1507 | Zollinger-Ellison Syndrome |
1508 rows × 1 columns
df_dia = df_dia.reset_index()
df_dia.columns = ['id', 'diagnosis']
df_dia| id | diagnosis | |
|---|---|---|
| 0 | 0 | Alkylating Agent Cystitis |
| 1 | 1 | Abdominal Distension |
| 2 | 2 | Abdominal Distension Prior to Abdominal X-ray |
| 3 | 3 | Abdominal Radiological Procedure |
| 4 | 4 | Abnormal Uterine Bleeding |
| ... | ... | ... |
| 1503 | 1503 | Xerostomia |
| 1504 | 1504 | X-Linked Hypophosphatemia |
| 1505 | 1505 | Yellow Fever Prophylaxis |
| 1506 | 1506 | Zinc Deficiency |
| 1507 | 1507 | Zollinger-Ellison Syndrome |
1508 rows × 2 columns
df_dia.to_csv(f'{base_dir}/diagnoses.csv', index=False)df_med = pd.DataFrame(df_dis_drg['drug'].unique())
df_med| 0 | |
|---|---|
| 0 | sodium bicarbonate |
| 1 | citric acid / sodium citrate |
| 2 | bethanechol |
| 3 | pamabrom |
| 4 | vasopressin |
| ... | ... |
| 1610 | zinc acetate |
| 1611 | trientine |
| 1612 | glycerin |
| 1613 | burosumab |
| 1614 | yellow fever vaccine |
1615 rows × 1 columns
df_med = df_med.reset_index()
df_med.columns = ['id', 'medication']
df_med| id | medication | |
|---|---|---|
| 0 | 0 | sodium bicarbonate |
| 1 | 1 | citric acid / sodium citrate |
| 2 | 2 | bethanechol |
| 3 | 3 | pamabrom |
| 4 | 4 | vasopressin |
| ... | ... | ... |
| 1610 | 1610 | zinc acetate |
| 1611 | 1611 | trientine |
| 1612 | 1612 | glycerin |
| 1613 | 1613 | burosumab |
| 1614 | 1614 | yellow fever vaccine |
1615 rows × 2 columns
df_med.to_csv(f'{base_dir}/medications.csv', index=False)df_dis_drg| api_num | disease | drug | |
|---|---|---|---|
| 0 | 0 | Alkylating Agent Cystitis | sodium bicarbonate |
| 1 | 1 | Alkylating Agent Cystitis | citric acid / sodium citrate |
| 2 | 2 | Abdominal Distension | bethanechol |
| 3 | 3 | Abdominal Distension | pamabrom |
| 4 | 4 | Abdominal Distension | bethanechol |
| ... | ... | ... | ... |
| 14678 | 14677 | Zollinger-Ellison Syndrome | lansoprazole |
| 14679 | 14678 | Zollinger-Ellison Syndrome | ranitidine |
| 14680 | 14679 | Zollinger-Ellison Syndrome | rabeprazole |
| 14681 | 14680 | Zollinger-Ellison Syndrome | cimetidine |
| 14682 | 14681 | Zollinger-Ellison Syndrome | esomeprazole |
14683 rows × 3 columns
diameds = []
for index,row in df_dis_drg.iterrows():
dia = row['disease']; med = row['drug']
dia_id = df_dia[df_dia['diagnosis']==dia].id.values[0]
med_id = df_med[df_med['medication']==med].id.values[0]
diameds.append({
'diagnosis_id':dia_id,
'medication_id':med_id},
)
df_diamed = pd.DataFrame(diameds)df_diamed = df_diamed.reset_index()
df_diamed.columns = ['id', 'diagnosis_id', 'medication_id']
df_diamed| id | diagnosis_id | medication_id | |
|---|---|---|---|
| 0 | 0 | 0 | 0 |
| 1 | 1 | 0 | 1 |
| 2 | 2 | 1 | 2 |
| 3 | 3 | 1 | 3 |
| 4 | 4 | 1 | 2 |
| ... | ... | ... | ... |
| 14678 | 14678 | 1507 | 219 |
| 14679 | 14679 | 1507 | 684 |
| 14680 | 14680 | 1507 | 397 |
| 14681 | 14681 | 1507 | 489 |
| 14682 | 14682 | 1507 | 396 |
14683 rows × 3 columns
df_diamed.to_csv(f'{base_dir}/claim_diagnosis.csv', index=False)def get_name_parts(name_string):
name_parts = name_string.split()
if len(name_parts)==2:
return name_parts
else:
print(f'Not used: {name_parts}')
return Nonedef make_members(num):
fake_members = []
for i in range(num):
prof = fake.profile()
#last and first names
name_parts = get_name_parts(prof['name'])
if name_parts==None: continue
#previous names
prev_name_prob = random.uniform(0, 1)
if random.uniform(0, 1) < 0.1:
cur_gender = prof['sex']
prof_prev_name = fake.profile(sex=cur_gender)
prev_name_parts = get_name_parts(prof_prev_name['name'])
if prev_name_parts==None:
prev_name = ''
else:
prev_name = prev_name_parts[0]+' '+prev_name_parts[1]
else:
prev_name = ''
fake_members.append({
'last_name': name_parts[1],
'first_name': name_parts[0],
'previous_name':prev_name,
'birth_date':prof['birthdate'],
'sex':prof['sex'],
'address':prof['address'],
'phone_number':fake.phone_number(),
'email_address':prof['mail'],
'user_name':prof['username'],
'blood_group':prof['blood_group'],
'job':prof['job'],
'employer':prof['company'],
'ssn':prof['ssn'],
})
return fake_membersdf_mem = pd.DataFrame(make_members(num=1000))Not used: ['Thomas', 'Barber', 'DVM']
Not used: ['Mr.', 'Willie', 'Mcclure']
Not used: ['Mr.', 'Robert', 'Graham']
Not used: ['Dr.', 'Kathleen', 'Pope']
Not used: ['Mrs.', 'Brenda', 'Phillips']
Not used: ['Eric', 'Gonzales', 'MD']
Not used: ['Mr.', 'William', 'Berry', 'MD']
Not used: ['Dr.', 'Paul', 'Thomas']
Not used: ['Mr.', 'Joshua', 'Hudson']
Not used: ['Jennifer', 'Hughes', 'DDS']
Not used: ['Dr.', 'Jared', 'Patton', 'MD']
Not used: ['Dakota', 'Brown', 'MD']
Not used: ['Dr.', 'Kristi', 'Edwards']
Not used: ['Jamie', 'Gutierrez', 'DDS']
Not used: ['Mr.', 'Joseph', 'Maddox']
Not used: ['Alicia', 'Jenkins', 'DDS']
Not used: ['Michelle', 'Hays', 'MD']
Not used: ['Mr.', 'Kevin', 'Knight', 'MD']
Not used: ['Jesus', 'Thomas', 'DDS']
Not used: ['Mr.', 'Robert', 'Evans']
Not used: ['David', 'Bishop', 'PhD']
Not used: ['Kristin', 'Foster', 'MD']
Not used: ['Diane', 'Bush', 'DVM']
Not used: ['Mr.', 'Jack', 'Wise']
Not used: ['Mr.', 'Phillip', 'Garcia']
Not used: ['Mr.', 'Terry', 'Tran', 'Jr.']
Not used: ['Amanda', 'Medina', 'PhD']
Not used: ['Ethan', 'Allen', 'Jr.']
Not used: ['Elizabeth', 'Hall', 'DDS']
Not used: ['Rebecca', 'Villegas', 'DVM']
Not used: ['Phillip', 'Sullivan', 'Jr.']
Not used: ['Casey', 'Schultz', 'MD']
Not used: ['Alan', 'Ross', 'DDS']
Not used: ['Mrs.', 'Brenda', 'Mann']
Not used: ['Miss', 'Theresa', 'Harris']
Not used: ['Dr.', 'Lisa', 'Lee']
Not used: ['Mrs.', 'Erika', 'Benjamin', 'DVM']
Not used: ['Dr.', 'Jose', 'Daniels', 'MD']
Not used: ['Brooke', 'Sparks', 'MD']
Not used: ['Samantha', 'Williams', 'MD']
Not used: ['Miss', 'Lori', 'Armstrong']
Not used: ['Patricia', 'Smith', 'MD']
Not used: ['Shirley', 'Oliver', 'DDS']
Not used: ['Mrs.', 'Sandra', 'Young']
Not used: ['Jennifer', 'Schneider', 'MD']
Not used: ['Mr.', 'Jacob', 'King']
Not used: ['Dr.', 'Michael', 'Johnson']df_mem| last_name | first_name | previous_name | birth_date | sex | address | phone_number | email_address | user_name | blood_group | job | employer | ssn | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | Lee | Kristi | 2013-12-08 | F | Unit 2222 Box 6087\nDPO AA 60968 | 524.030.6359 | goodmanrobert@gmail.com | jonathan50 | B- | Solicitor | Rodriguez, Williams and York | 606-93-4627 | |
| 1 | Diaz | Donna | 1910-01-29 | F | USNV Roberts\nFPO AA 87961 | 001-264-368-5096x956 | sreyes@yahoo.com | richardmartin | B- | Nature conservation officer | Buchanan PLC | 526-27-0606 | |
| 2 | Shaw | Lisa | 1949-05-13 | F | 912 Lynch Neck\nPort Scott, MI 23397 | 001-471-078-6036x44316 | dennis07@gmail.com | jeffrey89 | AB+ | Clinical biochemist | Mccarthy, Martinez and Patrick | 786-42-1164 | |
| 3 | Harvey | Michelle | 2003-02-26 | F | 76165 Jasmine Squares\nCarlosborough, VT 78356 | 001-746-273-4473 | jill66@yahoo.com | josephallen | AB- | Environmental health practitioner | Martinez, Reese and Ford | 012-98-3366 | |
| 4 | Odom | Shawn | 2005-06-22 | M | 473 Elliott Trail\nBarneschester, KY 77207 | 001-234-240-8768x5475 | tmccann@gmail.com | jillianlopez | A+ | Furniture designer | Salazar-Bailey | 502-86-0544 | |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 952 | Flores | Jamie | 1947-11-09 | F | 5351 Jill Coves Suite 930\nEast David, MN 62503 | 732.751.4409 | yolandafuentes@hotmail.com | crosbyerin | A- | Geochemist | Rogers Group | 473-53-3476 | |
| 953 | Barnes | Tina | 1996-09-17 | F | 110 Mark Gateway\nEast Jeffrey, CA 52738 | (330)510-4233x903 | janethall@gmail.com | richard79 | O- | Plant breeder/geneticist | Huffman Inc | 387-99-7895 | |
| 954 | Martin | Stacy | 1978-03-14 | F | 95725 Raymond Walks\nHaleyport, CA 94212 | 806-345-7941x41066 | angela09@hotmail.com | wlopez | AB- | Site engineer | Allen Group | 591-85-1746 | |
| 955 | Thompson | Katherine | 2003-05-19 | F | 942 Jennifer Common Apt. 549\nNicholasmouth, C... | (781)464-1259 | john33@gmail.com | jason63 | A+ | Drilling engineer | Fisher Group | 853-19-0052 | |
| 956 | Rodriguez | Anthony | 1997-06-10 | M | 682 Jensen Key\nLake Jessicashire, MO 51992 | 001-580-568-1299 | lharris@gmail.com | hensonmichael | AB+ | Engineer, electronics | Atkinson, Hahn and James | 608-60-9685 |
957 rows × 13 columns
orig_cols = list(df_mem.columns)
df_mem = df_mem.reset_index()
df_mem.columns = ['id'] + orig_cols
df_mem| id | last_name | first_name | previous_name | birth_date | sex | address | phone_number | email_address | user_name | blood_group | job | employer | ssn | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 0 | Lee | Kristi | 2013-12-08 | F | Unit 2222 Box 6087\nDPO AA 60968 | 524.030.6359 | goodmanrobert@gmail.com | jonathan50 | B- | Solicitor | Rodriguez, Williams and York | 606-93-4627 | |
| 1 | 1 | Diaz | Donna | 1910-01-29 | F | USNV Roberts\nFPO AA 87961 | 001-264-368-5096x956 | sreyes@yahoo.com | richardmartin | B- | Nature conservation officer | Buchanan PLC | 526-27-0606 | |
| 2 | 2 | Shaw | Lisa | 1949-05-13 | F | 912 Lynch Neck\nPort Scott, MI 23397 | 001-471-078-6036x44316 | dennis07@gmail.com | jeffrey89 | AB+ | Clinical biochemist | Mccarthy, Martinez and Patrick | 786-42-1164 | |
| 3 | 3 | Harvey | Michelle | 2003-02-26 | F | 76165 Jasmine Squares\nCarlosborough, VT 78356 | 001-746-273-4473 | jill66@yahoo.com | josephallen | AB- | Environmental health practitioner | Martinez, Reese and Ford | 012-98-3366 | |
| 4 | 4 | Odom | Shawn | 2005-06-22 | M | 473 Elliott Trail\nBarneschester, KY 77207 | 001-234-240-8768x5475 | tmccann@gmail.com | jillianlopez | A+ | Furniture designer | Salazar-Bailey | 502-86-0544 | |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 952 | 952 | Flores | Jamie | 1947-11-09 | F | 5351 Jill Coves Suite 930\nEast David, MN 62503 | 732.751.4409 | yolandafuentes@hotmail.com | crosbyerin | A- | Geochemist | Rogers Group | 473-53-3476 | |
| 953 | 953 | Barnes | Tina | 1996-09-17 | F | 110 Mark Gateway\nEast Jeffrey, CA 52738 | (330)510-4233x903 | janethall@gmail.com | richard79 | O- | Plant breeder/geneticist | Huffman Inc | 387-99-7895 | |
| 954 | 954 | Martin | Stacy | 1978-03-14 | F | 95725 Raymond Walks\nHaleyport, CA 94212 | 806-345-7941x41066 | angela09@hotmail.com | wlopez | AB- | Site engineer | Allen Group | 591-85-1746 | |
| 955 | 955 | Thompson | Katherine | 2003-05-19 | F | 942 Jennifer Common Apt. 549\nNicholasmouth, C... | (781)464-1259 | john33@gmail.com | jason63 | A+ | Drilling engineer | Fisher Group | 853-19-0052 | |
| 956 | 956 | Rodriguez | Anthony | 1997-06-10 | M | 682 Jensen Key\nLake Jessicashire, MO 51992 | 001-580-568-1299 | lharris@gmail.com | hensonmichael | AB+ | Engineer, electronics | Atkinson, Hahn and James | 608-60-9685 |
957 rows × 14 columns
df_mem = corrupt_with_missing_values(df=df_mem, sample_cols=[
'first_name', 'birth_date', 'sex', 'address', 'phone_number', 'email_address',
'user_name', 'blood_group', 'job', 'employer', 'ssn'], missing_entry='NA', prob=0.1)
df_memNumber of rows affected: 104| id | last_name | first_name | previous_name | birth_date | sex | address | phone_number | email_address | user_name | blood_group | job | employer | ssn | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 0 | Lee | Kristi | 2013-12-08 | F | Unit 2222 Box 6087\nDPO AA 60968 | 524.030.6359 | goodmanrobert@gmail.com | jonathan50 | B- | Solicitor | Rodriguez, Williams and York | 606-93-4627 | |
| 1 | 1 | Diaz | Donna | 1910-01-29 | F | USNV Roberts\nFPO AA 87961 | 001-264-368-5096x956 | sreyes@yahoo.com | richardmartin | B- | Nature conservation officer | Buchanan PLC | 526-27-0606 | |
| 2 | 2 | Shaw | Lisa | 1949-05-13 | F | 912 Lynch Neck\nPort Scott, MI 23397 | 001-471-078-6036x44316 | dennis07@gmail.com | jeffrey89 | AB+ | Clinical biochemist | Mccarthy, Martinez and Patrick | 786-42-1164 | |
| 3 | 3 | Harvey | Michelle | 2003-02-26 | F | 76165 Jasmine Squares\nCarlosborough, VT 78356 | 001-746-273-4473 | jill66@yahoo.com | josephallen | AB- | Environmental health practitioner | Martinez, Reese and Ford | 012-98-3366 | |
| 4 | 4 | Odom | Shawn | 2005-06-22 | M | 473 Elliott Trail\nBarneschester, KY 77207 | 001-234-240-8768x5475 | tmccann@gmail.com | jillianlopez | A+ | Furniture designer | Salazar-Bailey | 502-86-0544 | |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 952 | 952 | Flores | Jamie | 1947-11-09 | F | 5351 Jill Coves Suite 930\nEast David, MN 62503 | 732.751.4409 | yolandafuentes@hotmail.com | crosbyerin | A- | Geochemist | Rogers Group | 473-53-3476 | |
| 953 | 953 | Barnes | Tina | 1996-09-17 | F | 110 Mark Gateway\nEast Jeffrey, CA 52738 | (330)510-4233x903 | janethall@gmail.com | richard79 | O- | Plant breeder/geneticist | Huffman Inc | 387-99-7895 | |
| 954 | 954 | Martin | Stacy | 1978-03-14 | F | 95725 Raymond Walks\nHaleyport, CA 94212 | 806-345-7941x41066 | angela09@hotmail.com | wlopez | AB- | Site engineer | Allen Group | 591-85-1746 | |
| 955 | 955 | Thompson | Katherine | 2003-05-19 | F | 942 Jennifer Common Apt. 549\nNicholasmouth, C... | (781)464-1259 | john33@gmail.com | jason63 | A+ | Drilling engineer | Fisher Group | 853-19-0052 | |
| 956 | 956 | Rodriguez | Anthony | 1997-06-10 | M | 682 Jensen Key\nLake Jessicashire, MO 51992 | 001-580-568-1299 | lharris@gmail.com | hensonmichael | AB+ | Engineer, electronics | Atkinson, Hahn and James | 608-60-9685 |
957 rows × 14 columns
df_mem = corrupt_with_duplicate_rows(df=df_mem, prob=0.05)
df_memNumber of duplicates added: 31| id | last_name | first_name | previous_name | birth_date | sex | address | phone_number | email_address | user_name | blood_group | job | employer | ssn | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 0.0 | Lee | Kristi | 2013-12-08 | F | Unit 2222 Box 6087\nDPO AA 60968 | 524.030.6359 | goodmanrobert@gmail.com | jonathan50 | B- | Solicitor | Rodriguez, Williams and York | 606-93-4627 | |
| 1 | 1.0 | Diaz | Donna | 1910-01-29 | F | USNV Roberts\nFPO AA 87961 | 001-264-368-5096x956 | sreyes@yahoo.com | richardmartin | B- | Nature conservation officer | Buchanan PLC | 526-27-0606 | |
| 2 | 2.0 | Shaw | Lisa | 1949-05-13 | F | 912 Lynch Neck\nPort Scott, MI 23397 | 001-471-078-6036x44316 | dennis07@gmail.com | jeffrey89 | AB+ | Clinical biochemist | Mccarthy, Martinez and Patrick | 786-42-1164 | |
| 3 | 3.0 | Harvey | Michelle | 2003-02-26 | F | 76165 Jasmine Squares\nCarlosborough, VT 78356 | 001-746-273-4473 | jill66@yahoo.com | josephallen | AB- | Environmental health practitioner | Martinez, Reese and Ford | 012-98-3366 | |
| 4 | 4.0 | Odom | Shawn | 2005-06-22 | M | 473 Elliott Trail\nBarneschester, KY 77207 | 001-234-240-8768x5475 | tmccann@gmail.com | jillianlopez | A+ | Furniture designer | Salazar-Bailey | 502-86-0544 | |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 813 | 813.0 | Perkins | Taylor | 1984-07-11 | F | PSC 8911, Box 2686\nAPO AA 31832 | 2821264681 | cory49@hotmail.com | gjohnson | AB- | Forensic psychologist | Carr PLC | 499-63-7334 | |
| 875 | 875.0 | Smith | Adam | 2000-10-29 | M | 76743 Jean Trail Apt. 235\nJohnsonview, WA 92986 | 608-361-5927x867 | nguyensara@yahoo.com | zjackson | B+ | Adult nurse | Boyd-Kennedy | NA | |
| 918 | 918.0 | Bowman | Ashley | 2002-01-24 | F | 5287 Mary Vista Apt. 468\nLake Ronald, IL 19853 | 1735222944 | andrewcastillo@hotmail.com | austinregina | AB- | Computer games developer | Morton, Zimmerman and Browning | 571-72-7246 | |
| 940 | 940.0 | Cook | Laura | 1992-01-07 | F | 3457 Tammy Street Apt. 183\nMichelleville, OR ... | 7150104395 | jamescarey@gmail.com | coxjeremy | AB- | Scientific laboratory technician | Cain-Williams | 144-96-3501 | |
| 941 | 941.0 | Williams | Christine | 1972-07-21 | F | 19357 Nichols Lights\nWest Katherine, MT 79155 | 264.857.5155 | christopherrodriguez@hotmail.com | uhendrix | O+ | Broadcast engineer | Barber-Shea | 782-72-4233 |
988 rows × 14 columns
df_mem.to_csv(f'{base_dir}/members.csv', index=True)df_payer_names = pd.read_csv(f'{base_dir}/PAYERS.csv', header=None)
df_payer_names| 0 | |
|---|---|
| 0 | Aardvark Claims |
| 1 | Ability Clinical Technologies |
| 2 | Allegro Medical Billing |
| 3 | Allenton Medical Devices |
| 4 | Allied Medical Billing |
| ... | ... |
| 132 | Stellar Physicians Group |
| 133 | Streamlined Hospital Supplies |
| 134 | The Aegis Partners |
| 135 | Vital Heart Medical Co. |
| 136 | Vital Payments |
137 rows × 1 columns
def make_payers(num):
fake_payers = []
for i in range(num):
prof = fake.profile()
fake_payers.append({
'name':df_payer_names.sample().values[0][0],
'address':prof['address'],
'phone_number':fake.phone_number(),
})
return fake_payersdf_pay = pd.DataFrame(make_payers(num=100))df_pay| name | address | phone_number | |
|---|---|---|---|
| 0 | Priority Medical | 09525 Hernandez Ports\nStephanieside, SC 03000 | 505.041.1354 |
| 1 | Patients Remit | 060 Andrews Inlet\nPeterberg, ND 01067 | 625.389.8174x4641 |
| 2 | Atlas Recovery Inc. | 86728 Susan Knoll\nPort Billy, AK 61552 | (756)107-7716 |
| 3 | Premium Pulse | 444 Alicia Crest Apt. 512\nZunigabury, MD 68141 | (706)825-7011x401 |
| 4 | Cyma Associates | PSC 1686, Box 0526\nAPO AA 66075 | 264-562-7324x3978 |
| ... | ... | ... | ... |
| 95 | The Good Digits | 5801 Tonya Mount\nGibsonmouth, VT 72279 | 918.434.7526x947 |
| 96 | Procure Payments | 377 Marshall Fork Suite 006\nSouth Joshua, OR ... | 635-672-9837x9067 |
| 97 | All aboard Medical Transport | PSC 0645, Box 3038\nAPO AA 64291 | 408.647.4734x9067 |
| 98 | Lifeline Medical Supply | 5076 Whitney Spring\nOneillland, ID 08606 | +1-707-236-1234x9563 |
| 99 | Insight Medical Solutions | 371 Schroeder Ville\nAllenchester, NJ 09801 | (106)665-4897x65756 |
100 rows × 3 columns
orig_cols = list(df_pay.columns)
df_pay = df_pay.reset_index()
df_pay.columns = ['id'] + orig_cols
df_pay| id | name | address | phone_number | |
|---|---|---|---|---|
| 0 | 0 | Priority Medical | 09525 Hernandez Ports\nStephanieside, SC 03000 | 505.041.1354 |
| 1 | 1 | Patients Remit | 060 Andrews Inlet\nPeterberg, ND 01067 | 625.389.8174x4641 |
| 2 | 2 | Atlas Recovery Inc. | 86728 Susan Knoll\nPort Billy, AK 61552 | (756)107-7716 |
| 3 | 3 | Premium Pulse | 444 Alicia Crest Apt. 512\nZunigabury, MD 68141 | (706)825-7011x401 |
| 4 | 4 | Cyma Associates | PSC 1686, Box 0526\nAPO AA 66075 | 264-562-7324x3978 |
| ... | ... | ... | ... | ... |
| 95 | 95 | The Good Digits | 5801 Tonya Mount\nGibsonmouth, VT 72279 | 918.434.7526x947 |
| 96 | 96 | Procure Payments | 377 Marshall Fork Suite 006\nSouth Joshua, OR ... | 635-672-9837x9067 |
| 97 | 97 | All aboard Medical Transport | PSC 0645, Box 3038\nAPO AA 64291 | 408.647.4734x9067 |
| 98 | 98 | Lifeline Medical Supply | 5076 Whitney Spring\nOneillland, ID 08606 | +1-707-236-1234x9563 |
| 99 | 99 | Insight Medical Solutions | 371 Schroeder Ville\nAllenchester, NJ 09801 | (106)665-4897x65756 |
100 rows × 4 columns
df_pay_A = corrupt_with_missing_values(
df=df_pay,
sample_cols=['address', 'phone_number'], missing_entry='NA', prob=0.1)
df_payNumber of rows affected: 9| id | name | address | phone_number | |
|---|---|---|---|---|
| 0 | 0 | Priority Medical | 09525 Hernandez Ports\nStephanieside, SC 03000 | 505.041.1354 |
| 1 | 1 | Patients Remit | 060 Andrews Inlet\nPeterberg, ND 01067 | 625.389.8174x4641 |
| 2 | 2 | Atlas Recovery Inc. | 86728 Susan Knoll\nPort Billy, AK 61552 | (756)107-7716 |
| 3 | 3 | Premium Pulse | 444 Alicia Crest Apt. 512\nZunigabury, MD 68141 | (706)825-7011x401 |
| 4 | 4 | Cyma Associates | PSC 1686, Box 0526\nAPO AA 66075 | 264-562-7324x3978 |
| ... | ... | ... | ... | ... |
| 95 | 95 | The Good Digits | 5801 Tonya Mount\nGibsonmouth, VT 72279 | 918.434.7526x947 |
| 96 | 96 | Procure Payments | 377 Marshall Fork Suite 006\nSouth Joshua, OR ... | 635-672-9837x9067 |
| 97 | 97 | All aboard Medical Transport | PSC 0645, Box 3038\nAPO AA 64291 | 408.647.4734x9067 |
| 98 | 98 | Lifeline Medical Supply | 5076 Whitney Spring\nOneillland, ID 08606 | +1-707-236-1234x9563 |
| 99 | 99 | Insight Medical Solutions | 371 Schroeder Ville\nAllenchester, NJ 09801 | (106)665-4897x65756 |
100 rows × 4 columns
df_pay = corrupt_with_duplicate_rows(df=df_pay, prob=0.05)
df_payNumber of duplicates added: 2| id | name | address | phone_number | |
|---|---|---|---|---|
| 0 | 0.0 | Priority Medical | 09525 Hernandez Ports\nStephanieside, SC 03000 | 505.041.1354 |
| 1 | 1.0 | Patients Remit | 060 Andrews Inlet\nPeterberg, ND 01067 | 625.389.8174x4641 |
| 2 | 2.0 | Atlas Recovery Inc. | 86728 Susan Knoll\nPort Billy, AK 61552 | (756)107-7716 |
| 3 | 3.0 | Premium Pulse | 444 Alicia Crest Apt. 512\nZunigabury, MD 68141 | (706)825-7011x401 |
| 4 | 4.0 | Cyma Associates | PSC 1686, Box 0526\nAPO AA 66075 | 264-562-7324x3978 |
| ... | ... | ... | ... | ... |
| 97 | 97.0 | All aboard Medical Transport | PSC 0645, Box 3038\nAPO AA 64291 | 408.647.4734x9067 |
| 98 | 98.0 | Lifeline Medical Supply | 5076 Whitney Spring\nOneillland, ID 08606 | +1-707-236-1234x9563 |
| 99 | 99.0 | Insight Medical Solutions | 371 Schroeder Ville\nAllenchester, NJ 09801 | (106)665-4897x65756 |
| 1 | 1.0 | Patients Remit | 060 Andrews Inlet\nPeterberg, ND 01067 | 625.389.8174x4641 |
| 58 | 58.0 | Priority Medical | Unit 4012 Box 4779\nDPO AA 25295 | +1-299-057-6197x82520 |
102 rows × 4 columns
df_pay.to_csv(f'{base_dir}/payers.csv', index=False)df_facility_names = pd.read_csv(f'{base_dir}/FACILITIES.csv', header=None)
df_facility_names| 0 | |
|---|---|
| 0 | Alpha Health Care |
| 1 | Gold River Hospital |
| 2 | Serenity South Hills |
| 3 | Charlotte Eye Group |
| 4 | New Path Health |
| ... | ... |
| 295 | Serenity Surgical |
| 296 | North Mark |
| 297 | East Metro Health |
| 298 | Oasis Family Health |
| 299 | Next Care Health |
300 rows × 1 columns
def make_claims(num):
fake_claims = []
for i in range(num):
mem_sample = df_mem.sample()
pay_sample = df_pay_A.sample()
diamed_sample = df_diamed.sample()
fake_claims.append({
'date':fake.date_between(start_date='-30y', end_date='today'),
'member_id':int(mem_sample['id'].values[0]),
'facility_name':df_facility_names.sample().values[0][0],
'facility_address':fake.address(),
'payer_id':pay_sample['id'].values[0],
'claim_diagnosis_id':diamed_sample['id'].values[0],
})
return fake_claimsdf_clm = pd.DataFrame(make_claims(num=1000))df_clm| date | member_id | facility_name | facility_address | payer_id | claim_diagnosis_id | |
|---|---|---|---|---|---|---|
| 0 | 2021-04-27 | 876 | East Metro Health | 153 Miller Burg\nWest Richardton, ND 88559 | 55 | 11916 |
| 1 | 2014-01-28 | 316 | Zooner Medical | 7218 Tapia Keys\nPort Anthonyton, IL 71712 | 36 | 7952 |
| 2 | 1993-12-16 | 812 | New Path Health | 450 Bethany Estates Suite 840\nSouth Laurenmou... | 24 | 693 |
| 3 | 2013-11-03 | 52 | Claris Care | PSC 8290, Box 2703\nAPO AE 59993 | 27 | 1097 |
| 4 | 2001-10-04 | 363 | Universal Body Clinic | 63208 Stephens Rest Apt. 665\nLake Brianmouth,... | 64 | 500 |
| ... | ... | ... | ... | ... | ... | ... |
| 995 | 2002-03-19 | 26 | Medica Zone | 9998 Amy Lights\nSouth Ginafurt, IN 09994 | 44 | 2162 |
| 996 | 2019-04-08 | 342 | York General | 89688 Church Skyway Apt. 240\nLake Whitneyfurt... | 37 | 5396 |
| 997 | 1999-12-29 | 240 | Winter warm | 26348 Lopez Corners Apt. 955\nPort Dawnstad, A... | 89 | 10447 |
| 998 | 2013-04-06 | 559 | Aussie Health | 444 Jones Drives\nStevenfurt, MA 57955 | 90 | 13644 |
| 999 | 2019-05-26 | 95 | Midsummer Premium | USNV Richardson\nFPO AA 04503 | 24 | 10164 |
1000 rows × 6 columns
orig_cols = list(df_clm.columns)
df_clm = df_clm.reset_index()
df_clm.columns = ['id'] + orig_cols
df_clm| id | date | member_id | facility_name | facility_address | payer_id | claim_diagnosis_id | |
|---|---|---|---|---|---|---|---|
| 0 | 0 | 2021-04-27 | 876 | East Metro Health | 153 Miller Burg\nWest Richardton, ND 88559 | 55 | 11916 |
| 1 | 1 | 2014-01-28 | 316 | Zooner Medical | 7218 Tapia Keys\nPort Anthonyton, IL 71712 | 36 | 7952 |
| 2 | 2 | 1993-12-16 | 812 | New Path Health | 450 Bethany Estates Suite 840\nSouth Laurenmou... | 24 | 693 |
| 3 | 3 | 2013-11-03 | 52 | Claris Care | PSC 8290, Box 2703\nAPO AE 59993 | 27 | 1097 |
| 4 | 4 | 2001-10-04 | 363 | Universal Body Clinic | 63208 Stephens Rest Apt. 665\nLake Brianmouth,... | 64 | 500 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 995 | 995 | 2002-03-19 | 26 | Medica Zone | 9998 Amy Lights\nSouth Ginafurt, IN 09994 | 44 | 2162 |
| 996 | 996 | 2019-04-08 | 342 | York General | 89688 Church Skyway Apt. 240\nLake Whitneyfurt... | 37 | 5396 |
| 997 | 997 | 1999-12-29 | 240 | Winter warm | 26348 Lopez Corners Apt. 955\nPort Dawnstad, A... | 89 | 10447 |
| 998 | 998 | 2013-04-06 | 559 | Aussie Health | 444 Jones Drives\nStevenfurt, MA 57955 | 90 | 13644 |
| 999 | 999 | 2019-05-26 | 95 | Midsummer Premium | USNV Richardson\nFPO AA 04503 | 24 | 10164 |
1000 rows × 7 columns
df_clm_= corrupt_with_missing_values(df=df_clm, sample_cols=[
'date', 'facility_name', 'facility_address'], missing_entry='NA', prob=0.1)
df_clmNumber of rows affected: 107| id | date | member_id | facility_name | facility_address | payer_id | claim_diagnosis_id | |
|---|---|---|---|---|---|---|---|
| 0 | 0 | NA | 876 | East Metro Health | 153 Miller Burg\nWest Richardton, ND 88559 | 55 | 11916 |
| 1 | 1 | 2014-01-28 | 316 | Zooner Medical | 7218 Tapia Keys\nPort Anthonyton, IL 71712 | 36 | 7952 |
| 2 | 2 | 1993-12-16 | 812 | New Path Health | 450 Bethany Estates Suite 840\nSouth Laurenmou... | 24 | 693 |
| 3 | 3 | 2013-11-03 | 52 | Claris Care | PSC 8290, Box 2703\nAPO AE 59993 | 27 | 1097 |
| 4 | 4 | 2001-10-04 | 363 | Universal Body Clinic | 63208 Stephens Rest Apt. 665\nLake Brianmouth,... | 64 | 500 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 995 | 995 | 2002-03-19 | 26 | Medica Zone | 9998 Amy Lights\nSouth Ginafurt, IN 09994 | 44 | 2162 |
| 996 | 996 | 2019-04-08 | 342 | York General | 89688 Church Skyway Apt. 240\nLake Whitneyfurt... | 37 | 5396 |
| 997 | 997 | 1999-12-29 | 240 | Winter warm | 26348 Lopez Corners Apt. 955\nPort Dawnstad, A... | 89 | 10447 |
| 998 | 998 | 2013-04-06 | 559 | NA | 444 Jones Drives\nStevenfurt, MA 57955 | 90 | 13644 |
| 999 | 999 | 2019-05-26 | 95 | Midsummer Premium | USNV Richardson\nFPO AA 04503 | 24 | 10164 |
1000 rows × 7 columns
df_clm_= corrupt_with_duplicate_rows(df=df_clm, prob=0.05)
df_clmNumber of duplicates added: 46| id | date | member_id | facility_name | facility_address | payer_id | claim_diagnosis_id | |
|---|---|---|---|---|---|---|---|
| 0 | 0 | NA | 876 | East Metro Health | 153 Miller Burg\nWest Richardton, ND 88559 | 55 | 11916 |
| 1 | 1 | 2014-01-28 | 316 | Zooner Medical | 7218 Tapia Keys\nPort Anthonyton, IL 71712 | 36 | 7952 |
| 2 | 2 | 1993-12-16 | 812 | New Path Health | 450 Bethany Estates Suite 840\nSouth Laurenmou... | 24 | 693 |
| 3 | 3 | 2013-11-03 | 52 | Claris Care | PSC 8290, Box 2703\nAPO AE 59993 | 27 | 1097 |
| 4 | 4 | 2001-10-04 | 363 | Universal Body Clinic | 63208 Stephens Rest Apt. 665\nLake Brianmouth,... | 64 | 500 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 995 | 995 | 2002-03-19 | 26 | Medica Zone | 9998 Amy Lights\nSouth Ginafurt, IN 09994 | 44 | 2162 |
| 996 | 996 | 2019-04-08 | 342 | York General | 89688 Church Skyway Apt. 240\nLake Whitneyfurt... | 37 | 5396 |
| 997 | 997 | 1999-12-29 | 240 | Winter warm | 26348 Lopez Corners Apt. 955\nPort Dawnstad, A... | 89 | 10447 |
| 998 | 998 | 2013-04-06 | 559 | NA | 444 Jones Drives\nStevenfurt, MA 57955 | 90 | 13644 |
| 999 | 999 | 2019-05-26 | 95 | Midsummer Premium | USNV Richardson\nFPO AA 04503 | 24 | 10164 |
1000 rows × 7 columns
df_clm.to_csv(f'{base_dir}/claims.csv', index=False)def make_memdias(num):
fake_memdias = []
for x in range(num):
mem_sample = df_mem.sample()
dia_sample = df_dia.sample()
fake_memdias.append({
'member_id':int(mem_sample['id'].values[0]),
'diagnosis_id':dia_sample['id'].values[0],
})
return fake_memdiasdf_memdia = pd.DataFrame(make_memdias(num=1000))df_memdia| member_id | diagnosis_id | |
|---|---|---|
| 0 | 335 | 1094 |
| 1 | 267 | 1158 |
| 2 | 334 | 159 |
| 3 | 458 | 463 |
| 4 | 144 | 405 |
| ... | ... | ... |
| 995 | 740 | 304 |
| 996 | 51 | 1280 |
| 997 | 11 | 1044 |
| 998 | 837 | 1359 |
| 999 | 790 | 462 |
1000 rows × 2 columns
orig_cols = list(df_memdia.columns)
df_memdia = df_memdia.reset_index()
df_memdia.columns = ['id'] + orig_cols
df_memdia| id | member_id | diagnosis_id | |
|---|---|---|---|
| 0 | 0 | 335 | 1094 |
| 1 | 1 | 267 | 1158 |
| 2 | 2 | 334 | 159 |
| 3 | 3 | 458 | 463 |
| 4 | 4 | 144 | 405 |
| ... | ... | ... | ... |
| 995 | 995 | 740 | 304 |
| 996 | 996 | 51 | 1280 |
| 997 | 997 | 11 | 1044 |
| 998 | 998 | 837 | 1359 |
| 999 | 999 | 790 | 462 |
1000 rows × 3 columns
df_memdia.to_csv(f'{base_dir}/member-diagnoses.csv', index=False)!ls -alh "{base_dir}"total 1.4M
-rw------- 1 root root 192K Apr 29 19:35 claim_diagnosis.csv
-rw------- 1 root root 88K Apr 29 19:38 claims.csv
-rw------- 1 root root 40K Apr 29 19:34 diagnoses.csv
-rw------- 1 root root 585K Apr 28 17:49 DISEASE_DRUG_MAPPINGS.csv
-rw------- 1 root root 5.2K Apr 28 21:04 FACILITIES.csv
-rw------- 1 root root 34K Apr 29 19:34 medications.csv
-rw------- 1 root root 12K Apr 29 19:38 member-diagnoses.csv
-rw------- 1 root root 183K Apr 29 19:36 members.csv
-rw------- 1 root root 8.6K Apr 29 19:37 payers.csv
-rw------- 1 root root 2.6K Apr 28 20:17 PAYERS.csv
-rw------- 1 root root 220K Apr 29 19:41 synthetic_insurance_data.ipynbThe generated .csv files could be used to populate a database. Here is a brief section from each of these files:
!head -10 "{base_dir}/members.csv",id,last_name,first_name,previous_name,birth_date,sex,address,phone_number,email_address,user_name,blood_group,job,employer,ssn
0,0.0,Lee,Kristi,,2013-12-08,F,"Unit 2222 Box 6087
DPO AA 60968",524.030.6359,goodmanrobert@gmail.com,jonathan50,B-,Solicitor,"Rodriguez, Williams and York",606-93-4627
1,1.0,Diaz,Donna,,1910-01-29,F,"USNV Roberts
FPO AA 87961",001-264-368-5096x956,sreyes@yahoo.com,richardmartin,B-,Nature conservation officer,Buchanan PLC,526-27-0606
2,2.0,Shaw,Lisa,,1949-05-13,F,"912 Lynch Neck
Port Scott, MI 23397",001-471-078-6036x44316,dennis07@gmail.com,jeffrey89,AB+,Clinical biochemist,"Mccarthy, Martinez and Patrick",786-42-1164
3,3.0,Harvey,Michelle,,2003-02-26,F,"76165 Jasmine Squares
Carlosborough, VT 78356",001-746-273-4473,jill66@yahoo.com,josephallen,AB-,Environmental health practitioner,"Martinez, Reese and Ford",012-98-3366
4,4.0,Odom,Shawn,,2005-06-22,M,"473 Elliott Trail!head -10 "{base_dir}/payers.csv"id,name,address,phone_number
0.0,Priority Medical,"09525 Hernandez Ports
Stephanieside, SC 03000",505.041.1354
1.0,Patients Remit,"060 Andrews Inlet
Peterberg, ND 01067",625.389.8174x4641
2.0,Atlas Recovery Inc.,"86728 Susan Knoll
Port Billy, AK 61552",(756)107-7716
3.0,Premium Pulse,"444 Alicia Crest Apt. 512
Zunigabury, MD 68141",(706)825-7011x401
4.0,Cyma Associates,"PSC 1686, Box 0526!head -10 "{base_dir}/claims.csv"id,date,member_id,facility_name,facility_address,payer_id,claim_diagnosis_id
0,NA,876,East Metro Health,"153 Miller Burg
West Richardton, ND 88559",55,11916
1,2014-01-28,316,Zooner Medical,"7218 Tapia Keys
Port Anthonyton, IL 71712",36,7952
2,1993-12-16,812,New Path Health,"450 Bethany Estates Suite 840
South Laurenmouth, GA 44481",24,693
3,2013-11-03,52,Claris Care,"PSC 8290, Box 2703
APO AE 59993",27,1097
4,2001-10-04,363,Universal Body Clinic,"63208 Stephens Rest Apt. 665!head -10 "{base_dir}/diagnoses.csv"id,diagnosis
0,Alkylating Agent Cystitis
1,Abdominal Distension
2,Abdominal Distension Prior to Abdominal X-ray
3,Abdominal Radiological Procedure
4,Abnormal Uterine Bleeding
5,Abortion
6,Angina
7,Acetaminophen Overdose
8,Acidosis!head -10 "{base_dir}/medications.csv"id,medication
0,sodium bicarbonate
1,citric acid / sodium citrate
2,bethanechol
3,pamabrom
4,vasopressin
5,medroxyprogesterone
6,norethindrone
7,megestrol
8,levonorgestrel!head -10 "{base_dir}/claim_diagnosis.csv"id,diagnosis_id,medication_id
0,0,0
1,0,1
2,1,2
3,1,3
4,1,2
5,1,3
6,2,4
7,3,4
8,3,4!head -10 "{base_dir}/member-diagnoses.csv"id,member_id,diagnosis_id
0,335,1094
1,267,1158
2,334,159
3,458,463
4,144,405
5,843,982
6,135,104
7,656,998
8,858,1413