shell: combining several tables

Question

Within same workdir I have several 2 column tables (with | separators between the columns) containing the same number of lines and share the order within the first column . Here is an example for two tables containing 8 lines:

table 1.csv

lig10| -5.4600
lig13| -5.9900
lig14| -4.2200
lig15| -4.9200
lig3| -6.9700
lig4| -7.4000
lig5| -6.0000
lig9| -6.5700

table 2.csv

lig10| -5.2400
lig13| -6.6900
lig14| -4.4600
lig15| -4.9600
lig3| -6.9000
lig4| -6.3100
lig5| -3.7900
lig9| -6.5800

I would like to merge all tables in that (quite similar) format together side-by-side into big table, taking all data, so that the resulting table should contain the same number of lines with the number of columns proportional to number of fused filles. E.g. for 10 separate tables it would be 20 column table (note: I always take the column number one, even it is the same for all tables!),etc In the resulting table I would like to add header for each 2nd column (of initial table), according to the name of the initial file. Here is expected output for the 2 tables.

Is it possible to use some linux utility to fuse the tables in terminal in one line command like

prog -i /workdir/*.csv  -o resulting_table.csv

Do you want to ignore column 1 and just join the lines and have different values in column 1 for different files or is the idea to have all lig10 on one line and blanks where this does not exist in any file? What have you tried so far? — bu5hman, Oct 09 '20 at 15:26
I have tried to do it manually in libre office ! :-) this is how the image in my first post was produced.. Ideally yes I would like to take only the first column from the fist table (since it always the same in all the rest tables, including line order) and then to take from each table only the second column, merging it side-by-side .. but if it is difficult we could rather take the both columns from each table and fuse it. I will remove then the repetitions manually. — user3470313, Oct 09 '20 at 15:35
@user3470313 then please [edit] your question and tell us that is what you want. — terdon, Oct 09 '20 at 15:37

terdon · Answer 1 · 2020-10-09T15:43:00.363

That's what paste is for. You might have to work a bit to get the file names right though:

$ ( printf '%s\t' *table*; echo; paste -d'\t' *table*)
name_of_file_with_table1    name_of_file_with_table2    
lig10| -5.4600  lig10| -5.2400
lig13| -5.9900  lig13| -6.6900
lig14| -4.2200  lig14| -4.4600
lig15| -4.9200  lig15| -4.9600
lig3| -6.9700   lig3| -6.9000
lig4| -7.4000   lig4| -6.3100
lig5| -6.0000   lig5| -3.7900
lig9| -6.5700   lig9| -6.5800

If you only want the first column of the first file, it's even easier:

$ ( printf '%s ' *table*; echo; join  *table* )
name_of_file_with_table1 name_of_file_with_table2 
lig10| -5.4600 -5.2400
lig13| -5.9900 -6.6900
lig14| -4.2200 -4.4600
lig15| -4.9200 -4.9600
lig3| -6.9700 -6.9000
lig4| -7.4000 -6.3100
lig5| -6.0000 -3.7900
lig9| -6.5700 -6.5800

If your files aren't sorted, you will need to sort them first:

for f in *table*; do sort "$f" > $f.sorted; done

And then:

( printf '%s ' *table*sorted; echo; join  *table*sorted )

thank you! sorry i did not understand well if it would be possible to use paste just from terminal providing just a path to a folder contained all tables or rather I need to create a script with the list of the filles? — user3470313, Oct 09 '20 at 15:46
@user3470313 you can run paste -d'\t' file1 file2 file3 ... fileN or, if all of your file names share a pattern (e.g. tableFile1, tableFile2, tableFileN) you can pass the pattern: paste -d'\t' tableFile*. — terdon, Oct 09 '20 at 16:05

shell: combining several tables

1 Answers1