How to get the closest previous value for each row?

user_12659227 0
Viewed 74

I have a DataFrame which has many stocks (in this case just GM and F), Sales growth, stock growth.

import pandas as pd
import numpy as np

d = {'year': [2000, 2000, 2001, 2001, 2002, 2002, 2003, 2003, 2004, 2004],  'Stock': ["GM", "F", "GM", "F", "GM", "F", "GM", "F", "GM", "F"], 'US Sales Growth': [.3, .3, .1, .1, .6, .6, .12, .12, .7, .7], 'Stock Growth': [.1, .2, .3, .4, .14, .16,  .2, .1,.15,.16]}

df = pd.DataFrame(data=d)

enter image description here

My goal is to add a column that is called "closets_sales_growth_corresponding_stock_growth" that matches the current sales growth to past sales growths and collects the stock growth corresponding to the closest past sales growth to a new column.

It should look like this:

enter image description here

pandas match dataframe numpy python
3 Answers

I would create a function that returns the stock growth for each row. This can then be applied to each row:

import pandas as pd
import numpy as np

# Create dataframe
d = {'year': [2000, 2000, 2001, 2001, 2002, 2002, 2003, 2003, 2004, 2004],  'Stock': ["GM", "F", "GM", "F", "GM", "F", "GM", "F", "GM", "F"], 'US Sales Growth': [.3, .3, .1, .1, .6, .6, .12, .12, .7, .7], 'Stock Growth': [.1, .2, .3, .4, .14, .16,  .2, .1,.15,.16]}
df = pd.DataFrame(data=d)


# Define function to find nearest value
def find_nearest_value(df, year, stock, sales_growth):
    # Filter df to only include rows of same stock and earlier year
    filtered_df = df[(df['year'] < year) & (df['Stock'] == stock)]
    
    # Order the filtered row by how close they are to current sales growth
    ordered = (filtered_df['US Sales Growth']-sales_growth).abs().argsort()
    
    # Return nan if we do not find any previous value
    if len(ordered) == 0:
        return np.nan
    
    
    stock_growth = filtered_df['Stock Growth'].iloc[(ordered[:1])].values[0]
    return stock_growth

# Apply function on each row
df['closets_sales_growth_corresponding_stock_growth'] = df.apply(lambda x: find_nearest_value(df, x['year'], x['Stock'], x['US Sales Growth']), axis=1)

df

Output:

year    Stock   US Sales Growth Stock Growth    closets_sales_growth_corresponding_stock_growth
0   2000    GM  0.30    0.10    NaN
1   2000    F   0.30    0.20    NaN
2   2001    GM  0.10    0.30    0.10
3   2001    F   0.10    0.40    0.20
4   2002    GM  0.60    0.14    0.10
5   2002    F   0.60    0.16    0.20
6   2003    GM  0.12    0.20    0.30
7   2003    F   0.12    0.10    0.40
8   2004    GM  0.70    0.15    0.14
9   2004    F   0.70    0.16    0.16
user_79479940

Not pretty, but it works :)

import pandas as pd
import numpy as np

d = {'year': [2000, 2000, 2001, 2001, 2002, 2002, 2003, 2003, 2004, 2004],  'Stock': ["GM", "F", "GM", "F", "GM", "F", "GM", "F", "GM", "F"], 'US Sales Growth': [.3, .3, .1, .1, .6, .6, .12, .12, .7, .7], 'Stock Growth': [.1, .2, .3, .4, .14, .16,  .2, .1,.15,.16]}

df = pd.DataFrame(data=d)

close_s_g_s = []

for i in df.index:
    stock = df['Stock'][i]
    cur_s_g = df['US Sales Growth'][i]

    stock_growth = np.nan
    min_s_g_dif = np.inf 
    for i_less in range(0,i):

        if df['Stock'][i_less] == stock:
            s_g_dif = abs(df['US Sales Growth'][i_less] -  cur_s_g)
            if s_g_dif < min_s_g_dif:
                min_s_g_dif = s_g_dif
                stock_growth = df["Stock Growth"][i_less]

    close_s_g_s.append(stock_growth)

new_col = "closets_sales_growth_corresponding_stock_growth"
df[new_col] = close_s_g_s
user_169266110

Here's one way using groupby. Basically, just groupby "Stock" and apply to each group a function that finds the stock growth of the closest past US sales growth of each row.

def get_new_col(g):
    out = [np.nan]
    for idx in g.index[1:]:
        # get the index of the previous sales growth closest in absolute value to the current one
        closest_val_idx = (g.loc[idx, 'US Sales Growth'] - g.loc[:idx-1, 'US Sales Growth']).abs().idxmin()
        # index the stock growth with the index found above
        out.append(g.loc[closest_val_idx, 'Stock Growth'])
    return pd.Series(out, index=g.index)

df['growth_corresponding_stock_growth'] = df.groupby('Stock').apply(get_new_col).droplevel(0)

Output:

   year Stock  US Sales Growth  Stock Growth  growth_corresponding_stock_growth
0  2000    GM             0.30          0.10                             NaN
1  2000     F             0.30          0.20                             NaN
2  2001    GM             0.10          0.30                            0.10
3  2001     F             0.10          0.40                            0.20
4  2002    GM             0.60          0.14                            0.10
5  2002     F             0.60          0.16                            0.20
6  2003    GM             0.12          0.20                            0.30
7  2003     F             0.12          0.10                            0.40
8  2004    GM             0.70          0.15                            0.14
9  2004     F             0.70          0.16                            0.16
Admin1
Related