I have longitudinal patient data in R. I would like to create an aggregate table like table 2 below from table 1. so Table 2 would only have one row for each patient and have total counts of consultations before the registration date (column 3 in table 1) and total consultations after the registration date

Table1:

| patid | consultation\_date | registration\_date | consultation\_count |
| --- | --- | --- | --- |
| 1 | 07/07/2016 | 07/07/2018 | 1 |
| 1 | 07/07/2019 | 07/07/2018 | 1 |
| 1 | 07/07/2020 | 07/07/2018 | 1 |
| 2 | 14/08/2016 | 07/09/2016 | 1 |
| 2 | 07/05/2015 | 07/09/2016 | 1 |
| 2 | 02/12/2016 | 07/09/2016 | 1 |

Table 2:

| patid | consultation\_count\_pre\_registration | consultation\_count\_post\_registration |
| --- | --- | --- |
| 1 | 1 | 2 |
| 2 | 2 | 1 |