1

I have two CSV files, which share a column that is unique to each row in each file, like an ID. The files don't have a header. The file_2 has variable length columns e.g.

1,2,3
4,5,6,7,8

I joined to two filed based on the common column first by sorting with sort command and the using join on that column. Now I have a single csv file with variable length columns.

I want to pick the columns in the following order:

second column, first column, third column, {from 4th column onwards every 3rd column till end of row. e.g., 4,7,10...}

I tried awk -F "\"*,\"*",\"*" '{print $2 $1 $3}' joinedfile.csv

and was able to get those three columns. However no idea to handle the rest. I know how to do this in python. I would like to know how to do this in shell command like cut or awk. I am guessing a while loop in awk might help, but not sure how to construct.

  • Your question is a bit hard to read. I don't understand what you are trying to do. – DarkHeart Jul 6 '15 at 2:24
  • each row in the CSV file has variable number of elements, minimum is 4. I want to get columns 2,1,3 and then depending on how many elements, starting from 4th column get every 3rd columns. An example output row has columns 2,1,3,4,7,10 another row can have columns 2,1,3,4 another 2,1,3,4,7,10,13 – seek Jul 6 '15 at 2:28
1

Something like:

awk -F, '{
    # print first three columns
    printf("%s,%s,%s", $2,$1,$3);

    #for all other columns
    for ( i = 4; i < NF; i++ )
    {
        # if column number every third
        if ( ( i - 4 ) % 3 == 0) {
            printf(",%s", $i);
        }
    }
    #print newline
    print "";
}' your_file.csv
  • you are unbelievable :) worked like a cham. thanks a lot – seek Jul 6 '15 at 3:04
0

Pure shell, should work as long as there's fewer than 26 columns:

while IFS=, read a b c d e f g h i j k l m n o p q r s t u v w x y z
do 
    printf '%s,' $b $a $c $d $g $j $m $p $s $v $y
    printf '\b \b\n' 
done < joinedfile.csv

If there's more than 26 columns, try this:

tr , ' ' < joinedfile.csv | 
while read a; do 
    set -- $a
    printf '%s,' $2 $1 $3
    while [ "$4" ] ; do 
        shift 3
        printf '%s,' "$1"
    done
    printf '\b \b\n'
done

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.