To begin I recommend you to have all data inside a text/plain
file, where as you wrote, all data is tab delimited
.
Then you can try to experiment how to filter the columns using cut
Example
#get first column of a tab delimited file
> cut -f 1 -d $'\t' file
After that, I recommend you to install a database engine, as for example mysql-server
and a workbench as mysql-workbench
.
Then if you want, I can help you to build an indexed database with some insert functions, which will help you to insert, get and analyze all data you need now and in the future with ease.
Other choice
Is to change the extention of that text/plain
file to .csv
and open it with LibreOffice Calc
. After opening it, select the tabulator as delimiter. You will be able to analyze the data using pivot tables
, but I am not sure how can you achieve the output you want.
Bash solution
First, let's make a new directory to work with.
> mkdir test
Then copy your source file to that new directory.
> cp source test/file
Then enter to the directory
> cd test
Now, remove the first line of the file (the column names row)
> nano file
press ctrl+k, ctrl+x and y
Then sort the file
> sort file > file.sort
Get all column names
> cut -f 2 -d $'\t' file > cols
Make a directory for columns
> mkdir c
Split all data by columns (ignore errors)
> while read i ; do grep "$i" file | cut -f 1,3 -d $'\t' > "c/$i" ; done < cols
Join all data and delete repeats
> cut -f 1 -d $'\t' file.sort > result
> for f in c/* ; do join result "$f" > tmp ; join -v 1 result "$f" | sed -e 's/$/ -/g' >> tmp ; sort tmp > result ; done
> uniq result