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:

NameA
Count3
Height5
Weight16

Table n:

NameB
Count20
Height8
ColourBlue

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:

NameANameB
Count3Count20
Height5Height8
Weight16ColourBlue

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:

​

NameAB
Count320
Height58
Weight16
ColourBlue

Edits: Added second table (n) and combinations to better illustrate my problem.

Posted Nov 17, 2022, 11:01 PM UTC · edited

17
Loading comments…