Search code examples
python-3.xpandasdataframesqlitescipy

Why would SciPy stats.percentileofscore return an array instead of a scaler beginning with version 1.9?

I inherited an older Python install and ran development on it. I felt it was a reasonable time to bring the dev environment up to date. I am encountering a behavior change on my stats.percentileofscore call that I've narrowed down to a SciPy version change between 1.8.1 and 1.9.

In 1.8.1 I get a single value returned with the percentile rating from the query value. Beginning with 1.9, I'm getting what I think is an array returned. The query value is read in as a string from a text file while the database values are numeric.

Version 1.8.1 output. The "good" values are the long decimal numbers:

station_id,station_name,nfdr_date,nfdr_time,nfdr_type,fuel_model,wind_speed,woody_fuel_moisture,herbaceous_fuel_moisture,one_hr_tl_fuel_moisture,ten_hr_tl_fuel_moisture,hun_hr_tl_fuel_moisture,thou_hr_tl_fuel_moisture,thou_hr_tl_fuel_moisture_pctile,xthou_hr_tl_fuel_moisture,ignition_component,spread_component,energy_release_component,energy_release_component_pctile,burning_index,fire_load_index,staffing_class,adjective_rating,kbdi,lightning_risk,lightning_occurrance_index,todays_human_caused_risk,human_caused_occurrance_index
101100,PITTSBURG LANDING,090824,13,N,16Y4A ,,60,30,5,7,8,9,-999,-100,58,9,73,-999,59,42,,V ,640,0,0,0,0
101100,PITTSBURG LANDING,090824,13,N,16Y4A ,,60,30,5,7,8,9,-999,-100,58,9,73,-999,59,42,,V ,640,0,0,0,0
350113,TIDEWATER,090824,13,N,16Y1P ,,171,205,21,21,18,18,-999,-100,0,1,19,-999,11,8,,H ,704,0,0,0,0
350208,TILLAMOOK,090824,13,N,16Y1P ,,197,245,18,22,22,22,-999,-100,2,1,11,-999,10,7,,,632,0,0,0,0
350215,CEDAR,090824,13,N,16Y2P ,,172,207,15,20,16,16,10.015863859244304,-100,4,2,28,89.20536486876262,18,13,,M ,671,0,0,0,0
350216,SOUTH FORK,090824,13,N,16Y2P ,,157,182,15,18,15,15,4.924297043979813,-100,5,2,33,94.96755587599135,22,15,,,733,0,0,0,0
350308,MILLER,090824,13,N,16Y1P ,,180,219,18,22,19,18,-999,-100,2,1,19,-999,13,9,,,672,0,0,0,0
350505,RYE MOUNTAIN,090824,13,N,16Y1P ,,167,197,17,20,17,17,-999,-100,2,1,26,-999,14,10,,M ,764,0,0,0,0
350604,LOG CREEK,090824,13,N,16Y1P ,,162,192,13,18,15,16,7.088150289017341,-100,6,1,32,95.15173410404624,18,12,,,707,0,0,0,0
350718,REDBOX,090824,13,N,16Y1P ,,151,175,10,17,13,14,2.480173035328046,-100,13,2,40,94.93871665465032,21,15,,,723,0,0,0,0
350726,WANDERERS PEAK,090824,13,N,16Y2P ,,150,172,11,13,11,13,1.9250180245133381,-100,13,2,47,97.23864455659697,26,19,,,682,0,0,0,0

Version 1.9 and after output. The "bad" values are the bracketed values:

