I have a table with approximately 2000 entries that contains names, positions, field of expertise, and addresses of professors. The table is quite messy, and I’m struggling to find a programmatic way to transform and pivot it into a tidy format.
My goal is to create a tidy table that includes at least the following columns: names, positions, field of expertise, and email address.
Here is a sample of the data:
| data | field |
|---|---|
| Person 1, MD | A |
| Associate Professor of A | A |
| Program Associate, A | A |
| Senior Medical Director, FGP Operations | A |
| UMMG Ambulatory Surgery | A |
| Residency Program | A |
| Core Educational Lead | A |
| [email protected] | A |
| NA | A |
| Person 2, MD | B |
| Person 2 | B |
| Clinical Assistant Professor of B | B |
| Medical Student Clerkship and Core Educational Lead | B |
| [email protected] | B |
| NA | B |
| labcoat | B |
| Person 3, MD | B |
| Clinical Assistant Professor of B | B |
| [email protected] | B |
| NA | B |
| labcoat | B |
| Person 4, MD | B |
| Professor of B | B |
| Professor of B | B |
| [email protected] | B |
| NA | B |
| Person 5, MD | C |
| Person 5 | C |
| Professor of C | C |
| Professor of C | C |
| Associate Chair, Quality | C |
| Department of Urology and Service Chief | C |
| [email protected] | C |
| 132-547-1321 | C |
| NA | C |
Here is the tibble code (to reproduce):
tibble::tribble(
~data, ~field,
"Person 1, MD", "A",
"Associate Professor of A", "A",
"Program Associate, A", "A",
"Senior Medical Director, FGP Operations", "A",
"UMMG Ambulatory Surgery", "A",
"Residency Program", "A",
"Core Educational Lead", "A",
"[email protected]", "A",
NA, "A",
"Person 2, MD", "B",
"Person 2", "B",
"Clinical Assistant Professor of B", "B",
"Medical Student Clerkship and Core Educational Lead", "B",
"[email protected]", "B",
NA, "B",
"labcoat", "B",
"Person 3, MD", "B",
"Clinical Assistant Professor of B", "B",
"[email protected]", "B",
NA, "B",
"labcoat", "B",
"Person 4, MD", "B",
"Professor of B", "B",
"Professor of B", "B",
"[email protected]", "B",
NA, "B",
"Person 5, MD", "C",
"Person 5", "C",
"Professor of C", "C",
"Professor of C", "C",
"Associate Chair, Quality", "C",
"Department of Urology and Service Chief", "C",
"[email protected]", "C",
"132-547-1321", "C",
NA, "C"
)