merging files in a one dataframe

Question

I have almost 160 txt files, each file has 7 columns and now i want to put that in one file. Each files 1st and 6th column data (Geneid, Length) is same so i just want that to be the 1st and 2nd column in the final file, and from all the files extract the 7th column data (sample1, sample2, sample3.....) and put in the final file.

Example:

Sample 1:

Geneid              Chr     Start   End Strand  Length  Sample1
ENSG00000223972.5   Chr1    34554   12227   ++  1735    0
ENSG00000227232.5   Chr2    14404   1450    ++  1351    12
ENSG00000278267.1   Chr3    17369   17436   ++   68     20
ENSG00000243485.4   Chr4    29554   3003    ++  1021    0
ENSG00000237613.2   Chr5    14404   35174   ++  1219    0

Sample 2:

Geneid              Chr    Start    End Strand  Length  Sample2
ENSG00000223972.5   Chr1    11869   1450    ++  1735    180
ENSG00000227232.5   Chr2    14404   17436   ++  1351    40
ENSG00000278267.1   Chr3    17369   17436   ++   68     9
ENSG00000243485.4   Chr4    14404   3003    ++  1021    49
ENSG00000237613.2   Chr5    17369   3003    ++  1219    3

Sample 3:

Geneid              Chr    Start    End Strand  Length  Sample3
ENSG00000223972.5   Chr1    17369   1450    ++  1735    11
ENSG00000227232.5   Chr2    29554   17436   ++  1351    156
ENSG00000278267.1   Chr3    34554   3003    ++  68      56
ENSG00000243485.4   Chr4    14404   3003    ++  1021    89
ENSG00000237613.2   Chr5    17369   35174   ++  1219    23

Final output should be

Geneid            Length    Sample1 Sample2 Sample3
ENSG00000223972.5   1735    0       180      11
ENSG00000227232.5   1351    12      40      156
ENSG00000278267.1   68      20      9       56
ENSG00000243485.4   1021    0       49      89
ENSG00000237613.2   1219    0       3       23

Answer 1

Using awk with paste (for the revised question).

awk '{printf("%s\t%s", $1, $6); 
    for(i=7;i<=NF;i+=7) printf ("\t%s", $i); printf "\n"}' <(paste files) >final_file

P.s: Change printf formatting controls based on your fields actual value.

Explanations:

• {printf ("%s\t%s" ,$1, $6)} printing 1^st and 6^th columns first.

• for(i=7;i<=NF;i+=7) printf ("\t%s", $i); Then with this we are looping over rest fields and printing only 7^th column and every field with module of 7.

• First we are pasting all the files together then pass it to awk. files means you can feed paste with multiple files name like myfiles* which those all files starts with myfiles followed by anything.