station_id,station_name,nfdr_date,nfdr_time,nfdr_type,fuel_model,wind_speed,woody_fuel_moisture,herbaceous_fuel_moisture,one_hr_tl_fuel_moisture,ten_hr_tl_fuel_moisture,hun_hr_tl_fuel_moisture,thou_hr_tl_fuel_moisture,thou_hr_tl_fuel_moisture_pctile,xthou_hr_tl_fuel_moisture,ignition_component,spread_component,energy_release_component,energy_release_component_pctile,burning_index,fire_load_index,staffing_class,adjective_rating,kbdi,lightning_risk,lightning_occurrance_index,todays_human_caused_risk,human_caused_occurrance_index
101100,PITTSBURG LANDING,090824,13,N,16Y4A ,,60,30,5,7,8,9,-999,-100,58,9,73,-999,59,42,,V ,640,0,0,0,0
350113,TIDEWATER,090824,13,N,16Y1P ,,171,205,21,21,18,18,-999,-100,0,1,19,-999,11,8,,H ,704,0,0,0,0
350208,TILLAMOOK,090824,13,N,16Y1P ,,197,245,18,22,22,22,-999,-100,2,1,11,-999,10,7,,,632,0,0,0,0
350215,CEDAR,090824,13,N,16Y2P ,,172,207,15,20,16,16,[0. 0. 0. ... 0. 0. 0.],-100,4,2,28,[0.01442169 0.01442169 0.01442169 ... 0.01442169 0.01442169 0.01442169],18,13,,M ,671,0,0,0,0
350216,SOUTH FORK,090824,13,N,16Y2P ,,157,182,15,18,15,15,[0. 0. 0. ... 0. 0. 0.],-100,5,2,33,[0.01441961 0.01441961 0.01441961 ... 0.01441961 0.01441961 0.01441961],22,15,,,733,0,0,0,0
350308,MILLER,090824,13,N,16Y1P ,,180,219,18,22,19,18,-999,-100,2,1,19,-999,13,9,,,672,0,0,0,0
350505,RYE MOUNTAIN,090824,13,N,16Y1P ,,167,197,17,20,17,17,-999,-100,2,1,26,-999,14,10,,M ,764,0,0,0,0
350604,LOG CREEK,090824,13,N,16Y1P ,,162,192,13,18,15,16,[0. 0. 0. ... 0. 0. 0.],-100,6,1,32,[0.01445087 0.01445087 0.01445087 ... 0.01445087 0.01445087 0.01445087],18,12,,,707,0,0,0,0
350718,REDBOX,090824,13,N,16Y1P ,,151,175,10,17,13,14,[0. 0. 0. ... 0. 0. 0.],-100,13,2,40,[0.01441961 0.01441961 0.01441961 ... 0.01441961 0.01441961 0.01441961],21,15,,,723,0,0,0,0
350726,WANDERERS PEAK,090824,13,N,16Y2P ,,150,172,11,13,11,13,[0. 0. 0. ... 0. 0. 0.],-100,13,2,47,[0.01441961 0.01441961 0.01441961 ... 0.01441961 0.01441961 0.01441961],26,19,,,682,0,0,0,0

Here is a sample bit of the database I'm calculating against. Station 350726 (WANDERERS PEAK):

ID,ObsDate,StationID,ERC
0,1/1/2005,350726,5.4
1,1/2/2005,350726,10.5
2,1/3/2005,350726,12
3,1/4/2005,350726,12.6
4,1/5/2005,350726,9.7
5,1/6/2005,350726,11.8
6,1/7/2005,350726,11.4
7,1/8/2005,350726,6.2
8,1/9/2005,350726,5.8
9,1/10/2005,350726,5.5

And the code...

from scipy import stats
import pandas as pd
import os
import sqlite3

def calc_percentile_data(ws, station, query_value, element_requested):

    # Import the datafile into a pandas dataframe
    db_file = os.path.abspath(os.path.join(ws, '..\All_Key_Station_ERC_F1000.db')) ###Modified to set up for COOP remote laptops
    dbConnection = sqlite3.connect(db_file)
    
    # Assume table is named as below
    input_table = (f"{station}_FMY")
    
    # Check that the station table exists in the database
    # Otherwise, return a missing percentile (-999)
    table_exists = (f"SELECT name FROM sqlite_master WHERE type='table' AND name='{input_table}';")

    if dbConnection.execute(table_exists).fetchone():
        pass
    else:
        pctile = -999
        return(pctile)

    # Check that the requested element exists within the table. If so, read in the data and continue
    # Otherwise, return a missing percentile (-999)
    column_exists = (f"SELECT COUNT(*) AS CNTREC FROM pragma_table_info('{input_table}') WHERE name='{element_requested}'")
    if dbConnection.execute(column_exists).fetchone():
    
        # element_requested matches an available column; Read the data into a dataframe
        input_column = element_requested
    
        query = f"SELECT [{input_column}] FROM [{input_table}] WHERE [StationID]='{station}'"
        dataf = pd.read_sql(query, dbConnection)
        dbConnection.close()
    else:
        print(f"ERROR: {element_requested} not available in the table {input_table} of {db_file}")
        pctile = -999
        dbConnection.close()
        return(pctile)
    
    print(f"   Station {station} is Key RAWS: Calculating {element_requested} Percentile data")
    pctile=find_percentile_rank(query_value, dataf)

    return(pctile)

def find_percentile_rank(curr_value, array):
    # Find percentile rank of a new value in the current array
    percentile = stats.percentileofscore(array, curr_value)
    return(percentile)

