4 changed to an external sed command for the text replacements for clarity of intent
source | link

The corresponding expression for the unique from=fromid entries is the same but replacing fields should be obvious. If you want to fake additional toid$6 data from theand fromid$7 data, you can do that by substitution e.g.

toid.awkwith $2 and $3:

#!/usr/bin/$ awk -f

BEGINF, '{
 split($2,s,"="); arr[s[2]]=s[2]" FS="= "$3",";
"$2;}
  END{for (id in arr) print arr[id]}' data.txt
ABCC8 = gsub("from"fromid=5,"to"from=ABCC8
ABCB11 = fromid=4,$0);from=ABCB11


If you want the output to contain both toid and fromid data, you can combine the expressions i.e.

awk -F, '{
split($6$2,s,"="); arr[s[2]]=s[2]" = "$7""$3","$6;"$2;
  split($2$6,s,"="); arr[s[2]]=s[2]" = "$3""$7","$2;"$6;
}
END END{
  for (id in arr) print arr[id];
arr[id]}' data.txt

fromidTo change the labels (i.awk:e. label all the fields in one table as toid even if they come from the fromid lines) probably the most natural way is to pipe the output through sed e.g.

#!/usr/bin/$ awk -f

BEGIN {
  FS="F,";
}
{
  gsub("to","from",$0);'{
  split($6$2,s,"="); arr[s[2]]=s[2]" = "$7""$3","$6;"$2;
  split($2$6,s,"="); arr[s[2]]=s[2]" = "$3""$7","$2;"$6;
}
END END{
  for (id in arr) print arr[id];
arr[id]}' data.txt | sed 's/from/to/g'
ABCC8 = toid=5,to=ABCC8
EGF = toid=164,to=EGF
ADRA1A = toid=21,to=ADRA1A
ACE = toid=11,to=ACE
ABCB11 = toid=4,to=ABCB11
ADRA1B = toid=22,to=ADRA1B
ADRA1D = toid=23,to=ADRA1D
DRD2 = toid=158,to=DRD2
CHRM1 = toid=114,to=CHRM1
CHRM2 = toid=115,to=CHRM2

You could make the fromid <--> toid substitutions inside awk but this method makes the intent clearer, I think. The other table can then be made just by changing the final sed expression to sed 's/to/from/g' instead.

The corresponding expression for the unique from= fields should be obvious. If you want to fake additional toid data from the fromid data, you can do that by substitution e.g.

toid.awk:

#!/usr/bin/awk -f

BEGIN {
   FS=",";
}
 {
  gsub("from","to",$0);
  split($6,s,"="); arr[s[2]]=s[2]" = "$7","$6;
  split($2,s,"="); arr[s[2]]=s[2]" = "$3","$2;
}
END {
  for (id in arr) print arr[id];
}

fromid.awk:

#!/usr/bin/awk -f

BEGIN {
  FS=",";
}
{
  gsub("to","from",$0);
  split($6,s,"="); arr[s[2]]=s[2]" = "$7","$6;
  split($2,s,"="); arr[s[2]]=s[2]" = "$3","$2;
}
END {
  for (id in arr) print arr[id];
}

The expression for the unique fromid entries is the same but replacing fields $6 and $7 with $2 and $3:

$ awk -F, '{split($2,s,"="); arr[s[2]]=s[2]" = "$3","$2;} END{for (id in arr) print arr[id]}' data.txt
ABCC8 = fromid=5,from=ABCC8
ABCB11 = fromid=4,from=ABCB11


If you want the output to contain both toid and fromid data, you can combine the expressions i.e.

awk -F, '{
split($2,s,"="); arr[s[2]]=s[2]" = "$3","$2;
split($6,s,"="); arr[s[2]]=s[2]" = "$7","$6;
} END{for (id in arr) print arr[id]}' data.txt

To change the labels (i.e. label all the fields in one table as toid even if they come from the fromid lines) probably the most natural way is to pipe the output through sed e.g.

$ awk -F, '{
split($2,s,"="); arr[s[2]]=s[2]" = "$3","$2;
split($6,s,"="); arr[s[2]]=s[2]" = "$7","$6;
} END{for (id in arr) print arr[id]}' data.txt | sed 's/from/to/g'
ABCC8 = toid=5,to=ABCC8
EGF = toid=164,to=EGF
ADRA1A = toid=21,to=ADRA1A
ACE = toid=11,to=ACE
ABCB11 = toid=4,to=ABCB11
ADRA1B = toid=22,to=ADRA1B
ADRA1D = toid=23,to=ADRA1D
DRD2 = toid=158,to=DRD2
CHRM1 = toid=114,to=CHRM1
CHRM2 = toid=115,to=CHRM2

You could make the fromid <--> toid substitutions inside awk but this method makes the intent clearer, I think. The other table can then be made just by changing the final sed expression to sed 's/to/from/g' instead.

3 added gsub fakery to satisfy additional requirement
source | link

You could use an awk associative array indexed by the field whose uniqueness you are asserting e.g. for the unique values of the to= field (field $6 when split on commas):

$ awk -F, '{split($6,s,"="); arr[s[2]]=s[2]" = "$7","$6;} END{for (id in arr) print arr[id]}' data.txt
EGF = toid=164,to=EGF
ADRA1A = toid=21,to=ADRA1A
ACE = toid=11,to=ACE
ADRA1B = toid=22,to=ADRA1B
ADRA1D = toid=23,to=ADRA1D
DRD2 = toid=158,to=DRD2
CHRM1 = toid=114,to=CHRM1
CHRM2 = toid=115,to=CHRM2

