Insert value of column based on file name matching


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Insert value of column based on file name matching
# 1  
Old 08-28-2015
Insert value of column based on file name matching

At the top of the XYZ file, I need to insert the ABC data value of column 2 only when ABC column 1 matches the prefix XYZ file name (not the ".txt"). Is there an awk solution for this?

ABC Data

Code:
0101	0.54
0102	0.48
0103	1.63

XYZ File Name

Code:
0101.txt
0102.txt
0103.txt

Expected output
XYZ File Name

Code:
> -Z0.54
abcdefg
123456

# 2  
Old 08-28-2015
How about this bash solution:
Code:
declare -A TMP
while read A B; do TMP[$A]=$B; done < ABC
while read A; do echo ${TMP[${A%.*}]} | cat - $A; done < XYZ

# 3  
Old 08-28-2015
Thanks for replying Rudy.

Just to be clear I have a single ABC file but multiple XYZ files.

I did not get your code to work on a either a single or multiple XYZ files.

Any other options?
# 4  
Old 08-28-2015
Actually, I was wildly guessing WHAT your spec could mean. My proposal worked fine with three files whose names were listed in a "file name" file which in turn was matched against an ABC data file.

Obviously my interpretation regarding your file structure was wrong. Please post the file structure in your directory and some sample files.
# 5  
Old 08-28-2015
I hope this makes sense.

File ABC

Code:
0101    0.54
0102    0.48

File 0101.txt

Code:
-111   34
-111   33
-111   33

File 0102.txt

Code:
-112   34
-112   33
-112   33


Expected output

File 0101-new.txt

Code:
> -Z0.54
-111   34
-111   33
-111   33

Expected output

File 0102-new.txt

Code:
> -Z0.48
-112   34
-112   33
-112   33

# 6  
Old 08-28-2015
So - here's the results:
Code:
declare -A TMP
while read A B; do TMP[$A]=$B; done <ABC
while read A; do echo ${TMP[${A%.*}]} | cat - $A > ${A}.new; done < XYZ
cf 010?.txt.new
0101.txt.new:
0.54
-111   34
-111   33
-111   33
0102.txt.new:
0.48
-112   34
-112   33
-112   33
0103.txt.new:
1.63
-113   34
-113   33
-114   33

What be the errors that you encounter?
# 7  
Old 08-28-2015
awk version:
Code:
awk '
  NR==FNR {
    A[$1]=$2
    next
  }
  FNR==1 {
    close(f)
    split(FILENAME,F,".")
    f=F[1] "-new." F[2]
    print "> -Z" A[F[1]] > f
  }
  {
    print >f
  }
' abc [0-9][0-9][0-9][0-9].txt

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to insert data into black column( Secound Column ) in excel (.XLSX) file using shell script?

Source Code of the original script is down below please run the script and try to solve this problem this is my data and I want it column wise 2019-03-20 13:00:00:000 2019-03-20 15:00:00:000 1 Operating System LAB 0 1 1 1 1 1 1 1 1 1 0 1 (5 Replies)
Discussion started by: Shubham1182
5 Replies

2. Shell Programming and Scripting

Insert Columns before the last Column based on the Count of Delimiters

Hi, I have a requirement where in I need to insert delimiters before the last column of the total delimiters is less than a specified number. Say if the delimiters is less than 139, I need to insert 2 columns ( with blanks) before the last field awk -F 'Ç' '{ if (NF-1 < 139)} END { "Insert 2... (5 Replies)
Discussion started by: arunkesi
5 Replies

3. Shell Programming and Scripting

Based on column in file1, find match in file2 and print matching lines

file1: file2: I need to find matches for any lines in file1 that appear in file2. Desired output is '>' plus the file1 term, followed by the line after the match in file2 (so the title is a little misleading): This is honestly beyond what I can do without spending the whole night on it, so I'm... (2 Replies)
Discussion started by: pathunkathunk
2 Replies

4. Shell Programming and Scripting

awk print non matching lines based on column

My item was not answered on previous thread as code given did not work I wanted to print records from file2 where comparing column 1 and 16 for both files find rows where column 16 in file 1 does not match column 16 in file 2 Here was CODE give to issue ~/unix.com$ cat f1... (0 Replies)
Discussion started by: sigh2010
0 Replies

5. UNIX for Dummies Questions & Answers

Removing Lines based on matching first column

I have a file1 that looks like this: File 1 a b b c c e d e and a file 2 that looks like this: File 2 b c e e Note that file 2 is the right hand column from file1. I want to remove any lines from file1 that begin with the column in file2. In this case the desired output... (6 Replies)
Discussion started by: kschiltz55
6 Replies

6. Shell Programming and Scripting

Matching 2 files based on one column

Hi, On a similar subject, the following. I have two files: file1.txt dbSNP_rsID,Chromosome,Position,Gene rs10399749,chr. 01,45162,? rs4030303,chr. 01,72434,? rs4030300,chr. 01,72515,? rs940550,chr. 01,78032,? rs13328714,chr. 01,81468,? rs11490937,chr. 01,222077,? rs6683466,chr.... (5 Replies)
Discussion started by: swvanderlaan
5 Replies

7. Shell Programming and Scripting

Matching words based on column headers

Hi , Pls help on this. Input file: NAME1 BSC1 TEXT ID 1 MAINSFAIL TEXT ID 2 DGON TEXT ID 3 lOADONDG NAME2 BSC2 TEXT ID 1 DGON TEXT ID 3 lOADONG (1 Reply)
Discussion started by: bha148
1 Replies

8. Shell Programming and Scripting

Insert new line based on numerical number of column

My input file: Class Number Position Range 1 Initial 50 1 Initial 50 2 Terminal 150 2 Terminal 20 2 Single 10 3 Single 20 4 Double 50 5 Initial 50 5 Initial 60 Class Number... (11 Replies)
Discussion started by: patrick87
11 Replies

9. Shell Programming and Scripting

insert text into another file after matching pattern

i am not sure what i should be using but would like a simple command that is able to insert a certain block of text that i define or from another text file into a xml file after a certain match is done for e.g insert the text </servlet-mapping> <!-- beechac added - for epic post-->... (3 Replies)
Discussion started by: cookie23patel
3 Replies

10. Shell Programming and Scripting

Insert comma based on max number of column

Hi, I am new to unix shell shell scripting. I have a specific requirement where I need to append comma's based on the max number of column in the file. Eg: If my source file look something like this, sengwa,china tom,america,northamerica smith,america walter My output file... (8 Replies)
Discussion started by: nicholas_ejn
8 Replies
Login or Register to Ask a Question