HCP engagement with Medical Inquiry and Medical Inquiries Response
Requirement
Introduction: To analyze and enhance HCP (Healthcare Professional) engagement by processing medical inquiries and their corresponding responses. The goal is to assign engagement scores based on the urgency of inquiries and the nature of feedback received, then summarize the engagement performance of assigned representatives.
Requirements:
* Join the volume of medical_inquiry.csv file with medical_inquiry_response.csv using 'Inquiry_ID'.
Add a new column, Engagement Score, which is determined based on urgency and feedback*.
Scoring Criteria:
Urgency Score:*
* High urgency → 5 points*
* Medium urgency → 3 points*
* Low/Other urgency → 1 point*
Feedback Score:*
* Positive feedback → 3 points*
* Neutral feedback → 1 point*
* Negative/Other feedback → 0 points*
Total Engagement Score = Urgency + Feedback*
Aggregation:
* Group the data by Assigned_Rep.
Calculate the count of inquiries and the average engagement score* per Assigned_Rep.
Final Output:
Display the joined table and show all columns along with the calculated Engagement Score*.
Provide a summary report showing the count of inquiries and average engagement score* per Assigned_Rep.
Write Pyspark logic without the syntax error.
File path: /Volumes/agilisium_playground/purgo_playground/claims/medical_inquiry.csv
File path: /Volumes/agilisium_playground/purgo_playground/claims/medical_inquiry_response.csv