After loading and preparing the data I found myself with a list of 12 tibbles (one for each table), and when I bind the rows, I go from 16 distinct rows to 192 (I have from 2012 to 2018...).
I have been thinking that maybe using R isn't the best way to structure the data and I should be using a relational database to avoid repetition, since I'm starting to feel this isn't going to scale well.
ps.- I couldn't think a best way to put the title more clear, so if somebody could suggest a better idea, I'll change it asap.
This is a recurring untidy problem that I'm currently working on for another medical study, which has an analogous collection of data. In that case, 169 unique patients generated a 28,000 row tibble because the data were organized by observation
and so on. This reflected a failure of the researcher's source data (for which his institution was not responsible) to choose a single subject of observation, the patient as rows and variables, the types of observations, as columns.
Your problem may be simpler. If your unit of observation is the institution, I would insert new columns for institution and year. I'll have to assume you have csv files in a folder descending hierarchy by institution by year by observations. After creating the tibble in the form shown in your example
# Create an "empty" tib, `combined` with the fields from your example with just the first line
# mutate to create year=2011 (date or integer) and institution = any string
# filter it to remove the data
combined <- combined %>% filter(year < 2011)
2012_tib <- original_tib %>% mutate(year == 2012, institution = institution_1) %>% row_bind(combined, 2012_tib)
#...
2018_tib <- original_tib %>% mutate(year == 2012, institution = institution_5) %>% row_bind(combined, 2012_tib)
I think that by investing some time in normalizing your data, whether you choose to do it in a database or in R, your are going to save your self a lot of troubles later.
And, yes a relational database design is going to scale much better
Actually, I need to analyze those specific tables, and compare them between months, or by year. Maybe I should create a tibble that is institution + table + specific table (like a tibble per row in my example). Could that work?
Yes, I'm (trying) doing that, but I just found that I'm starting to see a lot of repeated rows, and smelled wrong.
R/dplyr can still connect and work with a SQL database right?
You can use dbplyr for applying dplyr commands on a sql database, and yes, it is possible to nest tibbles inside other tibbles, but if you are going to normalize your data, this should be done in the modeling part of your process not in the wrangling and cleaning part.
There is nothing inherently wrong with that approach though, but I do believe the normalization path pays off better at long term