Bash shell scripting - Appending a column of values in a csv file to another existing csv file

Question

I am trying to append one column of data in a csv file (let's call it file2) with another csv file (let's call it file1). csv files are tab delimited.

For example, file1:

Second	rateself
1	2
2	4
3	6
4	50
5	44
6	47
7	49
8	50

and file2:

Second	rateoth
1	44
2	43
3	37
4	20
5	50
6	55
7	61
8	66

I'm trying to use bash shell scripting to automate and combine date. The desired data:

desired output:

Second	rateself	rateoth
1	2	44
2	4	43
3	6	37
4	50	20
5	44	50
6	47	55
7	49	61
8	50	66

I've tried a slew of cat, awk, paste, and sed statements. I am new to scripting and do not have all the exact errors from each of my attempts; actually many have not had any errors, but simply not given me my desired output. Generally, the cat, paste, and sed statements have resulted in the data being combined, but by adding additional rows not the desired column. I have been unsuccessful with the rest, where my output file is blank.

tried and unsuccessful:

$join -t file2.csv file1.csv > combined.csv
$printf "%s/n" "file2.csv" | paste -d \t file1.csv > combined.csv
$paste -d <(cut -d \t -f3 file2.csv) file1.csv > combined.csv
$cat file1.csv <(tail -n +2 file2.csv) > combined.csv
$awk -F "\"*,\"*" '{print $1}' file2.csv > file1.csv
$paste -d file1.csv file2.csv > combined.csv
$cat -d file1.csv file2.csv > combined.csv

any solutions? i'm on a MacOS.

Thank you! I've tried join 5041.1_self_trimmed_cut.csv 5041.1_other_trimmed_cut.csv > 5041.1_combined.csv but my output that's created is empty. any other suggestions? — rsicil
– rsicil, Commented Mar 8, 2022 at 0:09
please update the question with the commands you've tried so far along with the (wrong) output generated by those commands; also update the question to show the complete set of expected output corresponding to the sample inputs (as currently written a cursory read of the question might lead someone to think you only want to match 4 sets of rows) — markp-fuso
– markp-fuso, Commented Mar 8, 2022 at 0:55

Zak · Accepted Answer · 2022-03-08 01:27:11Z

1

Here is a small script that would take the 2 files and combine them into 1. This assumes you have:

file_1.csv with Second and rateself
file_2.csv with Second and rateoth

#! /bin/bash
echo $'Second\trateself\trateoth' > file_3.csv

while read -r col_1 col_2
    do

    # ignore header line of file 2
    col_plus_1=$(( col_1 + 1 ))

    # read line number col_plus_1 into file_2 variable
    file_2=$(awk "NR == $col_plus_1" file_2.csv)

    # separate the tabbed line into array
    file_2=($file_2)

    # assign array indexed 1 to col_3
    col_3=${file_2[1]}

    # write to final file
    echo $'$col_1\t$col_2\t$col_3' >> file_3.csv

# ignore the header on read in
done < <(tail -n +2 file_1.csv)

answered Mar 8, 2022 at 1:27

Zak

7,5812 gold badges29 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rsicil Over a year ago

Thank you! I'm getting an error with this: ")syntax error: invalid arithmetic operator (error token is " but it is getting me the correct header in my output csv file: Second rateself rateoth

Zak Over a year ago

$(( $a + $b )) is the typical arithmetic operator for bash .. You will have to find whatever works for that line in Mac Os You can try $(($col_1+1))

Collectives™ on Stack Overflow

Bash shell scripting - Appending a column of values in a csv file to another existing csv file

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related