Flourish

# -*- coding: utf-8 -*-

"""issue_21_ultrasound_form_delivery_date.ipynb

Automatically generated by Colab.

Original file is located at

    https://colab.research.google.com/drive/1o_D5W6ARSNXdY-9G3g36h6B_R16cWs6g

## 1. Required dependencies

### ***please load the packages and update the constants/variables in the cell below. after that you can run the remainder of the script to reproduce the steps I took to audit the issue. thanks!***

"""

import pandas as pd

import numpy as np

# constant values needed to run script

# please update the values below

# export csv from: EDC > Flourish Caregiver > Birth Form

PATH_TO_MATERNAL_DELIVERY = '/MaternalDelivery-2024-06-20.csv'

# export csv from: EDC > Flourish Caregiver > Ultrasound Form

PATH_TO_ULTRASOUND = '/UltraSound-2024-06-14.csv'

# quick function to check df dimensions against n unique chilpid's to verify no duplicates

def print_df_dimensions(df):

    print(f"""checking dimensions:

          dimensions: {df.shape}

          nunique childpid: {df["childpid"].nunique()}

          ---------

          """)

# You can now run the rest of the script!

"""## 2. Run the script below to reproduce the data anomalies highlighted in this issue

### Loading data

"""

# loading maternal delivery data

maternal_delivery_df = pd.read_csv(PATH_TO_MATERNAL_DELIVERY)

# verifying no duplicates

print_df_dimensions(maternal_delivery_df)

# selecting columns relevant to issue

maternal_delivery_df = maternal_delivery_df.filter(regex = "childpid|matpid|datetime")

# printing columns to confirm appropriate variables selected

print("maternal_delivery_df columns selected:\n\t", maternal_delivery_df.columns.tolist())

# renaming columns with suffix to identify CRF data was pulled from

maternal_delivery_df = maternal_delivery_df.rename(columns={"report_datetime":"report_datetime_matdeliverycrf", "delivery_datetime":"delivery_datetime_matdeliverycrf"})

print("\nnew column names for merging with ultrasound df:\n\t", maternal_delivery_df.columns.tolist())

# loading ultrasound form data

ultrasound_df = pd.read_csv(PATH_TO_ULTRASOUND)

# verifying no duplicates

print_df_dimensions(ultrasound_df)

# selecting columns relevant to issue

ultrasound_df = ultrasound_df.filter(regex = "childpid|matpid|datetime|delivery_date")

# printing columns to confirm appropriate variables selected

print("ultrasound_df columns selected:\n\t", ultrasound_df.columns.tolist())

# renaming columns with suffix to identify CRF data was pulled from

ultrasound_df = ultrasound_df.rename(columns={"report_datetime":"report_datetime_ultrasoundcrf", "maternal_delivery_date":"maternal_delivery_date_ultrasoundcrf"})

print("\nnew column names for merging with maternal delivery df:\n\t", ultrasound_df.columns.tolist())

"""### Merging `maternal_delivery_df` and `ultrasound_df` on `childpid` to compare values for delivery dates across the two CRFs"""

# merging maternal_delivery_df and ultrasound_df on childpid

merged_df = pd.merge(ultrasound_df, maternal_delivery_df, on=['childpid', 'matpid'], how='left')

# verifying merge

print_df_dimensions(merged_df)

"""### Creating `issue_df` with records relevant to current issue by filtering `merged_df` where:

- delivery date is not missing

- delivery date in `maternal_delivery_df` does not match/equal delivery date in `ultrasound_df`

"""

# filtering observations where delivery_datetime_matdeliverycrf does NOT equal maternal_delivery_date_ultrasoundcrf

issue_df = merged_df.copy()[~(merged_df['delivery_datetime_matdeliverycrf'] == merged_df['maternal_delivery_date_ultrasoundcrf'])]

# removing observations for subjects who haven't been born yet (i.e., where delivery date is a missing value)

issue_df = issue_df[issue_df['delivery_datetime_matdeliverycrf'].notna()]

# verifying merge

print_df_dimensions(issue_df)