After updating SciPy and noticing the problem, I tried casting the query value as various number types but it didn't seem to make a difference. I then tried reverting to various versions to determine where the change occurred. This revealed something switched with version 1.9.0. I read the release notes but nothing has jumped out at me. I didn't notice anything specific relating to stats.percentileofscore but my eyes also became somewhat fuzzy at that point.

I am at a loss as how to correct this problem. Hopefully you more skilled folks can point me in the right direction. I much prefer to have our environment and code brought up to modern versions.

Edited the sample database above to match the requested output from the comments below. I am using station 350726 (Wanders Peak) for each of the examples, though this issue spans all stations/input tables.

I also added the variable's type to show how it's being passed into stats.percentileofscore plus the output type and data

With SciPy version 1.8.1:

   Station 350726 is Key RAWS: Calculating ERC Percentile data
array type=<class 'pandas.core.frame.DataFrame'>
  array=       ERC
0      5.4
1     10.5
2     12.0
3     12.6
4      9.7
...    ...
6930   5.0
6931   4.9
6932   4.6
6933   4.2
6934   3.9

[6935 rows x 1 columns],

curr_value type=<class 'float'>
  curr_value=41.0
percentile type=<class 'float'>
 percentile=93.85003604902667

And with SciPy version 1.11.4. Had to use this version due to some other issues later in the workflow needing to be fixed when using the latest version of SciPy:

** Station 350726 is Key RAWS: Calculating ERC Percentile data
array type=<class 'pandas.core.frame.DataFrame'>
  array=       ERC
0      5.4
1     10.5
2     12.0
3     12.6
4      9.7
...    ...
6930   5.0
6931   4.9
6932   4.6
6933   4.2
6934   3.9

[6935 rows x 1 columns],

curr_value type=<class 'float'>
  curr_value=41.0
percentile type=<class 'numpy.ndarray'>
 percentile=[0.01441961 0.01441961 0.01441961 ... 0.01441961 0.01441961 0.01441961]

For kicks, I tried assigning my scalar to an array via curr_value_array=[curr_value] with no change.

   Station 350726 is Key RAWS: Calculating ERC Percentile data
array type=<class 'pandas.core.frame.DataFrame'>
  array=       ERC
0      5.4
1     10.5
2     12.0
3     12.6
4      9.7
...    ...
6930   5.0
6931   4.9
6932   4.6
6933   4.2
6934   3.9

[6935 rows x 1 columns],

curr_value type=<class 'float'>
  curr_value=41.0
curr_value_array type=<class 'list'>
  curr_value_array=[41.0]
percentile type=<class 'numpy.ndarray'>
 percentile=[0.01441961 0.01441961 0.01441961 ... 0.01441961 0.01441961 0.01441961]
Solution 
  • array type=<class 'pandas.core.frame.DataFrame'>

    This isn't an array. It's a DataFrame. There are two important things here:

    • This is a Pandas DataFrame, not a NumPy array. This normally doesn't matter, as many functions will automatically convert a DataFrame into an array.
    • However, a DataFrame is 2D. The percentileofscore function is only designed to operate on 1D arrays. (See documentation.) It seems to have strange behavior in post 1.9.0 if you pass it a 2D array or DataFrame.

    I think what you intended here was to pass the column 'ERC' as a 1D array to the percentile function. You can access a specific column (or Series) by [] operator. You can convert that column into a 1D array with .values.

    Here's an example program to demonstrate this.

    import pandas as pd
import numpy as np
import scipy.stats

df = pd.DataFrame({'ERC': np.random.uniform(low=0, high=1, size=(10,))})
print(df)
print(scipy.stats.percentileofscore(df, 2))
print(scipy.stats.percentileofscore(df['ERC'].values, 2))

    Here's what happens in 1.8.1:

            ERC
0  0.532428
1  0.674558
2  0.529101
3  0.912309
4  0.477466
5  0.450533
6  0.310699
7  0.348802
8  0.182325
9  0.487267
100.0
100.0

    Here's what happens in 1.9.0:

            ERC
0  0.863470
1  0.776420
2  0.845065
3  0.732675
4  0.258756
5  0.846346
6  0.350822
7  0.119473
8  0.218777
9  0.857994
[10. 10. 10. 10. 10. 10. 10. 10. 10. 10.]
100.0

    This shows that passing a 2D DataFrame is the cause of the problem. If you used array['ERC'].values instead of array, you would have more robust code that works in both versions.