Bdtyktl

Consider this data:

#!/usr/bin/env bash

cat > example_file.txt <<EOL

group, value, price

1, 3.21, 3.21

1, 3.42, 4.11

1, 3.5, 1.22

2, 4.1, 9.2

2, 4.2, 2.11

EOL

I want to move the 'value' column in front:

value, price, group

3.21, 3.21, 1

3.42, 4.11, 1

3.5, 1.22, 1

4.1, 9.2, 2

4.2, 2.11, 2

the problem is: the order of the column (or even the number of column or the name of many of those columns --except 'value' which is always there) varies from file to file. So I have to select the value column by name (not order).

How can I do this?

If you don't mind the value column being duplicated, you could do something like this, with csvtool:

$ csvtool paste <(csvtool namedcol value example_file.txt) example_file.txt 

value,group,value,price

3.21,1,3.21,3.21

3.42,1,3.42,4.11

3.5,1,3.5,1.22

4.1,2,4.1,9.2

4.2,2,4.2,2.11

However as far as I know csvtool won't move (or remove) a namedcol.

If you can't find a specialized CSV tool that will, you can roll your own using a general purpose language such as Awk or Perl. The idea would be to search the fields of the first row for the index of the matching column, then slice and dice the fields in the chosen order.

For example using the perl Text::CSV module, and this trick How to get the index of specific element (value) of an array?

$ perl -MText::CSV -lpe '

  BEGIN{ $p = Text::CSV->new({ allow_whitespace => 1 }) };

  @f = $p->fields() if $p->parse($_);

  ($i) = grep { $f[$_] eq "value" } (0..$#f) if $. == 1; 

  $_ = join ", ", splice(@f, $i, 1),  @f

' example_file.txt

value, group, price

3.21, 1, 3.21

3.42, 1, 4.11

3.5, 1, 1.22

4.1, 2, 9.2

4.2, 2, 2.11

With the great Miller (http://johnkerl.org/miller/doc) is very easy

mlr --csv reorder -f " value, price,group" input.csv

you have

 value, price,group

 3.21, 3.21,1

 3.42, 4.11,1

 3.5, 1.22,1

 4.1, 9.2,2

 4.2, 2.11,2

Please note: I have edited my command, taking into account the spaces in fields name of the question CSV

My suggestion is the following script:

#!/bin/bash



# Set a default value of the LABEL of the target column that must become first column

if [[ -z ${LABEL+x} ]]; then LABEL='value'; fi



# Process a single FILE

move_the_label_column_first() {

    # Read the LABELS on the first line of the input file as an array

    IFS=', ' read -a LABELS < <(cat "$FILE" 2>/dev/null | head -n1)



    # Find the number of the target column

    for ((COL = 0; COL < ${#LABELS[@]}; ++COL))

    do

        if [[ ${LABELS[$COL]} == "$LABEL" ]]

        then

            break

        fi

    done



    # Read each LINE from the input file as an array and output it in the new order

    while IFS=', ' read -a LINE

    do

        printf '%s, ' "${LINE[$COL]}" "${LINE[@]:0:$COL}" "${LINE[@]:$((COL + 1))}" | 

        sed 's/, $/n/'

    done < <(cat "$FILE" 2>/dev/null)

}



# Process all input files, exclude the current script filename

for FILE in "$@"

do

    if [[ -f $FILE ]] && [[ $FILE != $(basename "$0") ]]

    then

        #echo "Input file: $FILE"

        move_the_label_column_first

    fi

done

Let's call the script reorder.sh. To illustrate the script's capabilities let's assume there are the following files we want to process and they are located in the same directory where our script is.

$ cat in-file-1 

group, value, price

1, 3.21, 3.21

1, 3.42, 4.11

1, 3.5, 1.22



$ cat in-file-2

price, group, value, other

3.21, 1, 3.21, 7

4.11, 1, 3.42, 13

1.22, 1, 3.5, -1

Process one input file:

$ ./reorder.sh in-file-1 

value, group, price

3.21, 1, 3.21

3.42, 1, 4.11

3.5, 1, 1.22

Process two input files and change the label of the column that must become first column to price:

$ LABEL='price' ./reorder.sh in-file-1 in-file-2 

price, group, value

3.21, 1, 3.21

4.11, 1, 3.42

1.22, 1, 3.5

price, group, value, other

3.21, 1, 3.21, 7

4.11, 1, 3.42, 13

1.22, 1, 3.5, -1

Process all files in the directory:

$ ./reorder.sh *

value, group, price

3.21, 1, 3.21

3.42, 1, 4.11

3.5, 1, 1.22

value, price, group, other

3.21, 3.21, 1, 7

3.42, 4.11, 1, 13

3.5, 1.22, 1, -1

Process recursively:

$ shopt -s globstar

$ ./reorder.sh **/*

value, group, price

3.21, 1, 3.21

...