3
\$\begingroup\$

Objective

I'd like to confirm if there is a more efficient approach for the working script below, which splits column data in my tsv files. I suspect this can be achieved with awk or maybe sed but my experience is limited, and I've been unable to use the correct words in my search, to find where someone has achieved a similar result. If someone can confirm if there is a similar post somewhere or a chapter in a text book that will hold the answer, I'd appreciate the guidance.

Context

In my tsv data set, I have columns 6-9 which have a combination of their ID value and matching string. My aim is to separate the IDs and strings, and move the remaining columns across to the right, to make room for the additional columns created. If there is no string data then I duplicate the ID value. If the data is blank then I leave blank cells.

  • The IDs can contain any character but are always contained in the last set of brackets for their column, unless the ID doesn't have a matching string, in which case only the ID value is present with brackets excluded.

  • The strings can also contain any character including brackets but are not always present in the column.

  • In some cases columns 6-9 can be empty

example data - data.tsv

client  geo 2022-08-30  username    city    supplier1   10413   232080743   ham
client  geo 2022-08-30  username    city    supplier2 (3219dds135)  campaign (4049) 275975960   ham
client  geo 2022-08-30  username    city    Supplier2 (3211239135)  Campaign. (9591)    276220665   ham
client  geo 2022-08-30  username    city    supplier3 (3219132DSAD35)   Campaign(campaign) (72dad59)    232074226   ham
client  geo 2022-08-30  username    city    Supplier4 (3242319135)  campaign (CAMPAIGN) (7424)  232074240   ham
client  geo 2022-08-30  username    city    Supplier4 (3242319135)  Campaign - (CAMPAIGN) (7424)    232074240   ham
client  geo 2022-08-30  username    city    232074240   Campaign – (CAMPAI4324GN) (7424)    DELIVERY (DSAD32E!) (232074240) ham
client  geo 2022-08-30  username    city                ham

working script - text-column.sh

#!/bin/bash

FILENAME="data"

cut -f 6 $FILENAME.tsv | sed -Ee 's/^.+ \(//g' -e 's/\)$//g' > $FILENAME-id.tsv
cut -f 6 $FILENAME.tsv | sed -Ee 's/ \(\S+\)$//g' > $FILENAME-name.tsv
cut -f 1 $FILENAME-id.tsv | paste $FILENAME-name.tsv - > $FILENAME-nameid.tsv

cut -f 7 $FILENAME.tsv | sed -Ee 's/^.+ \(//g' -e 's/\)$//g' > $FILENAME-id2.tsv
cut -f 7 $FILENAME.tsv | sed -Ee 's/ \(\S+\)$//g' > $FILENAME-name2.tsv
cut -f 1 $FILENAME-id2.tsv | paste $FILENAME-name2.tsv - > $FILENAME-nameid2.tsv

cut -f 8 $FILENAME.tsv | sed -Ee 's/^.+ \(//g' -e 's/\)$//g' > $FILENAME-id3.tsv
cut -f 8 $FILENAME.tsv | sed -Ee 's/ \(\S+\)$//g' > $FILENAME-name3.tsv
cut -f 1 $FILENAME-id3.tsv | paste $FILENAME-name3.tsv - > $FILENAME-nameid3.tsv

cut -f 1,2 $FILENAME-nameid.tsv | paste $FILENAME.tsv  - > 1-$FILENAME.tsv
cut -f 1,2 $FILENAME-nameid2.tsv | paste 1-$FILENAME.tsv  - > 2-$FILENAME.tsv
cut -f 1,2 $FILENAME-nameid3.tsv | paste 2-$FILENAME.tsv  - > 3-$FILENAME.tsv

awk -F'\t' -v OFS="\t" '{print $1,$2,$3,$4,$5,$10,$11,$12,$13,$14,$15,$9}' 3-$FILENAME.tsv > 4-$FILENAME.tsv

Expected output

client  geo 2022-08-30  username    city    supplier1   supplier1   10413   10413   232080743   232080743   ham
client  geo 2022-08-30  username    city    supplier2   3219dds135  campaign    4049    275975960   275975960   ham
client  geo 2022-08-30  username    city    Supplier2   3211239135  Campaign.   9591    276220665   276220665   ham
client  geo 2022-08-30  username    city    supplier3   3219132DSAD35   Campaign(campaign)  72dad59 232074226   232074226   ham
client  geo 2022-08-30  username    city    Supplier4   3242319135  campaign (CAMPAIGN) 7424    232074240   232074240   ham
client  geo 2022-08-30  username    city    Supplier4   3242319135  Campaign - (CAMPAIGN)   7424    232074240   232074240   ham
client  geo 2022-08-30  username    city    232074240   232074240   Campaign – (CAMPAI4324GN)   7424    DELIVERY (DSAD32E!) 232074240   ham
client  geo 2022-08-30  username    city                            ham
\$\endgroup\$

1 Answer 1

2
\$\begingroup\$

Consider using Python

This relatively simple task is not trivial to implement in Bash. It would be straightforward in Python, present in modern systems, and I think it would be worth the investment.

Don't repeat yourself

