13

file1.csv

A,,C,D
A,,C,D
A,,C,D
A,,C,D

file2.csv

A,B
A,B
A,B
A,B

desired Output.csv

A,B,C,D
A,B,C,D
A,B,C,D
A,B,C,D

I've tried using "join" and "paste" to no avail. Is there a bash command to do this? Column "A" is the same in both .csv files.

3
  • So you are asking to copy column B to File 1? Or columns C and D to file2?
    – Tim
    Nov 24 '14 at 14:20
  • Either way would be fine as long as the output matches "desiredOutput.csv" Nov 24 '14 at 14:21
  • I have added a new answer which, I think, is easier than all the other answers (my first answer included). You might want to consider accepting that so that for future reference the information is easily found.
    – don.joey
    Nov 27 '14 at 13:41
12

With only awk command:

awk -F, '{getline f1 <"file2" ;print f1,$3,$4}' OFS=, file1

Get a line from file1 and store it into local variable f1, then print the line that stored in f1 and finally print the third($3) and forth($3) fields from file1 which delimited with comma , altogether, and change the OFS(output field separator [space by default]) to comma(,).


The short command would be like this:

paste -d, file2 <(cut -d, -f3- file1)
 A,B,C,D  
 A,B,C,D  
 A,B,C,D  
 A,B,C,D  

paste the file2, then cut and paste the third column to the next(-f3-) from file1.


With awk and paste (option A)

Below command also copies the last two columns (C,D) from file1 at the end of each line in file2:

paste -d',' file2  <(awk -F',' '{print $(NF-1)","$NF}' file1)

Above command paste the file2 content then print a comma delimiter(-d',') then paste the two last field(NF is the index of last field and $NF is the string which its index is NF. So $(NF-1) is the second field before last field ) from file1 when those index redefines or splits with comma spectator(-F',').

With awk and paste (option B)

This command also is the same as above($3 and $4 points to third and forth field of each line from file1 ):

paste -d',' file2  <(awk -F',' '{print $3","$4}' file1)

Or another solution with cut command:

paste -d, <(cut -d, -f1 file1) <(cut -d, -f2 file2) <(cut -d, -f3- file1)

cut command in above command first cut the first field(-f1 which indexed with comma delimiter(-d.)) from file1(cut -d, -f1 file1), then cut and paste the second field of file2(cut -d, -f2 file2) and finally cut and paste the third column(-f3) to the nexts(-) from file1(cut -d, -f3- file1) again.

This command also returns the same result:

paste -d, <(awk -F',' '{print $1}' file1) <(awk -F',' '{print $2}' file2) <(awk -F',' '{print $3","$4}' file1)

paste the second field from file1(awk -F',' '{print $1}' file1) then print a comma(-d,), then paste the second column from file2(awk -F',' '{print $2}' file2), finally paste the second and last column of file1(awk -F',' '{print $3","$4}' file1) again.

1
9

Here's a beauty (I think):

join -t, <(csvcut -c 1,3,4 file1.csv) <(csvcut -c 1,2 file2.csv)

Broken down in steps:

Step 1. Install csvkit:

sudo pip install csvkit
sudo apt-get install python-dev python-pip python-setuptools build-essential

Step 2. Use the join command with a comma as separator

join -t,

Step 3. Feed it the actual columns you want to. Note how you feed it the first column twice, because that is the one the join is actually performed on (default behavior of join).

join -t, <(csvcut --columns 1,3,4 file1.csv) <(csvcut --columns 1,2 file2.csv)

or in shorthand:

join -t, <(csvcut -c 1,3,4 file1.csv) <(csvcut -c 1,2 file2.csv)

You can redirect that standard output to a file (desiredOutput) if wanted.

Advantages

This method has several advantages over the others proposed.

First and foremost: it performs a real join. That means that it can be used for more complex data as well. It is very easy to do a join on another field, for instance. It does not simply look at the position of the field, but it really takes the column into consideration. It actually works with the format of the data (csv) and does not treat it like text.

Second, it uses the very powerful csv toolkit which also allows you to a) display statistics with one command (csvstats), b) check whether the data is clean (csvclean), but also to transform it into json, into sql, or even load it into python! This toolkit is heavily used in data science for data preparation.

