Replace data at specific positions in txt file using data from another file

Question

I have a text file in the below format:

$data This is the experimental data    
good data
This is good file
datafile
1 4324 3673 6.2e+11 7687 67576
2 3565 8768 8760 5780 8778          "This is line '2'"
3 7656 8793 -3e+11 7099 79909
4 8768 8965 8769 9879 0970
5 5878 9879 7.970e-1 9070 0709799
.
.
.
100000 3655 6868 97879 96879 69899
$.endfile

I want to replace the data of the 3rd and 4th column from row '2' to '100000' with the data from two other text files which have one column of 99999 rows each.

How can I do this using awk, sed or any other unix command? Note that the column delimiter is space.

The other two text files have 99999 lines each, and they are both in the following format:

this doesn't look very complex, however, awk maight no be able to handle 99999 columns ... — Archemar
– Archemar, Commented Sep 8, 2014 at 11:36
Please give a full example that we can work with. We need to have an example of all three files and also your desired output. Presumably, you want column 2 to have the data from file2 and column3 from file3 but you do not make this clear. Also, is the $.endfile actually part of the file? — terdon
– terdon ♦, Commented Sep 8, 2014 at 11:36
@terdon yes $.endfile is the last line of my main file. I want to replace column 3 from file1.txt and column 4 from file2.txt. The desired output will be:- $data This is the experimental data good data This is good file datafile 1 4324 3673 6.2e+11 7687 67576 2 3565 8768 12414 5780 8778 "This is line '2'" 3 7656 8793 12421 7099 79909 4 8768 8965 36347 9879 0970 5 5878 9879 3.4e+3 9070 0709799 . . . 100000 3655 6868 87698 96879 69899 $.endfile — Nilesh
– Nilesh, Commented Sep 8, 2014 at 11:48

terdon · Accepted Answer · 2014-09-09 10:48:32Z

An awk way:

awk '{if(FNR==NR){f2[FNR+1]=$1;} 
      else{
        if(FNR==1){k++;} 
        if(k==1){f3[FNR+1]=$1} 
        else{if($1~/^[0-9]+/ && $1>1){$3=f2[$1];$4=f3[$1];} 
         print}
  }}' file2 file3 file1

This is the same thing written as a commented script for clarity:

#!/usr/local/bin/gawk -f

{
    ## NR is the current line number, irrespective of 
    ## which input file is being read. FNR is the line 
    ## number of the current file. It is reset to 1 each 
    ## time a new file is opened. Therefore, FNR will be 
    ## equal to NR only while the 1st file is being read.
    if(FNR==NR){
        ## If this is the 1st file, save its 1st field
        ## in the array f2. The key of the array is the
        ## line number of the current file plus one. This is
        ## because you want to start modifying from row '2' onwards.
        ## Therefore, presumably, you want the 1st row of file2 to
        ## be the value for row '2' of your data file..
        f2[FNR+1]=$1;
    } 
    ## If this is not the 1st file
    else{
        ## If this is the 1st line of the current file
        if(FNR==1){
            ## Increase the value of the variable k by 1.
            k++;
        } 
        ## If k is currently 1, this means that the above has only
        ## been run once so we are currently reading the 1nd file.
        if(k==1){
            ## Save the 1st field of this file (file3 in your example)
            ## in the array f3. The key of the array is the
            ## line number of the current file plus one. 
            f3[FNR+1]=$1
        }
        ## If k is not 1, we are reading the 3rd file. In this case, 
        ## your actual data.
        else{
            ## If the 1st field is a number and is greater than 1.
            ## In other words, if this is one of the lines you want
            ## to change. 
            if($1~/^[0-9]+/ && $1>1){
                ## Set the 3rd field to be the value saved in the array
                ## f2 for the value of $1.  
                $3=f2[$1];
                ## Set the 4th field to be the value saved in the array
                ## f3 for the value of $1. 
                $4=f3[$1];
            } 
            ## Print the current line. Since this is outside the
            ## previous if block, it will print all lines irrespective
            ## of whether they've been modified. 
            print;
        }
    }
}

A Perl way:

perl -lane 'BEGIN{
    open(A,"file2"); while(<A>){chomp; $f2{$.+1}=$_;} 
    open(B,"file3"); while(<B>){chomp; $f3{$.+1}=$_;}} 
    if($F[0]=~/^\d+$/ && $F[0]>1){$F[2]=$f2{$F[0]}; $F[3]=$f3{$F[0]}}
     print "@F"' file1

Explanation

