CODEWITHSUNDEEP
perlawkcut
palc
palc

Reputation: 45

Extracting and matching columns in text file

I have a text file with the following structure. I want to remove first part before comma and keep rest and then match them to the 2nd column and put them in individual rows.

INPUT: 

A,B,C       London
G,L,K,I     Berlin
Q,O,M,J     Madrid

I want a output like this:

OUTPUT:

B  London
C  London
L  Berlin
K  Berlin
I  Berlin
O  Madrid
M  Madrid
J  Madrid

Upvotes: 0

Views: 111

Answers (3)

Borodin
Borodin

Reputation: 126722

A Perl way

perl -aF[\\s,]+ -nE'say "$_ $F[-1]" for @F[1..$#F-1]' myfile

output

B London
C London
L Berlin
K Berlin
I Berlin
O Madrid
M Madrid
J Madrid

Upvotes: 3

Tiago Lopo
Tiago Lopo

Reputation: 7959

Since you have perl tag:

perl -alne '$F[0]=~s/^.*?,//g; @a=split /,/,$F[0]; map { print "$_ $F[1]" } @a' file 
B London
C London
L Berlin
K Berlin
I Berlin
O Madrid
M Madrid
J Madrid

Explanation:

  1. -a Splits fields into array @F
  2. -l prints \n after every print statement
  3. -n Loops trough file
  4. $F[0]=~s/^.*?,//g; Removes anything between begging of string and , for position 0 of array @F
  5. @a=split /,/,$F[0]; Creates array @a
  6. map { print "$_ $F[1]" } @a could also be written as foreach(@a){print "$_ $F[1]"}

Shorter version using shift:

perl -alne '@a=split /,/,$F[0]; shift @a; print "$_ $F[1]" for @a' file

Upvotes: 2

fedorqui
fedorqui

Reputation: 289505

This can be a way with awk:

$ awk '{n=split($1, a, ","); for (i=2; i<=n; i++) print a[i], $NF}' file
B London
C London
L Berlin
K Berlin
I Berlin
O Madrid
M Madrid
J Madrid

Explanation

  • n=split($1, a, ",") slices the first field into pieces based on comma as delimiter. split returns the number of slices and we store that.
  • for (i=2; i<=n; i++) print a[i], $NF we then loop through all these slices, starting from the 2nd one, printing each one together with the last field (city name).

Upvotes: 0

Related Questions