My data is currently in excel spreadsheets.
After gathering all the data needed for the 12 months of the year, i.e. 'Frequent Monthly Routes' I am looking to consolidate all this data into one table that depicts the 'Annual Top Routes' that contains all the same data points as the 'Frequent Monthly Routes' for both members and casual users, depicting the annual totals. Currently, all the monthly data (for both members and casuals) are in separate Excel Workbooks organized by month and are displayed in Pivot Tables. What I thought to do, in considering the best method in extracting the top annual routes by users, is to create a separate workbook that organizes the data by user status in two separate sheets i.e. casual user data in one sheet and member user data in another. The data in both respective sheets will be organized by month. What I need to do is simply extract the totals listed throughout the months of the year to solve for the top annual routes. Let me share a snapshot of what I began to create but not quite finished:
Only the months for December, January is currently there. As you can see this sheet shows only the casual user data and is listed by month. The data displayed are the monthly (Dec & Jan) totals for frequented routes. I need to find any matching route_names and tally up the totals to create my annual dataset. For example, as you can see the route 'Michigan Ave & 18th St to Michigan Ave & 18th St' is repeated between Jan & Dec. I would like to figure out a way to implement a count to tally up all matching route_names and their respective data and be able to implement this across all 12 months once listed in the sheet.
If you don't understand what it is I am trying to accomplish I have included a short Loom video explaining what I am trying to accomplish.
Would it make more sense to upload this data into an SQL server to implement code to get totals or how would I implement this with R?
