We get groundwater depth data from data loggers as a .txt file. The format of this data not changeable. It has consistent column names except that the first column name contains the well id. For each well, the data is structured:
"well 02,Time,Current(feet),Serial Number
"Well 17,Time,Current(feet),Serial Number
I am trying to create a script that merges csv .txt files from multiple into one long data frame. I only need column 2 and 3 from these files. I also need to add an id column noting which well the observation came from. In the end, I would like the to have a 3xn df with colnames = well_id, obs_date, observation
Note that the file name also contains the well id.
txt_list <- list.files(pattern = ".txt", full.names = TRUE)
txt_read_csv <- map(txt_list,
col_names = TRUE,
col_types = "iTdc"
...gets me a list of tibbles. But I cannot figure out how to select and mutate tibbles within a list to reduce the columns and assign an ID. I tried map_df, but since the first column name is different in each .txt the df explodes into a wide table with multiple columns for each different colname.
I can do this with a loop, but I'd really like to keep it tidy.
Thanks for any help.