Swapping columns is an integral part of data analysis. And with GUI spreadsheet programs it is simply a four-step process. Suppose ColumnA and ColumnB need to be swapped. Then the following sequence does the job.
- Create a new column before ColumnA
- Cut ColumnB into this new column
- Cut ColumnA to the location of ColumnB
- Delete empty column
However, for massive databases, the spreadsheet program is neither adequate nor recommended. The software will take a long time to load the file, maybe even stall in the process of loading the large database. A simpler solution will be to use AWK to swap the columns of the database. This method is extremely fast and efficient. A typical AWK command to rearrange the columns of a database will look like
awk -F ',' 'BEGIN{OFS=",";} {print $1, $5, $3, $4, $2}' test.csv
This command rearranges column 2 with column 8. This command is simple and elegant. But it has its drawbacks. The user needs to type all the column numbers by hand, which will become inefficient as the number of columns increases. A huge database might have more than 50 columns. It is very inefficient to type all column numbers by hand. Another disadvantage of manual entry is that the possibility of error is high while ordering the columns. The solution will be writing a shell script which achieves the same result with an AWK command but is more user-friendly.
#!/bin/bash # swapcolumns v2.1 - this script swaps two columns of a csv file # Usage: ./swapcolumns [inputfilename.csv] [outputfilename.csv] [delimiter] # Input file test if [ -z $1 ]; then echo "Input filename absent" read -p 'Enter input filename: ' inputfilename else inputfilename=$1 fi # check if file exists if [ ! -f $inputfilename ]; then echo "File" $inputfilename "does not exist. Exiting." exit fi # Output file test if [ -z $2 ]; then echo "Output filename absent" read -p 'Enter output filename: ' outputfilename else outputfilename=$2 fi # check if file exists if [ ! -f $inputfilename ]; then echo "File " $inputfilename " does not exist. Exiting." exit fi detecteddelimiter=`head -n1 $inputfilename | tr -d '[a-z][A-Z][0-9]' | sed 's/.\{1\}/&\n/g' | sort -r | uniq -c | sort -nr | tr -s " " | cut -d" " -f3 | head -n1` # Find column delimiter if [ -z $3 ]; then echo "Delimiter detected: " $detecteddelimiter read -p 'Press enter to accept this as the delimiter or enter one: ' delimiter if [ -z $delimiter ];then delimiter=$detecteddelimiter fi else delimiter=$3 fi head $inputfilename -n1 | tr $delimiter '\n' | nl echo read -p 'Enter column 1: ' col1 read -p 'Enter column 2: ' col2 columnlength=`head $inputfilename -n1 | tr ',' '\n' | wc -l` awkstr="" for i in `seq 1 $columnlength`; do if (( $i == $columnlength )); then if (( $i == $col1 )); then awkstr=$awkstr" $"$col2 elif (( $i == $col2 )); then awkstr=$awkstr" $"$col1 else awkstr=$awkstr" $"$i fi else if (( $i == $col1 )); then awkstr=$awkstr" $"$col2"," elif (( $i == $col2 )); then awkstr=$awkstr" $"$col1"," else awkstr=$awkstr" $"$i"," fi fi done # '"$variable"' - the double quotes expands the variable to its value while # single quotes allows AWK to interpreting it as an argument to print # "'"$delimiter"'" requires an extra "" around it so that it is interpreted # as OFS="," and not as OFS=, awk -F ${delimiter} 'BEGIN{OFS = "'"$delimiter"'" ;} {print '"$awkstr"' }' $inputfilename > $outputfilename echo "Output written to" $outputfilename
After the usual checks for missing filenames, the script extracts the column headers using head (which outputs the first part of files) and replaces the column delimiter with a newline using tr. This produces a list of column headers. An nl command numbers the lines and makes it easier for the user to choose the columns. It then reads the input stream for column numbers using the read command. Once the user input is loaded, the script uses a similar one-liner to extract the number of columns and runs through a loop which generates a string of column numbers in the order specified by the user (and stores in the variable awkstr). It checks for the special case when one of the swapped columns is the last column of the database and avoids appending a separating character (comma) for that case.
The AWK command needs some explanation. The ‘”$awkstr”‘ variable, which holds the string meant to generate the reordering of the columns, needs to be expanded inside the AWK command and at the same time interpreted as an argument to print. Double quotes around a bash variable expands its value while single quotes allow string literal interpretation by the print command of AWK. The quotes around the variable for the delimiter work in the same manner except there needs to be an extra double quote around it so that it is interpreted as OFS=”,” and not as OFS=, (absence of quotes generates AWK error).
To implement this code, let’s say we want to rearrange the columns 1 and 2 on a test file displayed below. The test CSV file has three columns – Test, NumA, and NumB.
$ cat test.csv Test,NumA,NumB A,2,9 B,2,9 C,6,17 D,1,7 E,3,11 F,3,11 G,6,17 H,5,15
When the script is run on this test data, the script automatically detects the delimiter and displays a numbered list of the columns. It then asks the user to choose the column numbers which needs to be swapped. We have appended the output of the script stored in the file out.csv.
$ ./swapcolumns.sh test.csv out.csv Delimiter detected: , Press enter to accept this as the delimiter or enter one: 1 Test 2 NumA 3 NumB Enter col1: 1 Enter col2: 2 $ cat out.csv NumA,Test,NumB 2,A,9 2,B,9 6,C,17 1,D,7 3,E,11 3,F,11 6,G,17 5,H,15
The ordering of column numbers is immaterial.
$ ./swapcolumns.sh test.csv out.csv Delimiter detected: , Press enter to accept this as the delimiter or enter one: 1 Test 2 NumA 3 NumB Enter col1: 2 Enter col2: 1 cat out.csv NumA,Test,NumB 2,A,9 2,B,9 6,C,17 1,D,7 3,E,11 3,F,11 6,G,17 5,H,15
The automatic detection of the delimiter (see variable $detecteddelimiter in the above code) is a very useful piece of code (albeit a long one) and is discussed in a subsequent post on identifying delimiter of a CSV file.
When one of the swapped columns is the last column of the database it is a special case, which is handled in the script by the first if statement inside the for loop.
$ ./swapcolumns.sh test.csv test.csv 1 Test 2 NumA 3 NumB Enter col1: 2 Enter col2: 3 Test,NumB,NumA A,9,2 B,9,2 C,17,6 D,7,1 E,11,3 F,11,3 G,17,6 H,15,5
The scripts’ error handlers handle wrong, misspelled, and non-existent filenames.
$ ./swapcolumns.sh Input filename absent Enter input filename: junk.csv File junk.csv does not exist. Exiting.
The script can be easily modified to remove columns instead of swapping them.
#!/bin/bash # removecolumns v1.0 - this script removes columns from a csv file # Usage: ./removecolumns [inputfilename.csv] [outputfilename.csv] [delimiter] # Input file test if [ -z $1 ]; then echo "Input filename absent" read -p 'Enter input filename: ' inputfilename else inputfilename=$1 fi # check if file exists if [ ! -f $inputfilename ]; then echo "File" $inputfilename "does not exist. Exiting." exit fi # Output file test if [ -z $2 ]; then echo "Output filename absent" read -p 'Enter output filename: ' outputfilename else outputfilename=$2 fi # check if file exists if [ ! -f $inputfilename ]; then echo "File " $inputfilename " does not exist. Exiting." exit fi detecteddelimiter=`head -n1 $inputfilename | tr -d '[a-z][A-Z][0-9]' | sed 's/.\{1\}/&\n/g' | sort -r | uniq -c | sort -nr | tr -s " " | cut -d" " -f3 | head -n1` # Find column delimiter if [ -z $3 ]; then echo "Delimiter detected: " $detecteddelimiter read -p 'Press enter to accept this as the delimiter or enter one: ' delimiter if [ -z $delimiter ];then delimiter=$detecteddelimiter fi else delimiter=$3 fi head $inputfilename -n1 | tr $delimiter '\n' | nl echo read -p 'Enter column to be removed: ' col1 columnlength=`head $inputfilename -n1 | tr ',' '\n' | wc -l` awkstr="" for i in `seq 1 $columnlength`; do if (( $i == $columnlength )); then if (( $i != $col1 )); then awkstr=$awkstr" $"$i fi else if (( $i != $col1 )); then awkstr=$awkstr" $"$i"," fi fi done # '"$variable"' - the double quotes expands the variable to its value while # single quotes allows AWK to interpreting it as an argument to print # "'"$delimiter"'" requires an extra "" around it so that it is interpreted # as OFS="," and not as OFS=, awk -F ${delimiter} 'BEGIN{OFS = "'"$delimiter"'" ;} {print '"$awkstr"' }' $inputfilename > $outputfilename echo "Output written to" $outputfilename
Bytes of computer wisdom 