1

I have several file like A_bla.csv(i.e B_bla.csv C_bla.csv etc ...) (It's a subsampling of the real file in term of lign but the number of column is the real one) :

1,test,test2,55.2,test3
1,test,test2,96.3,test3
1,test,test2,64.2,test3
1,test,test2,97.2,test3

And a main file main.tsv (field separator = \t):

id  coverage    clade
A   wrongdata   20
B   wrongdata   19
C   wrongdata   19

And I want to print the field of the 89th line of the 4th column from each *_bla.csv file into the coverage column of main.tsv file. In order to do that, I need to use the FILENAME of the *_bla.csvfile and use it as pattern with the id column of main.tsv file.

So far I tried :

for file in *_bla.csv ; do
r="$(basename -s "_bla.csv" $file)"
awk ... ; 
done

But I'm near to a start of answer. Do you have an idea how to do that using only tool that are by default in a Linux machine (awk,grep,sed,python,perl ...) ? Thanks

5
  • In your example, you want to replace wrongdata of line 2 with – let's say – 42A, if this is the 4th field of line 89 in A_bla.csv?
    – Philippos
    Jul 6, 2021 at 9:58
  • Yes per example! I don't know if it's helpful but the value is only numeric (i.e float)
    – nstatam
    Jul 6, 2021 at 10:13
  • 1
    Please edit your question and give us files that we can use to test our answers. Show us a minimal example and the output you would expect from that minimal example. You mention "89th line". Why only that line? Will it always be the 89th line for all *bla.csv files?
    – terdon
    Jul 6, 2021 at 11:40
  • @terdon The problem is that if I give several file with each time at least 89 line it would be indigest for the Helpers. That's why I subsample my question to be more digest
    – nstatam
    Jul 6, 2021 at 12:46
  • 1
    @nstatam then tell us to use line 3 instead of line 89, the principle is the same. But if you don't show us your expected output, how can we know we are giving you what you need?
    – terdon
    Jul 6, 2021 at 14:30

3 Answers 3

1

The unavoidable GNU sed one-liner:

sed '1n;h;s/\([[:alnum:]]\).*/sed -E "89!d;s_([^,]*,){3}__;s_,.*__" \1_bla.csv/e;G;s/\(.*\)\n\(.*\)wrongdata/\2\1/' main.tsv

What's the magic? We use the execute option to the substitute command to extract the 4th field of the 89th line with another sed command. In detail:

  • 1n to leave the first line unchanged
  • h to store the line in hold space before we mess it up
  • \([[:alnum:]]\).* matches the whole line and captures the first alphanumeric field in \(\) so we can refer to it as \1 in the replacement
  • sed -E "89!d;s_([^,]*,){3}__;s_,.*__" \1_bla.csv is this replacement: All lines except for 89 will be deleted, then the first three fields get deleted and finally everything after the new first field. So actually only the fourth field of line 89 survives, so this will be returned by the execution of the buffer
  • Now we only need to append the save line from the hold space with G and use s/\(.*\)\n\(.*\)wrongdata/\2\1/ to replace wrongdata with the extracted field.
0
awk -F',|\t' '
FILENAME != "main.tsv" && FNR == 89 {
                        sub(/_.*$/, "", FILENAME)
                        A[FILENAME]=$4
                        }
FILENAME != "main.tsv"  {next}
A[$1]                   {$2 = A[$1]}
                        {print}
' *bla.csv OFS='\t' main.tsv

I see that there is no tab delimiter in the main.tsv file. Then it will be necessary to correct the pattern like this: -F',|[[:blank:]]+'

6
  • 1
    Unfortunately, the code rendering system here eats the tabs which is probably why you don't see them in the OP's example.
    – terdon
    Jul 6, 2021 at 11:37
  • Thanks for your response. I have a problem when running your script, the output is a simily of the structure of the *bla.csv file; is like the FNR and NR are not in the good order or something. Do you know why ?
    – nstatam
    Jul 6, 2021 at 12:36
  • @nstatam, thank you. I fixed the code
    – nezabudka
    Jul 6, 2021 at 12:58
  • You can also make the code more universal and use the following construction instead of FILENAME != "main.tsv" the name of the last file to - FILENAME != AGRV[ARGC-1].
    – nezabudka
    Jul 6, 2021 at 13:10
  • @EdMorton, there are many input files, but the last one is needed. I would have liked the /,/ pattern or the !/,/ antipattern better. And @gest_7 decision is more reasonable split(FILENAME, a, "_")
    – nezabudka
    Jul 6, 2021 at 18:35
0

You can do using the awk utility. Must run from the directory where the csv files are.

awk -v OFS='\t' '
  FS=="," && FNR==89 {
    split(FILENAME, a, "_")
    h[substr(a[1],3)] = $4
  }
  f=FS=="\t" {
    $2 = FNR>1&&($1 in h) ? h[$1] : $2
  };f
' FS=, ./*_bla.csv FS="\t" ./main.tsv

id  coverage  clade
A   35.8      20
B   65.7      19
C   35.8      19

perl -F, -lane '
  if (@ARGV) {
    89..89 && do{
      $h{($ARGV=~/^..(\w+)_/)[0]} = $F[3];
      close ARGV;
    };
  } else { #last file here
    @F = split "(\t)";
    $F[2] = $h{$F[0]} // $F[2];
    print($.>1?@F:$_)
  }
' ./*_bla.csv ./main.tsv

GNU sed. But first we grep out the 89th line,4th field from each of the CSV files, escape the data coming out to make it safe for plugging in the RHS of a sed s/// statement.

hold=$(
  grep -Pzo '^(?:.*\n){88}(?:[^,]*,){3}\K[^,]*' -- *_bla.csv |
  tr '\0' '\n'|
  sed -Ee '
    s/_.*:/\t/
    s:[\&/]:\\&:g
    $!s:$:\\:
  ')

sed -En \
  -e "1{x;s/.*/$hold/;x;}" \
  -e '
    1!G
    s/^(\S+\t)wrongdata(\t.*)\n\1([^\n]*)/\1\3\2/
    P
  ' \
./main.tsv

python3 -c '
import sys, pathlib, itertools
mainf = sys.argv[1]
fs,rs = ofs,ors = "\t","\n"
d,dlm = {},","
for p in pathlib.Path(".").glob("*_bla.csv"):
  if p.is_file():
    id = p.name[:p.name.find("_")]
    with open(p.name) as f:
      d[id] = [l.rstrip(rs).split(dlm)[3] for l in itertools.islice(f,88,89)][0]
with open(mainf) as f:
  for l in f:
    L = l.rstrip(rs).split(fs)
    print(L[0], d.get(L[0],L[1]), *L[2:], sep=ofs)
' ./main.tsv
2
  • Thanks for your response. I have a problem when running your script, the output is a simily of the structure of the *bla.csv file; is like the FNR and NR are not in the good order or something. Do you know why ?
    – nstatam
    Jul 6, 2021 at 12:36
  • Edited my answer plus added a few more solutions using different tools.
    – guest_7
    Jul 7, 2021 at 7:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .