Looking for an elegant solution to a stupid problem concerning many 2 column .csv files
I'm quite new to R, so please be gentle.
I have a piece of equipment that looks at any number of items and produces a folder full of files for each item. Unfortunately, if I'm looking at patterns across all the items in a test, I have to compile all of the data from across these many folders. My problem happens when I'm combining them.Each file has its data formatted as "criteria" in column 1 and value in column 2, with no header, like this:
Table 1:
| Name | A |
|---|---|
| Count | 3 |
| Height | 5 |
| Weight | 16 |
Table n:
| Name | B |
|---|---|
| Count | 20 |
| Height | 8 |
| Colour | Blue |
The criteria column may or may not contain all of the same fields across files.
My current solution is to build a list of each file to be read ("files"), use lapply(files, read.csv) to make a list containing the contents of each csv, and then bind_cols() to press it all down to one dataframe which results in something like this:
| Name | A | Name | B |
|---|---|---|---|
| Count | 3 | Count | 20 |
| Height | 5 | Height | 8 |
| Weight | 16 | Colour | Blue |
From there I need to remove every other column (since it's the criteria column, which I only need one of), transpose it, select the criteria I actually care about, and export it as a report.csv file.The problems are that, first: It's clunky AF and I hate it. There has to be a better way; and second: if the criteria column doesn't match in length it produces an error, and if it happens to be in the wrong order in some files I would never know (Edit: As above in the bottom row)
I understand that there is a better way of doing this if the criteria are in row 1 and the matching data values in row 2, but transposing each before combining it in this case is cumbersome and seems wrong.
Does anyone have a more elegant way of merging all of these many files by criteria, and adding a new row if there happens to be a different criteria in one file than is in others?
Edit: The above example would ideally look more like this:
| Name | A | B |
|---|---|---|
| Count | 3 | 20 |
| Height | 5 | 8 |
| Weight | 16 | |
| Colour | Blue |
Edits: Added second table (n) and combinations to better illustrate my problem.
Posted Nov 17, 2022, 11:01 PM UTC · edited