2

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?

  • Do you need to output the additional columns - or just value,price,group in that order regardless of what other columns may exist? – steeldriver Jan 15 at 0:54
  • all the original columns but with value now moved to being the first one – user2413 Jan 15 at 0:58
3

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
  • Fantastic! I will throw a bounty your way to attract attention to this great answer asap – user2413 Jan 15 at 2:12
  • 1
    Thank you for your answer. I now find aborruso's answer the simplest to use for this problem. – user2413 Jan 21 at 13:24
2
+50

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

  • 2
    I would like to upvote your answer because of its simplicity, but it does not work...The output I get is identical to the input :-( – zx485 Jan 20 at 22:29
  • hi @zx485 I'm using this csv gist.githubusercontent.com/aborruso/… Please look also at this video youtu.be/XcTXaxKy-t4?hd=1 – aborruso Jan 20 at 22:39
  • I looked at both, but mlr aka miller still doesn't do what it's supposed to do. I got it from the repositories of Ubuntu 18.04. – zx485 Jan 20 at 22:43
  • 1
    You are welcome @zx485! But why nasty? As you can read in CSV RFC " Spaces are considered part of a field and should not be ignored": than it's necessary to take in account spaces in field name. – aborruso Jan 21 at 15:26
  • 1
    Well then. I didn't knew that. Thanks for the clarification. – zx485 Jan 21 at 15:35
1

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
...
  • Thank you for your answer (+1). I find aborruso's to be the simplest to use in the sense that it all happens 'under the fold'. – user2413 Jan 21 at 13:26
  • @user2413, thanks. Yes, aborruso's answer looks nice and simple, but I didn't succeed with it. However, my suggestion is most portable of the 4 solutions provided up to this moment :) – pa4080 Jan 21 at 19:27

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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