-lane : the l will automatically remove trailing newlines from the end of each input line (the same as chomp) and add a newline to each print statement. The a will automatically split each input line at whitespace into the @F array, making perl run like awk. The n means "run the script provided by -e on each line of the input file.
BEGIN{...} : this is run before the input file is read. In this case, I am opening each of the extra files and saving their content in the %f2 and %f3 hashes. This is basically the same as the awk arrays I used above.
if($F[0]=~/^\d+$/ && $F[0]>1){...} : again, this is the same logic as in the awk script. It will replace the fields with the corresponding entries of each file.
print "@F" : this will print all fields.

@Jidder it might but only if you have very little memory. A file with a single field of just a few characters and 100000 lines is less than 1M. Any modern system should be able to deal with this easily. In any case, both the awk and the perl ways slurp the files into memory so they'll have the same problem. — terdon
– terdon ♦, Commented Sep 8, 2014 at 14:02
@terdon: In your solution using awk, in which line or where you are reading the input files? I dont get it.. Please explain... — Nilesh
– Nilesh, Commented Sep 9, 2014 at 5:29
@Nilesh I'm not sure what you mean. Awk reads through its input lines line by line by default. So, awk '{print}' input_file will simply print each line of input_file. Here, I am giving three files so the entire script is applied to each line of each of the three files. In the script version, you would need to save the script to a file, make it executable (chmod a+x foo.awk) and then give it the files as input (./foo.awk file2 file3 file1`). — terdon
– terdon ♦, Commented Sep 9, 2014 at 12:18

G-Man Says 'Reinstate Monica' · Accepted Answer · 2014-09-08 16:28:43Z

Since you haven’t asked for a 100% awk solution, I’ll offer a hybrid that (a) may, arguably, be easier to understand, and (b) doesn’t stress awk’s memory limits:

awk '
    $1 == 2 { secondpart = 1 }
       { if (!secondpart) {
                print > "top"
         } else {
                print $1, $2 > "left"
                print $5, $6, $7, $8, $9 > "right"
         }
       }' a
(cat top; paste -d" " left b c right) > new_a
rm top left right

Or we can eliminate one of the temporary files and shorten the script by one command:

