Selecting rows based on value closest to

user_14456390 0
Viewed 44

I have a dataframe radiosondes which contains a lot of radiosonde data. Now there are hundreds of radiosondes being done, all with a unique timestamp, so the dataframe has a datetimeindex. What I want is a timeseries of the variables (temperature, pressure etc) based on a certain pressure level. So basically every individual radiosonde should give me the values of the other variables for a certain pressure level. The problem arises that the pressure interval isn't homogeneous, and is written in 2 decimals. Also every radiosonde has a different pressure interval because measurements were taken every second, and not based on pressure. What I did was the following:

x = radiosondes[(radiosondes['Press'] >= 500) & (radiosondes['Press'] <= 501)]

Now this line gave me somewhat correct data, but not exactly as you see in the results below: Some timestamps are included multiple times, because they have multiple measurements where the pressure was between 500 and 501 HPa.

                  Press  GeopHgt   Temp  RH  PO3  GPSHgt  O3
datetime                                                        
2019-09-21 05:00:00  500.86     5263  237.4  79  NaN  5279.0 NaN
2019-09-21 05:00:00  500.49     5268  237.4  78  NaN  5285.0 NaN
2019-09-21 05:00:00  500.12     5273  237.3  76  NaN  5290.0 NaN
2019-09-22 04:00:00  500.64     5359  243.5  54  NaN  5369.0 NaN
2019-09-22 04:00:00  500.14     5368  243.4  54  NaN  5378.0 NaN
...                     ...      ...    ...  ..  ...     ...  ..
2020-10-01 11:00:00  500.68     5443  244.6  63  NaN  5460.0 NaN
2020-10-01 11:00:00  500.29     5449  244.6  63  NaN  5466.0 NaN
2020-10-01 14:00:00  500.92     5465  245.1  29  NaN  5485.0 NaN
2020-10-01 14:00:00  500.55     5469  245.1  29  NaN  5490.0 NaN
2020-10-01 14:00:00  500.16     5474  245.1  28  NaN  5496.0 NaN

So what I want is that every radiosonde is included only once in the new timeseries. I would like to select the row where the pressure is closest too 500. So then the result would be something like:

                          Press  GeopHgt   Temp  RH  PO3  GPSHgt  O3
datetime                                                        
2019-09-21 05:00:00  500.12     5273  237.3  76  NaN  5290.0 NaN
2019-09-22 04:00:00  500.14     5368  243.4  54  NaN  5378.0 NaN
...                     ...      ...    ...  ..  ...     ...  ..
2020-10-01 11:00:00  500.29     5449  244.6  63  NaN  5466.0 NaN
2020-10-01 14:00:00  500.16     5474  245.1  28  NaN  5496.0 NaN

Hopefully it is clear what I meant here. Thanks very much in advance!

python
2 Answers

To achieve this you can do the following: if your dataframe is x, and considering that you look for the pressure as close to 500 as possible, so it is equal to the minimum pressure between 500 and 501:

print(x.loc[x.groupby("datetime")["Press"].idxmin()])

This will keep one line per datetime group, with the minimum pressure, so the closest to 500.

user_146621790

After your initial manipulation, do:

x.sort_values('Press').drop_duplicates('date').sort_index()

Afterwards, you might want to re-sort your dataframe with regard to the timestamp, which is trivial.

user_152135530
Related