join two tables, one with header one without header

[alperyilmaz]

Describe the bug
Hi, I often need to join two tables where one table is a csv/tsv file with header. But the second file is generated by terminal command, thus does not have header. So, I want to join adhoc, headerless tables with csv/tsv files.
The problem is, since second file does not have a header, I use numbers to indicate column number and I get strange result regarding first line of right table.

To Reproduce
Steps to reproduce the behavior:

1. use the following tables

$ cat jointest6.csv
gene_id,tissue
gene1,liver
gene2,lung
gene3,brain

$ cat jointest7.csv
gene1,0.004
gene2,0.01
gene3,0.005

2. Run the following command:

cat jointest7.csv | qsv join gene_id jointest6.csv 1 -

The result is

gene_id,tissue,gene1,0.004
gene2,lung,gene2,0.01
gene3,brain,gene3,0.005

which is wrong.

Expected behavior
The expected behavior/output is either put NA as no headers, or drop the headers completely:

gene_id,tissue,NA,NA
gene1,liver,gene1,0.004
gene2,lung,gene2,0.01
gene3,brain,gene3,0.005

OR

gene1,liver,gene1,0.004
gene2,lung,gene2,0.01
gene3,brain,gene3,0.005

Desktop (please complete the following information):

• OS: Pop OS 22.04 LTS
• qsv Version 0.122.0

[13minutes-yt]

qsv select -n 1- join7.csv |qsv join -n 1 join6.csv 1 -

[ondohotola]

And add a rename to the pipe perhaps

[alperyilmaz]

oh my, I thought using "join -n" would mess up if there's actually header in one of the files.. thanks for the reply.

[jqnatividad]

Transferred this to Discussions, should somebody else have the same question... thanks @13minutes-yt and @ondohotola for jumping in!

[ondohotola]

So you want something like

qsv rename -n 'gene_id,value' jointest7.csv | qsv join gene_id jointest6.csv 1 -|qsv select 1,2,4

perhaps, which results in something useful:

gene_id,tissue,value
gene1,liver,0.004
gene2,lung,0.01
gene3,brain,0.005