Concatenate files by table values

Question

I have a number of files each containing a specific pattern in their names like ABC1234001 which carry information about certain groups of my data (tables of multiple columns). I also have a table info.tsv like this:

group1    ABC1234001    ABC1234010
group2    ABC1234011    ABC1234018
group3    ABC1234019    ABC1234028
...       ...           ...

It contains:

• "group" column, which specifies the group,
• "first file" column, which specifies pattern for the first file (alphabetical order) containing info for the corresponding group,
• "last file" column, which specifies pattern for the last file (alphabetical order) containing info for the corresponding group.

So what I need to do is combine files for each group into one file - just like

cat ABC123401{1..8}* >> group2.tsv

would be for group2 as an example - while reading this info.tsv file. In this given example all files (ABC1234011.tsv, ABC1234012.tsv, ABC1234013.tsv, ABC1234014.tsv, ABC1234015.tsv, ABC1234016.tsv, ABC1234017.tsv, ABC1234018.tsv) are concatenated into a group2.tsv file

What I'm going to do is as follows:

while read $file; do
  #assign columns to variables like $1="group", $2="firstfile", $3="lastfile"
  cat *{$2..$3}* > $1.tsv;
done < info.tsv

But I'm not quite sure how to iteratively change variables for this approach. Maybe using awk is more useful, but I don't know. The script should produce a bunch of files called group1.tsv, group2.tsv, that contain the contents of corresponding files from "first file" to "last file" in table. Please help me to write the script to do so.

Answer 1

The script below assumes that all files that you may want to concatenate matches the pattern *.tsv. If you know that they all match ABC*.tsv, then you may want to use that pattern at the start of the script in place of *.tsv.

The script furthermore assumes that all names of files that goes into a specific group are generated as a continuous sub-list of the list that *.tsv expands to.

#!/bin/sh

set -- *.tsv

while read -r group first last; do
        collect=false

        for name do
                if ! "$collect"; then
                        [ "$name" = "$first.tsv" ] || continue
                        collect=true
                fi

                if "$collect"; then
                        cat -- "$name"
                        [ "$name" = "$last.tsv" ] && break
                fi
        done >"$group.tsv"

done <info.tsv

The script sets the list of positional parameters to the list of names matching *.tsv. It then reads the three fields of each line from info.tsv into the variables group, first and last.

For each line read from info.tsv in this way, the list of positional parameters is scanned for names matching the first name in the group. Once this first name is found, we set a flag, collect, that tells the logic of the script to start collecting the data from the files named in the list of positional parameters, from the current position in the list. This ends once we come across a name that corresponds to the last name of a group.

Note that true and false here are being used as commands and not simple strings. The value stored in the variable $collect is being executed in if ! "$collect" so that means the script will run one of the two shell builtin commands true or false. The shell doesn't have any special keywords for true or false the way some other languages (e.g. Python) do.

Testing:

$ ls
script

$ touch ABC{1234001..1234030}.tsv
$ for name in ABC*.tsv; do printf 'Name: %s\n' "$name" >"$name"; done
$ cat ABC1234015.tsv
Name: ABC1234015.tsv

$ cat >info.tsv <<END_DATA
group1 ABC1234001 ABC1234010
group2 ABC1234025 ABC1234030
END_DATA

$ ./script
$ cat group1.tsv
Name: ABC1234001.tsv
Name: ABC1234002.tsv
Name: ABC1234003.tsv
Name: ABC1234004.tsv
Name: ABC1234005.tsv
Name: ABC1234006.tsv
Name: ABC1234007.tsv
Name: ABC1234008.tsv
Name: ABC1234009.tsv
Name: ABC1234010.tsv
$ cat group2.tsv
Name: ABC1234025.tsv
Name: ABC1234026.tsv
Name: ABC1234027.tsv
Name: ABC1234028.tsv
Name: ABC1234029.tsv
Name: ABC1234030.tsv

---

As mentioned in comments to this answer, the way I woulddevelop this script for my own personal use would be to leave the script looking like this:

#!/bin/sh

while read -r group first last; do
        collect=false

        for name do
                filename=$( basename "$name" )

                if ! "$collect"; then
                        [ "$filename" = "$first.tsv" ] || continue
                        collect=true
                fi

                if "$collect"; then
                        cat -- "$name"
                        [ "$filename" = "$last.tsv" ] && break
                fi
        done >"$group.tsv"

done

Note the deletion of the set command at the top (this will be replaced by command line arguments), and the deletion of the redirection from info.tsv (this will be replaced by a redirection on the command line). I have also introduced a filename variable that will hold the filename component of the pathnames given on the command line.

I would then run the script like so:

$ ./script ABC*.tsv <info.tsv

What I have achieved with this is a script that is agnostic to where the input group list is stored or what it's called, and that does not care what the ABC files are called (as long as they have a .tsv filename suffix) or where they are stored.

Answer 2

Your approach is a good idea, but unfortunately it won't work because variables are not expanded inside brace expansions:

$ echo {1..5}
1 2 3 4 5
$ a=1
$ b=5
$ echo {$a..$b}
{1..5}

You can get around this by using eval though:

sed 's/ABC//g' info.tsv | 
    while read -r group start end; do 
        files=( $(eval echo ABC{$start..$end}.tsv) )
        cat "${files[@]}" > "$group.tsv"; 
    done 

This will first remove all instances of ABC from the info.tsv file so we can get the numbers alone. Note that this assumes the exact data structure you have shown us. If ABC can also be present in the group name, then this will break.

After removing ABC, the result is piped into the while loop which reads three variables: $group, $start and $end. These are then passed to eval which will expand the variable before calling the brace expansion, allowing you to get a list of file names:

$ eval echo ABC{1..5}
ABC1 ABC2 ABC3 ABC4 ABC5

The result of the eval is stored in the $files array, which is passed as input to cat:

cat "${files[@]}" > "$group.tsv";

Answer 3

If I understand you correctly here's an option

$ while IFS= read -r i; do
    f=$(echo "$i" | cut -d' ' -f1)
    cat $(echo "$i" | cut -d' ' -f2- | sed -E 's/([0-9])\s+/\1.tsv /;s/([0-9])$/\1.tsv /') > "$f.txt"
  done < info.tsv

---

• f=$(echo "$i" | cut -d' ' -f1) retrieves the name of the group.
• cat $(cut -d' ' -f2- | sed -E 's/([0-9])\s+|([0-9])$/\1.tsv /g') concatenates the list of files in the line.