awk,compare,match,mismatch , Compare two files and append the values, leave the mismatches as such in the output file
Compare two files and append the values, leave the mismatches as such in the output file
Question:
Tag: awk,compare,match,mismatch
I'm trying to match two files,file1.txt(50,000 lines), file2.txt(55,000 lines). I want to campare file2 to file 1 extract the values of column 2 and 3 and leave the mismatches as such. Output file must contain all the ids from file2 i.e., it should have 55000 lines. Note: All the ids in file 1 are not present in file2. i.e the actual matches could be less than 50,000.
file1.txt
ab1 12 345
ab2 9 456
gh67 6 987
file2.txt
ab2 0 0
ab1 0 345
nh7 0 0
gh67 6 987
Output
ab2 9 456
ab1 12 345
nh7 0 0
gh67 6 987
This is what i tried but it only print the matches (so instead of 55,000 lines i have 49,000 lines in my output file)
awk "NR==FNR {f[$1]=$0;next}$1 in f{print f[$1],$0}" file1.txt file2.txt >output.txt
Answer:
This awk script will work
NR == FNR {
a[$1] = $0
next
}
$1 in a {
split(a[$1], b)
print $1, (b[2] == $2 ? $2 : b[2]), (b[3] == $3 ? $3 : b[3])
}
!($1 in a)
If you save this as a.awk and run
awk -f a.awk foo.txt foo1.txt
This will output
ab2 9 456
ab1 12 345
nh7 0 0
gh67 6 987
Related:
excel,vba,excel-vba,compare
I have 2 different files which have different headers, for example: OldfileHeaders | NewFileheaders ID | Test ID Date | New date and so on. I am trying to compare the data in both sheets and see if they match. The rows of data may be in different order and...
awk
How to make this command line: awk -F "," '{NF>0?$NF:$0}' to print the last field of a line if NF>0, otherwise print the whole line? Working data bogota dept math, bogota ...
bash,csv,awk
I have CSV file which could look like this: name1;1;11880 name2;1;260.483 name3;1;3355.82 name4;1;4179.48 name1;2;10740.4 name2;2;1868.69 name3;2;341.375 name4;2;4783.9 there could more or less rows and I need to split it into multiple .dat files each containing rows with the same value of the second column of this file. (Then I will...
python,python-2.7,csv,compare
I am trying to compare two csv files and find the rows that are different using python 2.7. The rows are considered different when all columns are not the same. The files will be the same format with all the same columns and will be in this format. oldfile.csv ID...
regex,r,bash,awk,sed
I have a file like this: aaa b b ccc 345 ddd fgt f u 3456 e r der der 5 674 As you can see the only way that we can separate the columns is by finding columns that have only one or more spaces. How can we identify...
awk,etl
I'd like to be able to work with a file in awk where records are separated by a blank line and each field consists of a name followed by a colon, some optional whitespace to be ignored/discarded, followed by a value. E.g. Name: Smith, John Age: 42 Name: Jones, Mary...
unix,awk
I've been trying to extract lines where a number in one columns falls within a particular range. Lets say I have a file that looks as so: chrom prediction chrom1 0.75 chrom2 0.5 chrom4 0.76 If I wanted to print lines where the prediction value was in the range from...
regex,perl,shell,awk
I have test.dat file with values given below: 20150202,abc,,,,3625.300000,,,,,-5,,,,,,,,,,,,,,,,,,,,,, 20150202,def,,,,32.585,,,,,0,,,,,,,,,,,,,,,,,,,,,, 20150202,xyz,,,,12,,,,,0.004167,,,,,,,,,,,,,,,,,,,,,, My expected output is shown below: 20150202,abc,,,,3625.300000,,,,,-5.,,,,,,,,,,,,,,,,,,,,,, ^. added here 20150202,def,,,,32.585,,,,,0.,,,,,,,,,,,,,,,,,,,,,, ^. added here 20150202,xyz,,,,12.,,,,,0.004167,,,,,,,,,,,,,,,,,,,,,, ^. added here So if column 6 and 11 doesn't have decimal point in it, then we should add '.' at the end of...
bash,awk,sed,grep
I have a text file and want to extract all interfaces matching "blue" random text random text random text random text random text int 1 random text blue random text random text int 2 random text random text red random text int 3 random text random text random text blue...
linux,bash,awk,sed,sh
I am trying to use a script to append the host name at the end of a multi-line entry of a specific Host_Alias field in sudoers file. The current sudoers file has an entry similar to : Host_Alias srv_linuxestate= \ host10,host12,host13,host1,host50,\ host16,host1,host2,host11,host15,host21,\ host3,host14 My required output would be something like...
wpf,sorting,listbox,compare,collectionview
I have a listbox DropPrice MyPrice Price1 Price2 I want to sort it like this Price1 Price2 DropPrice MyPrice I mean, if there's an item that starts with the sequence "price", it gets priority, else the smallest string should get the priority. My source code: var lcv = (ListCollectionView)(CollectionViewSource.GetDefaultView(_itemsSource)); var...
python,python-2.7,csv,compare
I am trying to compare two csv files in python and save the difference to a third csv file in python 2.7. import csv f1 = open ("olddata/file1.csv") oldFile1 = csv.reader(f1) oldList1 = [] for row in oldFile1: oldList1.append(row) f2 = open ("newdata/file2.csv") oldFile2 = csv.reader(f2) oldList2 = [] for...
bash,perl,command-line,awk,sed
Hello I have a file that has multiple Headers in it that I need to have turned into column values. The file looks like this: Day1 1,Smith,London 2,Bruce,Seattle 5,Will,Dallas Day2 1,Mike,Frisco 4,James,LA I would like the file to end up looking like this: Day1,1,Smith,London Day1,2,Bruce,Seattle Day1,5,Will,Dallas Day2,1,Mike,Frisco Day2,4,James,LA The file...
arrays,bash,awk
I have a bash array like myarray = (1 2 3 4 5 ... n) Also I am reading a file with an input of only one line for example: 1 2 3 4 5 ... n I am reading it line by line into an array and printing it...
shell,command-line,awk,terminal
I'm a complete newbie to using command-line utilities and am wondering how to process information as following: mapping.txt: 80 001 002 81 011 012 013 014 82 021 022 ... input.txt: 81 103823044 80 103823054 81 103823064 ... Desired output.txt: 103823044|011| 103823044|012| 103823044|013| 103823044|014| 103823054|001| 103823054|002| 103823064|011| 103823064|012| 103823064|013| 103823064|014|...
bash,shell,unix,awk
I have csv file having content like below : 1|2|3 4|5|6 7|8|9 Now I would like to find the numbers which are divisible by 3 using shell scripting. I would like to use awk command for this. I am learning shell scripting. So could you please help me out to...
unix,awk
I've been trying to figure out a way to select lines that are unique based on the values of two columns. For example, here is a sample/example of my file: chr1 10 12 chr1 10 12 chr1 10 11 chr1 9 12 chr2 15 20 And this is what I...
bash,awk
Is it possible? I was wondering how to do: Count fields differentiated by comma. Only the obtained first field of the previous step, count words differentiated by space. If there is more than 2 words, print NF, otherwise $0. Input cellular biol immunogenet, rosario escuela estadist, medellin medellin Expected output...
linux,bash,csv,awk
i have CSV file with some database benchmark results here is the example: Date;dbms;type;description;W;D;S;results;time;id Mon Jun 15 14:22:20 CEST 2015;sqlite;on-disk;text;2;1;1;570;265;50 Mon Jun 15 14:22:20 CEST 2015;sqlite;on-disk;text;2;1;1;420;215;50 Mon Jun 15 14:22:20 CEST 2015;sqlite;on-disk;text;2;1;1;500;365;50 Mon Jun 15 14:22:20 CEST 2015;sqlite;on-disk;text;2;1;1;530;255;50 Mon Jun 15 14:22:20 CEST 2015;hsql;on-disk;text;2;1;1;870;265;99 Mon Jun 15 14:22:20 CEST 2015;hsql;on-disk;text;2;1;1;620;215;99...
linux,bash,awk,scripting
I want to calculate the average of the 5th column (last column) excluding the rows with the value "9999". Would appreciate your feedback. 77.300 16 1 3.6112914285714268 9.4 77.300 16 2 -0.001737142857145102 20.0 77.300 16 3 5.1570742857142857 8.9 77.300 17 0 3.6112914285714268 8.9 77.300 17 1 2.9484342857142849 11.7 77.300 17...
python,list,loops,if-statement,compare
When I run my code it tells me: Type Error: unorderable types: str() < float(). I can't figure out why it won't let me compare these two numbers. The list I am using is defined, and the numbers in it have been redefined as floats, so I'm not sure what...
string,awk,substring,extract,cut
I have a list of files with the following file name format: [some unknown amount of characters][_d][yyyymmdd][some unknown amount of characters] I want to extract the substring that contains the date (yyyymmdd) which I know will always be proceeded by "_d". So basically I want to extract the first 8...
awk,printf,gawk
I am trying to format the output of the AWK's printf() function. More precisely, I am trying to print a matrix with very long rows and I would like to wrap them and continue on the next line. What I am trying to do is best illustrated using Fortran. Consider...
linux,string,bash,unix,awk
In bash scripting what's an efficient way to do the following please? var="fooo_barrrr" What is the best way to remove all characters before and including the '_' so that var becomes "barrrr" please?...
bash,awk,sed
I have a csv with lines like this: Last,First,A00XXXXXX,1492-01-10,2015-06-17,,Sentence Skills 104,,Elementary Algebra 38, Last,First,A00XXXXXX,1492-01-10,2015-06-17,,,,Elementary Algebra 101,College Level Math 56 Last,First,A00XXXXXX,1492-01-10,2015-06-17,Reading Comprehension 102,,,, Last,First,A00XXXXXX,1492-01-10,2015-06-17,,,,Elementary Algebra 118,College Level Math 97 I want to remove the word "Reading Comprehension" but leave the number, but only if its in column 6, if its in...
java,calendar,compare
I'm having some trouble with comparing 2 calendar objects. Here's my code: String date = "06/19/2015"; Calendar c = Calendar.getInstance(); String days="", months="", years=""; Scanner sc = new Scanner(date); sc.useDelimiter("/"); while(sc.hasNext()){ months = sc.next(); days = sc.next(); years = sc.next(); } int day = Integer.parseInt(days); int month = Integer.parseInt(months); int...
python,unix,awk
Here is a sample row of input data : header: id,indicator,{(pid,days_remaining)} row: id_558314,1,{(property_66021,7),(property_24444,1),(property_285395,6)} Expected output of data : header: id,indicator,pid,days_remaining row1: id_558314,1,property_66021,7 row2: id_558314,1,property_24444,1 row3: id_558314,1,property_285395,6 Is this something easily doable using awk/unix/python? Thanks in advance. Your time and help is appreciated....
javascript,boolean,compare,boolean-expression
This question already has an answer here: chaining double equals == returns false 3 answers Let's var a = "first", b = "second", c = "first";. Expression a == c returns true, (of course!); a == c is true too. Then why does a == a == c return...
table,awk,rows,trace
I have a table (.tr file) with different rows (events). **Event** **Time** **PacketLength** PacketId sent 1 100 1 dropped 2 100 1 sent 3 100 2 sent 4.5 100 3 dropped 5 100 2 sent 6 100 4 sent 7 100 5 sent 8 100 6 sent 10 100 7...
javascript,angularjs,select,filter,compare
I would like to setup two select inputs so that the combined value of either select input should be less than or equal to a specific value. Newbie to the AngularJS world so I may have overlooked something. I assume a filter but it might be ng-change that I should...
windows,awk,system
I am currently running this script in Windows 7. So, I have a program that is meant to color-code output from another command (mkmk) and tally up varying numbers of errors and other notable stats, etc. So right now, it starts as a batch file which Turns off echo Sets...
regex,bash,shell,awk
Edited - TLDR: Using awk to parse fields that include commas. # original config file - confile1 $ cat confile1 list=( app1,"HOSTNAME - port - application name - alert1",99.0,99.0 app2,"HOSTNAME - port - application name - alert1",99.0,99.0 app3,"HOSTNAME - port - service name - alert2",99.0,99.0 web1,"URL - HOSTNAMES(01,02) - http://someurl.com/...
.net,vb.net,linq,compare,compareto
This is a partially defined function that I'm using to retrieve the specified lines of a textfile, by passing the index of the lines I want: Public Shared Function GetLines(ByVal sourceFilepath As String, ByVal lineNumbers As IEnumerable(Of Integer)) As IEnumerable(Of String) Return File.ReadAllLines(sourceFilepath, encoding). Select(Function(line As String, index As Integer)...
linux,awk
I have a file like this and would like to print $0 except the first two and last three lines in linux. Tried awk command but no luck, is there any options I am using the following command - I suppose I am doing something wrong, but not able to...
bash,awk,sed
I have a csv file like this: ,College Level Math 55,Elementary Algebra 112 ,Elementary Algebra 79, ,College Level Math 102,Elementary Algebra 54 ,,College Level Math 54 I need an awk or sed command that does the following if College Level Math *,Elementary Alegrbra * exist flip it so it looks...
linux,shell,command-line,awk,sed
Given a CSV file: id, fruit, binary 1, apple, 1 2, orange, 0 3, pear, 1 4, apple, 0 5, peach, 0 6, apple, 1 How can i calculate for each unique values in fruit, the number of times the binary value =1 / number of occurences of that fruit...
linux,bash,for-loop,awk
I have a script that I need to turn into a loop, the script works exactly as I need it to (My Awk-fu is extremely weak, so be nice) when I put in the file name like the example below. #!/bin/bash awk -v FS='(<LastName>|<\/LastName>)' '{print $2}' 17822624.xml >> test.csv awk...
shell,awk,sed,grep,sh
This question already has an answer here: Remove duplicates from text file based on second text file 4 answers I have a data.txt file with a lot of lines in it and a lines.txt that contains some lines. I want to delete all lines from data.txt that match any...
android,performance,sqlite,compare
We are developing an Android App based on a product that already has a website. As a result, when launching the Android app it may happen that a lot of things differ between the local SQLite database and our central database. What is the fastest way to synchronize the Android...
sql,awk,sqlite3
I have to parse very big (abt 40Gb) text files(logs) very often. Usually AWK/grep is enough for my needs, but logs are growing and now I'm curious whether sqlite3 will allow me to do the same things more efficiently. I chosen sqlite for my tests as it installed out-of-the-box (SQLite...
c,integer,compare,bit-manipulation,string-comparison
I have small vectors. Each of them is made of 10 integers which are between 0 and 15. This means that every element in a vector can be written using 4 bits. Hence I can concatenate my vector elements and store the whole vector in a single long type (in...
javascript,web,compare,case-sensitive
I have problem with a variable I made (it's a string) in JavaScript. It will be prompt from the user and then with the switch I will check if it is true or not. Then when I input it upper case it will say it is identified as a another...
bash,awk,sed
pattern1 a b pattern2 cd pattern1 re pattern2 gh pattern1 ef pattern2 qw e I can show all matching pattern by sed -n '/pattern1/,/pattern2/p' Choose the second matching pattern or any Nth by awk -vM=2 '(x+=/pattern1/)==M&&x+=/pattern2/' file pattern1 re pattern2 Print only last matching pattern by awk 'x+=/pattern1|pattern2/{!y++&&B="";B=B?B"\n"$0:$0;x==2&&y=x=0}END{print B}' file...
linux,bash,shell,awk,sed
I have a CSV file with columns A,B,C,D. Column D contains values on a scale of 0 to 1. I want to use AWK to write to a new column E base in values in column D. For example: if value in column D <0.7, value in column E =...
regex,awk,sed,gawk
I have a semicolon separated csv-file which looks like this: column1;column2;;123564;128;;IJL;value;;;;;3705;;;;;;;; column1;column2;;26789786413423;;CCE;value value;;;;;;3705;;;;;;;; column1;column2;;4564564;128;;SSE;value;;;;;;;;;;;;; column1;column2;;4645646;128;;JJY;someting X;;;;;;;;;;;;; column1;column2;;123132;128;;ASA;X value;;;;;;;;;;;;; column1;column2;;45643123;128;;TT;9 someting;;;;;;;;;;;;; column1;column2;;456464;128;;KK;VALUE 9 VALUE;;;;;;;;;;;;; column1;column2;;4646;128;;ST;value 6;;;;;;;;;;;;;...
linux,bash,awk
I have a very big CSV file (aprox. 10.000 rows and 400 columns) and I need to modify certain columns (like 15, 156, 220) to change format from 20140321132233 to 2014-03-21 13:22:33. All fields that I need to modify are datetime. I saw some examples using awk but for math...
bash,awk,sed
How can I replace [a-z],[a-z] with [a-z], [a-z] and keeping the letters? Input suny stony brook, stony brook,usa. Output suny stony brook, stony brook, usa. What I have tried sed 's/[a-z],[a-z]/[a-z], [a-z]/g' <<< "suny stony brook, stony brook,usa." sed 's/[a-z],[a-z]/, /g' <<< "suny stony brook, stony brook,usa." ...
linux,bash,shell,unix,awk
I would like to extract sum, mean and average in each 6 numbers interval from a column. I found many discussions related to this problem, but all those are for whole column. e.g. To compute sum of a column: awk '{sum+=$1} END { print sum}' To calculate Average: awk '{sum+=$1}...
bash,awk
I feel like this should be a fairly straight forward question, but I cant seem to get it to work. I have a csv file and I need to add comma after the nth comma in each row. I believe I have to use gsub to get it to loop....
sql-server,tsql,sql-update,compare
I have 2 tables and want to compare them and modify tableA (set NameMod = 1) if it has different rows. To compare tables I use: select Id, Name from tableB except select Id, Name from tableA And then I want to modify tableA: update tableA Set NameMod = 1...