(awk '
    $1 == 2 { secondpart = 1 }
       { if (!secondpart) {
                print
         } else {
                print $1, $2 > "left"
                print $5, $6, $7, $8, $9 > "right"
         }
       }' a; paste -d" " left b c right) > new_a
rm left right

This will put some extra spaces at the ends of the lines of the output, and it will lose data from file a if any line has more than nine fields (columns). If those are issues, they can be fixed fairly easily.

Community · Accepted Answer · 2020-06-11 12:04:56Z

{ { paste -d\  /dev/fd/[345] | 
    sed 's/ \( [^ ]*\)\(.*\)/\2\1/'
} 3<<FILE1 4<<FILE2 5<<FILE3
$(<file1 sed '1,/^1/w /dev/fd/2
      /^2/,$!d;s/ [^ ]*//4;s// /3')
FILE1
$(<file2 tr -s \\n)
FILE2
$(<file3 tr -s \\n)
FILE3
} 2>&1

In the above command sequence I do a fair amount of in/out juggling. It is pretty simply done. file[23] are actually identical - they're both a copy of your 99,999 lines of rows 3/4. That leaves file1 - it is essentially the exact file in your example above, but line 5 is duplicated into 6 and 7 to match file[23].

Basically, each file just gets its own file-descriptor and its own bit of prepwork. file[23] get almost no prep - tr just squeezes all repeated \newline characters into one - so the blank lines disappear.

file1 gets a little more. First all lines up to and including the first line beginning with a 1 are written to stderr. They're next deleted from output - so they only get out to >&2. Next sed selects cols 3/4 and replaces them with a single space - which means that where they were there are now two consecutive space characters.

paste gathers all of the file-descriptors and sticks them all together separated by spaces. Then sed attempts to swap the first sequence of non-space characters immediately following two space characters with everything thereafter.

Last the file descriptors for stderr and stdout are joined into stdout. The result looks like this:

OUTPUT

$data This is the experimental data

good data

This is good file

datafile

1 4324 3673 6.2e+11 7687 67576
2 3565 8768 12414 12414 8778
3 7656 8793 12421 12421 79909
4 8768 8965 36347 36347 0970
5 5878 9879 3.4e+3 3.4e+3 0709799
6 5878 9879 -3.5e22 -3.5e22 0709799
7 5878 9879 987983 987983 0709799
. . .
. . .
. . .
100000 3655 6868 87698 87698 69899
$.endfile

Ha! I was hoping someone would come up with a nice paste approach. — terdon
– terdon ♦, Commented Sep 9, 2014 at 13:51
@terdon - thanks man. I was hoping someone would notice! you made my day — mikeserv
– mikeserv, Commented Sep 9, 2014 at 13:56
By the way, I'm pretty sure the blank lines were a mistake in the OP. There was also a link to [this image]( i.sstatic.net/QwJGs.jpg) which shows the file with no blank lines. I have since removed them. You might want to simplify a bit. — terdon
– terdon ♦, Commented Sep 9, 2014 at 14:03
@terdon - But that's the same - file1 never had any. It just had all that weird gooooooood data stuff. It would make it simpler if I could remove two heredocs and refer directly to two files, but those two are the easy part. I don't feel like testing it all again, either. Maybe I'll take a wait and see stance on this one. In any case, definitely between us, we've got it covered, I think. I quite like the colors in the image though. — mikeserv
– mikeserv, Commented Sep 9, 2014 at 14:07

Archemar · Accepted Answer · 2014-09-08 12:27:40Z

here is what i came up.

your data are on a.txt, third column are on b.txt (I put weekday name for clarity, this will work as well with number.).

mybox $ cat b.txt
day monday tuesday wednesday thursday friday saturday
mybox $ cat a.txt
1 4324 3673 6.2e+11 7687 67576
2 3565 8768 8760 5780 8778
3 7656 8793 -3e+11 7099 79909
4 8768 8965 8769 9879 0970
5 5878 9879 7.970e-1 9070 0709799
100000 3655 6868 97879 96879 69899
mybox $ cat ul.awk
FILENAME == "b.txt" { for (i=2;i<=NF;i++) value_one[i-1]=$i ; next ; }
 {printf "%s %s %s %s %s %s\n",$1,$2,value_one[FNR],$4,$5,$6}

mybox $ awk -f ul.awk b.txt a.txt
1 4324 monday 6.2e+11 7687 67576
2 3565 tuesday 8760 5780 8778
3 7656 wednesday -3e+11 7099 79909
4 8768 thursday 8769 9879 0970
5 5878 friday 7.970e-1 9070 0709799
100000 3655 saturday 97879 96879 69899

however, I am not sure awk can handle 99999 columns.

Is this whate you are looking for ? (apart merging only one file)

edit 1 b.txt single column (thos lift any issue on awk, by the way).

mybox $ cat a.txt
1 4324 3673 6.2e+11 7687 67576
2 3565 8768 8760 5780 8778
3 7656 8793 -3e+11 7099 79909
4 8768 8965 8769 9879 0970
5 5878 9879 7.970e-1 9070 0709799
100000 3655 6868 97879 96879 69899
mybox $ cat b.txt
monday
tuesday
wednesday
thursday
friday
saturday

content of ul.awk

FILENAME == "b.txt" { value[FNR]=$i ; }
FILENAME != "b.txt" { printf "%s %s %s %s %s %s\n",$1,$2,value[FNR],$4,$5,$6}

mybox $ awk -f ul.awk b.txt a.txt
1 4324 monday  6.2e+11 7687 67576
2 3565 tuesday  8760 5780 8778
3 7656 wednesday  -3e+11 7099 79909
4 8768 thursday  8769 9879 0970
5 5878 friday  7.970e-1 9070 0709799
100000 3655 saturday 97879 96879 69899

is this nearing ?

my files a.txt and b.txt are single column each not one row multiple columns.and apart from merging into one file this is exactly what I am looking for. — Nilesh
– Nilesh, Commented Sep 8, 2014 at 11:55

user78605 · Accepted Answer · 2014-09-08 14:55:19Z

0

Another awk way without arrays, its a bit of a mess so i'll try and clean it up later

awk 'function get(file,L) {x=1
        while ( (getline < file) > 0) {if(NR==x)y=$0;x++}
        close(file)
        return y
        }
     ARGV[1]==FILENAME{d=$0;a=get(ARGV[2],$0);b=get(ARGV[3],$0);$0=d;$2=a;$3=b;print
     }' file file1 file2

answered Sep 8, 2014 at 14:55

user78605

Add a comment |

Stack Exchange Network

Replace data at specific positions in txt file using data from another file

5 Answers 5

Explanation

OUTPUT

You must log in to answer this question.

Linked

Hot Network Questions

Replace data at specific positions in txt file using data from another file

5 Answers 5

Explanation

OUTPUT

You must log in to answer this question.

Linked

Related

Hot Network Questions