python


Python/Pandas find closest value above/below in one Column


I have a large data frame (~1 million rows). I am ultimately going to be interpolating spectra between two age groups. However I need to first find the nearest values above and below any age I need to find.
The DataFrame briefly looks like this
Age Wavelength Luminosity
1
1
1
4
4
6
6
I need to be able to input 5, and return values 4 and 6. I am struggling to find a way to do this? This is what I tried :
def findnearest(array,value):
idx = np.searchsorted(array,value, side='left')
if idx > 125893.0:
return array[idx]
else:
return array[idx]
idx1 = np.searchsorted(array,value, side='right')
if idx1 < 2e10:
return array[idx1]
else:
return array [idx1-1]
C = findnearest(m05_010['age'], 5.12e7)
print(C)
This only returns one value, and not both. Is this the right path or should I be doing something different? Is there a better way?
I think you should use bisect, its a lot faster and is made for this purpose only.
from bisect import *
arr = np.array([1,1,1,4,4,4,4,4,4,4,6,6])
value = 5
lower = arr[bisect_left(arr, value) - 1]
above = arr[bisect_right(arr, value)]
lower, above
Output -
(4, 6)
Heres the time comparison from Ipython -
%timeit for x in range(100): arr[bisect_left(arr, value)]
Output -
10000 loops, best of 3: 92.4 µs per loop
And using searchsorted -
%timeit for x in range(100): arr[np.searchsorted(arr,value,'left')-1]
Output -
The slowest run took 7.62 times longer than the fastest. This could
mean that an intermediate result is being cached. 10000 loops, best of
3: 142 µs per loop
IIUC and assuming sorted input array, you can do something like this -
above = arr[np.searchsorted(arr,value,'left')-1]
below = arr[np.searchsorted(arr,value,'right')]
Sample runs -
Case 1: Without exact match for value
In [17]: arr = np.array([1,1,1,4,4,4,4,4,4,4,6,6])
In [18]: value = 5
In [19]: above = arr[np.searchsorted(arr,value,'left')-1]
...: below = arr[np.searchsorted(arr,value,'right')]
...:
In [20]: above, below
Out[20]: (4, 6)
Case 2: With exact match for value
In [33]: arr = np.array([1,1,1,4,4,4,4,4,4,4,5,5,5,6,6])
In [34]: value = 5
In [35]: above = arr[np.searchsorted(arr,value,'left')-1]
...: below = arr[np.searchsorted(arr,value,'right')]
...:
In [36]: above, below
Out[36]: (4, 6)

Related Links

Merge rows based on values and key string in awk, sed or python
i find the files at the end empty ,python
How do I prevent my FOR loop from ending too early?
PyInstaller 2.1 import custom package
Python: Read File and append a lot of list of 28 lines
Numba Matrix Vector multiplication
How to improve very inefficient numpy code for calculating correlation
Flask-strange routing issue
Upgrade path for re-usable apps with South AND django 1.7 migrations
Making scikit-learn train on all training data after cross-validation
matplotlib does not display latex fonts correctly
How to use py2exe with third-party libraries
How to extract two numbers from input line and tab delimit them?
Copy selected lines of text from one file to another with command line argument with or without spaces
TypeError: can't use a string pattern on a bytes-like object
Why does my code skip to the end and ignore parts of my code?

Categories

HOME
numpy
signalr
vhdl
sed
textwatcher
wildcard
avro
computer-vision
docker-swarm
concourse
matplotlib
webdav
jpanel
data-science-experience
vsftpd
cockroachdb
x-frame-options
webmethods
react-leaflet
multiple-monitors
python-unicode
atlassian-plugin-sdk
google-api-java-client
ghost-inspector
restfb
searchbar
csh
printdocument
data-cleansing
osmdroid
lync-2013
spring-data-neo4j
helper
autofill
hibernate-cache
android-vpn-service
backup-strategies
body-parser
npm-publish
swagger-editor
elfinder
ntfs-mft
visualstudio.testtools
infusionsoft
ddms
nsexception
nashorn
knockout-3.0
productivity
nsjsonserialization
jenkins-jira-trigger
livescribe
multi-agent
fedora20
adler32
ingres
pspice
master
spring-ioc
tomcat5
pax-web
django-south
qt-linguist
servlet-3.0
app.xaml
webhdfs
findcontrol
uos
jazz
sysfs
ctrlp
lexicographic
tilestache
cocoascript
odftoolkit
cg
firebug-lite
author
humanizer
coin-flipping
help-viewer
document-database
trimming
installshield-2009
multiple-conditions
shiva3d
snapjs
dropdownlistfor
crocodoc
usn
sunspot-rails
caliper
getstring
applicationcontext
supersized
v4l
pivotal-crm
pureftpd
carbide
jquery-ui-button
linfu
code-design
swfloader
windows-controls
nt4
inline-if
project-lifecycle

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App