Splitting second column of a line to create multiple lines with a bash oneliner

user_7870777 0
Viewed 653

I have a tab-separated data and it looks like this:

a   1a,2x,c1
b2  a4,4.6
3c  323

The second column has multiple comma seperated values. I want to get this output:

a   1a
a   2x
a   c1
b2  a4
b2  4.6
3c  323

I was able to do it with this python code I wrote:

import sys
f = sys.argv[1]

with open(f) as f:
    for line in f:
        line = line.strip("\n").split("\t")
        genes = line[1].split(",")
        for gene in genes:
            print(line[0],gene, sep="\t")

I know I can do the same with a bash script but I would like to know how can I do this with a cool bash oneliner, using awk, sed, tr and/or cut without using a for loop.

I couldn't go any further than this:

tr ',' '\n' data

cut tr sed awk
6 Answers

EDIT: As per OP's request without loop will be(tested and written with provided samples only),(Fair warning: gsub version with a pipe is curiosity from OP and it is both more fragile and slower than just using a for loop and keeping all processing inside of awk):

awk '{gsub(/,/,ORS $1 OFS)} 1'  Input_file | column -t

Brief explanation: Using gsub function of awk to globally substitute all occurrences of , in each line with ORS(new line by default it value) $1(first field as per OP's requirement) OFS(space by default its value). Then mentioning 1 will print edited/non-edited line here. Then passing awk command's output to column command to beautify its output with same space.

Could you please try following.

awk '{num=split($2,array,",");for(i=1;i<=num;i++){print $1,array[i]}}' Input_file
user_58665800

To avoid writing a loop, with GNU awk for multi-char RS:

$ awk -v RS='[,\n]' 'NF>1{k=$1} {print k, $NF}' file
a 1a
a 2x
a c1
b2 a4
b2 4.6
3c 323

By comparison a loop is clearer, simpler, and would work with any awk in any shell on every UNIX box:

$ awk -F'[[:space:]]+|,' '{for (i=2; i<=NF; i++) print $1, $i}' file
a 1a
a 2x
a c1
b2 a4
b2 4.6
3c 323

Performance wise there won't be a significant difference, they'll both run fast enough.

user_17450010 
$ awk -F$'\t' '{split($2,arr,","); for(e in arr) print($1, arr[e])}' file
a 1a
a 2x
a c1
b2 a4
b2 4.6
3c 323

If you want the tab between col 1 and 2:

awk -F$'\t' '{split($2,arr,","); for(e in arr) print($1 "\t" arr[e])}'
a   1a
a   2x
a   c1
b2  a4
b2  4.6
3c  323
user_2986070 
$ awk -F'[\t,]' '{for (i=2;i<=NF;i++) printf "%s\t%s\n", $1, $i }' file
a       1a
a       2x
a       c1
b2      a4
b2      4.6
3c      323

Use tab and comma as field separators and loop through the fields starting at the second field. Print the first field and the looped field value separated by a tab.

user_109314550

GNU sed

sed -E ':a; s/([^ ]+) *([^,]+),([^,]+)/\1 \2\n\1 \3/; ta' infile

Explanation

This works by repeatedly matching 3 groups with the parenthesis.

  • ([^ ]+) match up-to first space
  • ([^,]+) match up-to the first comma
  • ([^,]+) match up-to a possible second comma
  • \1 \2\n\1 \3 substitute what was matched by the first group followed by the second group, then a new-line followed by the first group and third group

Portable sed

parse.sed

:a
s/([^ ]+) *([^,]+),([^,]+)/\1 \2\
\1 \3/
ta

Run it like this:

sed -Ef parse.sed infile

Output in both case:

a 1a
a 2x
a c1
b2 a4
b2 4.6
3c  323
user_13313990

This might work for you (GNU sed):

sed -E 's/^((.*\t)[^,]+),/\1\n\2/;P;D' file

Replace the first two tab separated values followed by a comma by the first two tab separated values, a newline and the first value followed by a tab. Print and delete the first line in the pattern space and repeat.

user_9674920
Related