The corresponding expression for the unique from= fields should be obvious. If you want to fake additional toid data from the fromid data, you can do that by substitution e.g.

toid.awk:

#!/usr/bin/awk -f

BEGIN {
  FS=",";
}
{
  gsub("from","to",$0);
  split($6,s,"="); arr[s[2]]=s[2]" = "$7","$6;
  split($2,s,"="); arr[s[2]]=s[2]" = "$3","$2;
}
END {
  for (id in arr) print arr[id];
}

fromid.awk:

#!/usr/bin/awk -f

BEGIN {
  FS=",";
}
{
  gsub("to","from",$0);
  split($6,s,"="); arr[s[2]]=s[2]" = "$7","$6;
  split($2,s,"="); arr[s[2]]=s[2]" = "$3","$2;
}
END {
  for (id in arr) print arr[id];
}

You could use an awk associative array indexed by the field whose uniqueness you are asserting e.g. for the unique values of the to= field (field $6 when split on commas):

$ awk -F, '{split($6,s,"="); arr[s[2]]=s[2]" = "$7","$6;} END{for (id in arr) print arr[id]}' data.txt
EGF = toid=164,to=EGF
ADRA1A = toid=21,to=ADRA1A
ACE = toid=11,to=ACE
ADRA1B = toid=22,to=ADRA1B
ADRA1D = toid=23,to=ADRA1D
DRD2 = toid=158,to=DRD2
CHRM1 = toid=114,to=CHRM1
CHRM2 = toid=115,to=CHRM2

The corresponding expression for the unique from= fields should be obvious.

You could use an awk associative array indexed by the field whose uniqueness you are asserting e.g. for the unique values of the to= field (field $6 when split on commas):

$ awk -F, '{split($6,s,"="); arr[s[2]]=s[2]" = "$7","$6;} END{for (id in arr) print arr[id]}' data.txt
EGF = toid=164,to=EGF
ADRA1A = toid=21,to=ADRA1A
ACE = toid=11,to=ACE
ADRA1B = toid=22,to=ADRA1B
ADRA1D = toid=23,to=ADRA1D
DRD2 = toid=158,to=DRD2
CHRM1 = toid=114,to=CHRM1
CHRM2 = toid=115,to=CHRM2

The corresponding expression for the unique from= fields should be obvious. If you want to fake additional toid data from the fromid data, you can do that by substitution e.g.

toid.awk:

#!/usr/bin/awk -f

BEGIN {
  FS=",";
}
{
  gsub("from","to",$0);
  split($6,s,"="); arr[s[2]]=s[2]" = "$7","$6;
  split($2,s,"="); arr[s[2]]=s[2]" = "$3","$2;
}
END {
  for (id in arr) print arr[id];
}

fromid.awk:

#!/usr/bin/awk -f

BEGIN {
  FS=",";
}
{
  gsub("to","from",$0);
  split($6,s,"="); arr[s[2]]=s[2]" = "$7","$6;
  split($2,s,"="); arr[s[2]]=s[2]" = "$3","$2;
}
END {
  for (id in arr) print arr[id];
}
2 corrected from=/to= labels
source | link

You could use an awk associative array indexed by the field whose uniqueness you are asserting e.g. for the unique values of the from=to= field (field $6 when split on commas):

$ awk -F, '{split($6,s,"="); arr[s[2]]=s[2]" = "$7","$6;} END{for (id in arr) print arr[id]}' data.txt
EGF = toid=164,to=EGF
ADRA1A = toid=21,to=ADRA1A
ACE = toid=11,to=ACE
ADRA1B = toid=22,to=ADRA1B
ADRA1D = toid=23,to=ADRA1D
DRD2 = toid=158,to=DRD2
CHRM1 = toid=114,to=CHRM1
CHRM2 = toid=115,to=CHRM2

The corresponding expression for the unique tofrom= fields should be obvious.

You could use an awk associative array indexed by the field whose uniqueness you are asserting e.g. for the from= field

$ awk -F, '{split($6,s,"="); arr[s[2]]=s[2]" = "$7","$6;} END{for (id in arr) print arr[id]}' data.txt
EGF = toid=164,to=EGF
ADRA1A = toid=21,to=ADRA1A
ACE = toid=11,to=ACE
ADRA1B = toid=22,to=ADRA1B
ADRA1D = toid=23,to=ADRA1D
DRD2 = toid=158,to=DRD2
CHRM1 = toid=114,to=CHRM1
CHRM2 = toid=115,to=CHRM2

The corresponding expression for the unique to fields should be obvious.

You could use an awk associative array indexed by the field whose uniqueness you are asserting e.g. for the unique values of the to= field (field $6 when split on commas):

$ awk -F, '{split($6,s,"="); arr[s[2]]=s[2]" = "$7","$6;} END{for (id in arr) print arr[id]}' data.txt
EGF = toid=164,to=EGF
ADRA1A = toid=21,to=ADRA1A
ACE = toid=11,to=ACE
ADRA1B = toid=22,to=ADRA1B
ADRA1D = toid=23,to=ADRA1D
DRD2 = toid=158,to=DRD2
CHRM1 = toid=114,to=CHRM1
CHRM2 = toid=115,to=CHRM2

The corresponding expression for the unique from= fields should be obvious.

1
source | link