Project dataset

[robperch]

Create dataset containing all the appointment data from years 2022 and 2023 to do the analysis for the project

Appointment features

• [x] Week day of the appointment
• [x] Starting hour of the appointment
• [x] Appointment duration
• [x] First time or recurrent appointment
• [x] Time between creating and appointment time
• [x] Time between confirmation and the appointment time
• [x] Clinic
• [x] Source of the appointment (call center, online platform)
• [x] If appointment was moved
• [x] If confirmation message was sent to patient

Patient information

• [x] Age
• [x] Sex

Medical features

• [x] Doctor
• [x] Medical specialty

Label

• [x] Final status of the appointment

Other features

• Weather (Note: challenging feature; hard to extract)
• If the patient has past no-shows (Note: challenging feature; hard to extract)
• Purpose of the appointment (procedure or checkup) (Note: complicated feature to add; not standard)
• mobile vs landline (Note: since 2019 there no difference between mobile and landline; both are 10 digits long)
• Number of times the patient has moved the appointment (Note: complicated feature to obtain)
• Absence of phone number - (Note: almost all patients have phone number)

[robperch]

Hey @jefwei, could you help me out with the following?

I'll send you a dataframe as a pickle through Notion (pivot_df.pkl) containing the matadata from all the appointments from 2022 and 2023. Could you join this dataframe with an extraction of all the appointments from 2022 and 2023? I would suggest you get these with the following query:

SELECT cita.citaid as appointment_id,
       citafecha as appointment_date,
       citahorad as appointment_start_time,
       citahorah as appointment_end_time,
       citaestado as appointment_status,
       u.usuarionomfull as doctor,
       e.especialidadnom as medical_specialty,
       su.sucursalnom as clinic,
       se.servicionom as service,
       p.pacienteid as patient_id,
       p.pacientefnac as patient_birth_date

FROM cita

 INNER JOIN usuario u ON cita.citadoctorid = u.usuarioid
 INNER JOIN servicio se ON cita.servicioid = se.servicioid
 INNER JOIN especialidad e ON u.usuarioespecialidadid = e.especialidadid
 INNER JOIN sucursal su ON cita.citasucursalid = su.sucursalid
 LEFT JOIN paciente p ON cita.pacienteid = p.pacienteid

WHERE citafecha >= '2022-01-01'
  AND citafecha <= '2023-12-31'

;

This merge is intended to be our final data source from the system.

Could you also save this resulting dataframe as a pickle and send it to me?

These are some code snippets to solve saving and loading pickles:

## Saving df as pickle and storing it locally
path = '../../pkg_dir/data/pickles/robs'
name = 'pivot_df.pkl'

pickle.dump(
    dfp,
    open(
        os.path.join(path, name),
        'wb'
    )
)

## Saving df as pickle and storing it locally
path = '../../pkg_dir/data/pickles/robs'
name = 'pivot_df.pkl'

## Reading extract object saved as pickle locally
pkl_obj = path + "/" + name

with open(pkl_obj, 'rb') as obj_content:
    dfp = pickle.load(obj_content)