8
  • If you are installing on Ubuntu you may need to install the Python development headers prior to installing csvkit: sudo apt-get install python-dev python-pip python-setuptools build-essential -- link
    – karel
    Nov 24 '14 at 16:42
  • Awesome answer as well, I'm working on a company server so installing stuff takes about a week of paper work. Works on my machine though! Nov 24 '14 at 18:10
  • +1 for showing me another tool for CSV data. Separate question, but do you know of a standalone report writer for CSV data files?
    – Joe
    Nov 27 '14 at 0:38
  • @Joe can you be a more specific about what you mean when you speak about a 'report writer'? I am not sure I understand what you mean.
    – don.joey
    Nov 27 '14 at 9:03
  • I posted a separate question at unix.stackexchange.com/questions/170199/…
    – Joe
    Nov 27 '14 at 11:18
8

Here is another beautiful one. I think it is the easiest of all suggestions, thus far.

csvtool pastecol 2 2 file1.csv file2.csv

If you have not installed csvtool already in the past, you have to sudo apt-get install csvtool.

From the docs:

pastecol <column-spec1> <column-spec2> input.csv update.csv

Replace the content of the columns referenced by in the file input.csv with the one of the corresponding column specified by in update.csv.

Example:

  csvtool pastecol 2-3 1- input.csv update.csv.csv > output.csv

Note how in our case we are replacing the second columns of the files.

Examples

file1.csv

A,,C,D
A,,C,D
A,,C,D
A,,C,D

file2.csv

A,B
A,B
A,B
A,B

Combining the two files:

csvtool pastecol 2 2 file1.csv file2.csv
A,B,C,D
A,B,C,D
A,B,C,D
A,B,C,D

What you essentially do is paste the column two of file2.csv as column 2 in file1.csv.

Note that this also works on the same document. If you want to swap two columns, you can do so by using the same file as input.csv and update.vsc.

csvtool pastecol 2 1 file2.csv file2.csv 
A,A
A,A
A,A 
A,A
1
  • No doubt the most elegant. Nov 27 '14 at 15:09
2

To move a chosen number of columns from one file to another:

#!/usr/bin/env python3

cols = 1; file_1 = "/path/to/file_1"; file_2 = "/path/to/file_2"

def readfile(file):
      with open(file) as src:
          return [item.strip().split(",") for item in src.readlines()]

file_1 = readfile(file_1); file_2 = readfile(file_2)

for i in range(len(file_1)):
    print((",").join(file_1[i]+file_2[i][-cols:]))

from two files:

file_1

A,B
A,B
A,B
A,B

file_2

K,L,M
K,L,M
K,L,M
K,L,M

When you set cols = 1:

A,B,M
A,B,M
A,B,M
A,B,M

But When you set cols = 2:

A,B,L,M
A,B,L,M
A,B,L,M
A,B,L,M

cols = 3:

A,B,K,L,M
A,B,K,L,M
A,B,K,L,M
A,B,K,L,M

How to use

Copy it into an empty file, set the path to file1, file2 and the number of columns to move, save it as move.py and run it by:

python3 /path/to/move.py

It is also possible to add one or more columns from the middle of the source file's colums this way.

2
  • Would have loved to see you use import csv though.
    – don.joey
    Nov 24 '14 at 18:02
  • @don.joey Thanks for the suggestion, will definitely look into it. Nov 24 '14 at 19:40
0

Another method in python through csv module.

script.py

#!/usr/bin/python3
import csv
import sys
file1 = sys.argv[1]
file2 = sys.argv[2]
with open(file2, 'r') as r:
    with open(file1, 'r') as f:
        csv_f = csv.reader(f)
        csv_r = csv.reader(r)
        bar = [linex for linex in csv_r]
        foo = [liney[2:] for liney in csv_f]
        zipped = zip(bar,foo)
        result = [x+y for (x,y) in list(zipped)]
        for i in result:
            print(','.join(i))

To run the above script,

python3 script.py file1 file2

Output:

A,B,C,D
A,B,C,D
A,B,C,D
A,B,C,D

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.