CODEWITHSUNDEEP
bashawksedtext-processing
Adriano S.
Adriano S.

Reputation: 25

Transpose rows to column after nth column in bash

I have a file like below format:

$ cat file_in.csv

1308123;28/01/2019;28/01/2019;22/01/2019
1308456;20/11/2018;27/11/2018;09/11/2018;15/11/2018;10/11/2018;02/12/2018
1308789;06/12/2018;04/12/2018  
1308012;unknown

How can i transpose as below, starting from second column:

1308123;28/01/2019  
1308123;28/01/2019  
1308123;22/01/2019  
1308456;20/11/2018  
1308456;27/11/2018  
1308456;09/11/2018  
1308456;15/11/2018  
1308456;10/11/2018  
1308456;02/12/2018  
1308789;06/12/2018  
1308789;04/12/2018  
1308012;unknown

I'm testing my script, but obtain a wrong result

echo "123;23/05/2018;24/05/2018" | awk -F";" 'NR==3{a=$1";";next}{a=a$1";"}END{print a}'

Thanks in advance

Upvotes: 1

Views: 111

Answers (2)

stack0114106
stack0114106

Reputation: 8791

Another awk

awk -F";" '{ OFS="\n" $1 ";"; $1=$1;$1=""; printf("%s",$0) } ' file

Upvotes: 0

RavinderSingh13
RavinderSingh13

Reputation: 133760

1st Solution: Eaisest solution will be, loop through all fields(off course have set field separator as ;) and then print $1 along with all fields in new line. Also note that loop is running from i=2 to till value of NF leaving first field since we need to print in new line from column 2nd onwards.

awk 'BEGIN{FS=OFS=";"} {for(i=2;i<=NF;i++){print $1,$i}}'  Input_file

2nd Solution: Using 1 time substitution(sub) and global substitutions(gsub) functionality of awk. Here I am changing very first occurence of ; with @@@(assumed that your Input_file will NOT have this characters together, in case it is there choose any unique character(s) which are NOT in one's Input_file on place of @@@), then globally subsituting ;(all occurences) with ORS val(a variable which has value of $1) and ; so make values in new column. Now finally remove @@@ from first field. Why we have done this approch if we DO NOT substitute very first occurence of ; with any other character then it will place a NEW LINE before substituion which we DO NOT want to have. (Also as per Ed sir's comment this solution was tested in 1 Input_file and may have issues while reading multiple Input_files)

awk 'BEGIN{FS=OFS=";"} {val=$1;sub(";","@@@");gsub(";",ORS val ";");sub("@@@",";",$1)} 1' Input_file

Upvotes: 3

Related Questions