Pick columns from a variable length csv file

Question

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.

Answer 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

Answer 2

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