Duplicate a CSV column in Bash

Question

I have an issue where a client needs to duplicate a column in a CSV file. The values are always going to be identical and unfortunately our API doesn't allow for duplicate columns to be specified in the JSON.

For example I have the following column structure and values:

Name, Surname, City, Age, Job
John, Doe, Johannesburg, 28, Technical Support

Now I need to duplicate City so the output should be:

Name, Surname, City, City Again, Age, Job
John, Doe, Johannesburg, Johannesburg, 28, Technical Support

The column needs to be placed after that which will be duplicated. The value is also dependent on this first column.

Answer 1

awk can handle this easily:

awk 'BEGIN{FS=OFS=", "} {$3 = $3 OFS $3} 1' file.csv

Name, Surname, City, City, Age, Job
John, Doe, Johannesburg, Johannesburg, 28, Technical Support

Note that this does the job in single and shorted command that is easy to read and is far more efficient than pipeline command that involves calling cut twice and then a `paste.

As @codeforester rightly commented below, cut doesn't let a column be repeated in the output; it is used for stripping out a value.

Answer 2

Assuming that City will always be located at column 3, the cut and paste commands can be utlized. For example:

csv=path/to/somefile.csv
echo "$(paste -d',' <(cut -d',' -f1-3 $csv) <(cut -d',' -f3- $csv))" > "$csv"

Notes:

1. The path/to/quux.csv part, which is assigned to the csv variable, should be replaced with the actual path to your .csv file.
2. The new content which includes the duplicated City column is written back to the same source file.
3. The <(...) part is known as process substitution and is typically used when piping data is not possible. A good explanation of it can be found here.

Answer 3

A while back, I wrote a bash function to insert array elements:

function array_insert {
    # options: arrayname index [value]
    if ! declare -p "$1" 2>/dev/null | grep -q '^declare -a'; then
        printf '%s: not an array: %s\n' "$0" "$1" >&2
        return 1
    fi
    local -n source="$1"
    local -a indices=( "${!source[@]}" )
    for ((i=${#indices[@]}-1; i>=$2; i--)) ; do
        source[$((i+1))]="${source[$i]}"
    done
    if [ -n "$3" ]; then
        source[$2]="$3"
    fi
}

With this function loaded, you might do the following:

while read line; do
  IFS=, declare a=( $line )     # assign the line's fields to an array,
  array_insert a 3 "${a[2]}"    # insert the column in this line,
  o=$(printf '%s,' "${a[@]}")   # assemble your output,
  printf '%s\n' "${o%,}"        # remove the trailing comma.
done < input.txt

For me, this provides the following output using your input:

Name, Surname, City, City, Age, Job
John, Doe, Johannesburg, Johannesburg, 28, Technical Support

Note that in bash, arrays are indexed from 0, so ${a[2]} is the third column.

Answer 4

below is Small script which took two input

1. File Name
2. Column name and it will duplicate mention column

Below is Script

echo "Enter  CSV File  name "
read fileName

echo "Enter Column name to be duplicated "
read columnName


columnNumber=`head -1 $fileName | awk -v RS="," "/$columnName/{print NR;}"`     #Identify Column number using column name
totalNumberOfColumn=`head -1 $fileName | awk -F',' '{print NF}'`                #identify total number of column

str=""                                                                          #Create empty variab str to print column number in awk
for ((i=1;i<=$totalNumberOfColumn;i++));
do
str="$str \$$i\",\""
        if [ $i == $columnNumber ]
        then
        str="$str \$$i\",\""
        fi
done


awk -F',' "{print ${str}}" $fileName  | sed 's/,$//g'                           #Print all column inculding duplicate column 

below is content of file, file name is data.csv

:cat data.csv
Name, Surname, City, Age, Job
John, Doe, Johannesburg, 28, Technical Support

Output 1 for Duplicating column City

:bash script.sh
Enter  CSV File  name
data.csv
Enter Column name to be duplicated
City
Name, Surname, City, City, Age, Job
John, Doe, Johannesburg, Johannesburg, 28, Technical Support

Output 2 : for Duplicating column Name

:bash script.sh
Enter  CSV File  name
data.csv
Enter Column name to be duplicated
Name
Name,Name, Surname, City, Age, Job
John,John, Doe, Johannesburg, 28, Technical Support