The sed operations that extract id and name values from a column are not trivial, and repeated. To avoid duplication, you can define a function, with a descriptive name to signal readers what's happening, for example:

extract_id() {
    sed -Ee 's/^.+ \(//g' -e 's/\)$//g'
}

extract_name() {
    sed -Ee 's/ \(\S+\)$//g'
}

cut -f 6 "$FILENAME.tsv" | extract_id > "$FILENAME-id.tsv"
cut -f 6 "$FILENAME.tsv" | extract_name > "$FILENAME-name.tsv"

I also added the recommended double-quotes around the parameters that contain variables, as that is a good practice.

And then you can take this even further. Instead of repeating the same operation for 3 different columns, consider using a loop:

for col in 6 7 8; do
    cut -f "$col" "$FILENAME.tsv" | extract_id > "$FILENAME-$col-id.tsv"
    cut -f "$col" "$FILENAME.tsv" | extract_name > "$FILENAME-$col-name.tsv"
done

It's true that the above changes to output filenames, it's not too hard to adapt the rest of the script, and this way it's easier to see where those files came from.

Avoid unnecessary commands

Many of the cut commands are actually unnecessary, taking the first column from a file with a single column, or the first 2 columns from a file with two columns:

cut -f 1 $FILENAME-id.tsv | paste $FILENAME-name.tsv - > $FILENAME-nameid.tsv
cut -f 1,2 $FILENAME-nameid.tsv | paste $FILENAME.tsv  - > 1-$FILENAME.tsv

These could be written simpler without cut at all:

paste "$FILENAME-name.tsv" "$FILENAME-id.tsv" > "$FILENAME-nameid.tsv"
paste "$FILENAME.tsv "$FILENAME-nameid.tsv" > "1-$FILENAME.tsv"

Also, many of the cut + paste commands create unnecessary intermediate files. That is, once you have the id and name columns in separate files, you don't need to create files with name-id pairs, you can paste them together directly.

Following up on the loop we used earlier, you can skip most of the intermediate files with a single paste:

paste "$FILENAME.tsv" "$FILENAME"-{6,7,8}-{name,id}.tsv > "3-$FILENAME.tsv"

You could also eliminate that last intermediate file by piping directly into awk:

paste "$FILENAME.tsv" "$FILENAME"-{6,7,8}-{name,id}.tsv |
    awk -F'\t' -v OFS="\t" '{print $1,$2,$3,$4,$5,$10,$11,$12,$13,$14,$15,$9}' > "4-$FILENAME.tsv"

Actually, instead of typing out all the columns in that awk, it would be simpler to introduce an intermediary file for the 9th column, and then paste the rest together:

cut -f9 "$FILENAME.tsv" > "$FILENAME-9.tsv"
cut -f1-5 "$FILENAME.tsv" |
    paste - "$FILENAME"-{6,7,8}-{name,id}.tsv "$FILENAME-9.tsv" > "4-$FILENAME.tsv"

Cleanup temporary files

I'm not sure if you need all the intermediary files created by the script. For any files that are not needed afterwards, you can use a temporary directory, and make the script clean it up on exit:

workdir=$(mktemp -d)

cleanup() {
    find "$workdir"
    rm -fr "$workdir"
}

trap cleanup EXIT

Don't use SHOUT_CASE for variables

SHOUT_CASE is commonly used for system environment variables. It's confusing to see them used for other purposes, and may lead to problems.

Putting it together

With the above suggestions, the script becomes less repetitive, using fewer intermediate files, and a simple cleanup:

#!/bin/bash

set -euo pipefail

input="data.txt"
output="4-$input.tsv"

workdir=$(mktemp -d)

cleanup() {
    rm -fr "$workdir"
}

trap cleanup EXIT

extract_id() {
    sed -Ee 's/^.+ \(//g' -e 's/\)$//g'
}

extract_name() {
    sed -Ee 's/ \(\S+\)$//g'
}

for col in 6 7 8; do
    cut -f "$col" "$input.tsv" | extract_name > "$workdir/$col-name.tsv"
    cut -f "$col" "$input.tsv" | extract_id > "$workdir/$col-id.tsv"
done

cut -f 9 "$input.tsv" > "$workdir/9.tsv"
cut -f 1-5 "$input.tsv" |
    paste - "$workdir"/{6,7,8}-{name,id}.tsv "$workdir/9.tsv" > "$output"

This is still not so great, as it still reads the input file 8 times. It's certainly possibly to do this all with awk in a single pass, but so it is with Python, a modern language.

\$\endgroup\$
2
  • \$\begingroup\$ thanks for highlighting some bad habits I've clearly picked up teaching myself. I've got limited experience with Python (typically Python3), so can you recommend which modules I should start learning, which would benefit me for this problem? I find learning from problems I have - vs going through text books - easier with scripting. \$\endgroup\$
    – El_Birdo
    Sep 1, 2022 at 10:05
  • 1
    \$\begingroup\$ No worries! Take a look at the first results of google search for "python csv" and "python re". They have examples for performing what you need here: reading and writing TSV, and pattern replacement using regular expressions. \$\endgroup\$
    – janos
    Sep 1, 2022 at 10:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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