Paste single-column files with same name using directory name as column name

Question

I have a number of text files that have the same name. Each file is saved in a different folder also each file contains one column of numbers as follow:

FILE.TXT   FILE.TXT   FILE.TXT   FILE.TXT   ....

5            4              5            7
8            2              1            5
6            1              1            1
1            3              5            9
3            1              8            9
.           .                 .          .
.           .                 .          .
.           .                 .          .               

I want to merge the files in one spreadsheet (CSV format) and I want the names of the columns to be the same as the names of the folders that contain that file. I tried for loop as follow:

#!/bin/bash
  in=a/b/c
  for i in $(cat $in/folders_names.txt); do    # i is the folder name that contain the file.txt
  paste ${in}/${i}/file.txt         
   done > all_files.txt
   sed 's/  */,/g' all_files.txt >all_files.csv &

This code is pasting all the columns ( from all the files ) in one column ( in the file all_files.txt). I don't know what I am doing wrong. Any suggestions?

Answer 1

{ paste -d,  /dev/null "${in}"/folders_names.txt | tr -d \\n | cut -c2-; \
sed 's|.*|'"${in}"'/&/file.txt|' "${in}"/folders_names.txt \
| tr \\n \\0 | xargs -0 paste -d,; } > all_files.csv

The first command

paste -d,  /dev/null "${in}"/folders_names.txt | tr -d \\n | cut -c2-

prints the header, e.g. if your "${in}"/folders_names.txt is:

w
x
y
z

it prints w,x,y,z

The sed command processes the same file so that each line becomes a path e.g. if in=a/b/c:

a/b/c/w/file.txt
a/b/c/x/file.txt
a/b/c/y/file.txt
a/b/c/z/file.txt

and the result is transformed into a null separated input fed to paste via xargs -0 so the final output is e.g.

w,x,y,z
5,4,5,7
8,2,1,5
6,1,1,1
1,3,5,9
3,1,8,9

If no line in your folders_names.txt contained blanks (i.e. sane file names) you could just run:

{ paste -d,  /dev/null "${in}"/folders_names.txt | tr -d \\n | cut -c2-; \
paste -d, $(sed 's|.*|'"${in}"'/&/file.txt|' "${in}"/folders_names.txt); } > all_files.csv

as the second command would expand to

paste -d, a/b/c/w/file.txt a/b/c/x/file.txt a/b/c/y/file.txt a/b/c/z/file.txt