I have a short list (List A) and a long one (List B), and I need to find inside of List B the closest match to each of List A's numbers.
Pic related.
Except I have one List A about 150 entries long, and about 100 List Bs about 10k entries long, so it would take me a very long time to do them all by hand like in the example.
Is there a way to do it automatically?
Thank you very much in advance.
Other urls found in this thread:
mrexcel.com
twitter.com
Binary sorting.
>Binary sorting
I can't program.
Is there some software that I can use?
For each i in A:
For each j in B:
find the minimum (i - j) squared
You can use the excel functions to find this.
I don't understand this but I'll Google it, thank you.
There are functions in excel.
There is a min function.
I'll make a script that does this for you for 5$ bucks.
I'm finding some results, thank you very much.
I'd accept your offer if I could.
Unfortunately I'm a neet and I own almost nothing, so I can't pay you.
I would basically take the entire List A and then loop through List B checking if each number in B is closer than the previous for all in List A.
So you would assign 117 to all in the first item of List A, then in the second loop check if 124 is closer to the number in List A than 117 was, and continue.
So you're looping List A inside List B.
The check can be a simple subtraction of B from A, changed to a positive number, if the result is smaller then the new number takes the place.
Am I retarded or is this all op needs:
arr_a =sorted( [n1, n2, n3, ...])
arr_b = sorted([n1, n2, n3, ...])
for a, b in zip(arr_a, arr_b):
print(a, 'closest to: ', b)
But I can't code, so I'm asking for some software that does it for me.
I'm currently trying in Excel
Nope. It's that easy.
Feel like this would match 261 to 124...
Can you match two items from list A to one item from list B?
You guys are dumb.
arr_a =sorted( [118, 261, 315, 395])
arr_b = sorted([117, 124, 257, 296, 348, 351, 395, 408])
for a, b in zip(arr_a, arr_b):
print(a, 'closest to: ', b)
118 closest to: 117
261 closest to: 124
315 closest to: 257
395 closest to: 296
>Can you match two items from list A to one item from list B?
Yes.
OP here.
I managed with Excel using this:
mrexcel.com
It took me a while because my version is not in Italian so I had to translate the function and change a few things, but it seems to be working.
Thanks a lot to everyone who tried to help. I really appreciate it.
Glad you managed it, here's what I could come up with in python.
#!/usr/bin/env python3
A = [118, 261, 315, 395]
B = [117, 124, 257, 296, 348, 351, 395, 408]
S = dict()
for a, b in [ (a, b) for a in A for b in B ]:
if a not in S or abs(a - b) < abs(a - S[a]):
S[a] = b
print(S)
I can't find words for how atrocious this is.
I also tried to make the same thing work, and my Excel is in Russian, and apparently it requires all function to be written using Russian functions names, English ones don't work.
What was Microsoft thinking? I'm sure this was not a thing in earlier versions of Excel, when did this tsart?
Thank you.
Just out of curiosity, with this you need to paste in the tho lists for each script (since there are several List Bs), correct?
Yeah, it took me an hour to fiddle with words and punctuation to make it work.
I guess it's for your average accountants and cubicle workers who don't know English and would be totally confused by weird words that don't make sense, but for people who do things in English it's an absolute pain.
Especially if there are no resources in your language for what you need to do.
it's just a mockup, we could easily read the lists from text files, one number by line
Ah, neat.
>Just out of curiosity, with this you need to paste in the tho lists for each script (since there are several List Bs), correct?
The best idea, I guess, would be to put List A into the script and then pass List B to the script at runtime, or have it read from a file that you just change out.
I see, thank you.
Now it reads all files in the "data" folder, one number per line, and outputs the corresponding numbers from the first list that has to be hardcoded in the script
#!/usr/bin/env python3
import os
path = 'data'
A = [118, 261, 315, 395]
for f in os.listdir(path):
B = [ int(line) for line in open(path + '/' + f) ]
S = dict()
for a, b in [ (a, b) for a in A for b in B ]:
if a not in S or abs(a - b) < abs(a - S[a]):
S[a] = b
for v in S.values():
print(v)
print('---')
>for a, b in [ (a, b) for a in A for b in B ]:
stop this shit
import itertools
for x, y in itertools.product(a,b):
print(x, y)
it's that bad?
how about this:
var arr_a = [118, 261, 315, 395]
var arr_b = [117, 124, 257, 296, 348, 351, 395, 408]
var i,j, difference, test;
j = 0;
for(i = 0; i < arr_a.length; i++) {
difference = Infinity;
for(; j < arr_b.length; j++) {
test = Math.abs(arr_a[i] - arr_b[j]);
if(test > difference) {
j--;
break;
}
difference = test;
}
console.log(arr_a[i], arr_b[j]);
}
\thread. O(n^2) but op doesnt need better
Thank you, but I already solved it with Excel (see ).
Also I can't code, so I don't really know what to do with that, but I appreciate your effort, thank you very much.