I have a list of data showing dates of hospital visits alongside patient ID numbers, data was originally a pandas dataframe which I wrote to a CSV file which now looks similar to this:
| Patient | Date |
|---------|------------|
| 2 | 17/08/2005 |
| 2 | 07/03/2006 |
| 2 | 27/08/2008 |
| 2 | 22/09/2010 |
| 2 | 20/09/2011 |
| 2 | 01/10/2012 |
| 3 | 13/03/2006 |
| 3 | 12/09/2006 |
| 3 | 12/09/2007 |
| 4 | 18/08/2005 |
| 4 | 03/05/2006 |
| 4 | 25/11/2008 |
| 5 | 23/08/2005 |
| 5 | 09/03/2006 |
| 5 | 06/09/2006 |
I want to change the date column to show number of days since that individual patients' first visit, so output will look something like this for the above data -
| Patient | Days |
|---------|------|
| 2 | 0 |
| 2 | 202 |
| 2 | 1106 |
| 2 | 1862 |
| 2 | 2225 |
| 2 | 2602 |
| 3 | 0 |
| 3 | 183 |
| 3 | 548 |
| 4 | 0 |
| 4 | 258 |
| 4 | 1195 |
| 5 | 0 |
| 5 | 198 |
| 5 | 379 |
Is there an easy way to do this using NumPy/Pandas? n.b. the overall dataset has about 100,000 visits.
Eventually, I have a 3rd column (for a test carried out at the hospital), and would like to plot (days since last visit) vs (test result) for ~ 5000 patients, on one graph, each patient with their own line.
| Patient | Days | Test_result |
|---------|------|-------------|
| 2 | 0 | 28 |
| 2 | 202 | 28 |
| 2 | 1106 | 29 |
| 2 | 1862 | 28 |
| 2 | 2225 | 23 |
| 2 | 2602 | 24 |
| 3 | 0 | 25 |
| 3 | 183 | 28 |
| 3 | 548 | 28 |
| 4 | 0 | 24 |
| 4 | 258 | 20 |
| 4 | 1195 | 24 |
| 5 | 0 | 17 |
| 5 | 198 | 19 |
| 5 | 379 | 27 |