Iterating over unique column values to create dataframes, running functions on the sub dataframes and saving as a single concat

user_11582077 0
Viewed 79

I have a master dataframe that has hundreds of key values, it's in the below format:

Key,Val1,Val2
a,1,w
a,2,w
a,3,e
b,1,g
b,2,d

The idea is to create a loop that creates a new dataframe for every unique key value:

Key,Val1,Val2
a,1,w
a,2,w
a,3,e

and perform a function over this dataframe and then create a new dataframe that is a concat of all the new dataframes obtained from unique key values. So if the functions were avg and median over val 2, the output would look like:

     a     b
avg  1      1
med  2      2

The code I've come up with is, the function dataframe is saved in dffinal:

def regression():
    X=Final.iloc[:,7:-1].copy()
    y=Final[['Sale Rate']].copy()
    X_train, X_test, y_train, y_test = train_test_split(X,y,test_size=.2, random_state=0)
    sel=f_classif(X_train, y_train)
    p_values=pd.Series(sel[0], index=X_train.columns)
    p_values=p_values.reset_index()
    pd.options.display.float_format = "{:,.2f}".format
    return p_values

Final.groupby('Key').agg([regression]).T

Please point out where exactly I might be going wrong, I have no idea how to go about this.

error:

ValueError: no results

Data structure eg : Function to run ANOVA and give F stat values as the output

pandas for-loop python
1 Answers

If I understand correctly, use

df.groupby('Key')['Val1'].agg(['mean', 'median']).T

and rename index/cols as you wish.

Demo:

>>> df
  Key  Val1 Val2
0   a     1    w
1   a     2    w
2   a     3    e
3   b     1    g
4   b     2    d
>>> df.groupby('Key')['Val1'].agg(['mean', 'median']).T
Key       a    b
mean    2.0  1.5
median  2.0  1.5

Aggregating with custom functions.

def custom1(values):
    return values.sum()*100

def custom2(values):
    return values.max() - values.min()

Demo:

>>> df.groupby('Key')['Val1'].agg([custom1, custom2]).T
Key        a    b
custom1  600  300
custom2    2    1

Custom names:

>>> df.groupby('Key')['Val1'].agg([('foo', custom1), ('bar', custom2)]).T
Key    a    b
foo  600  300
bar    2    1
user_36200030
Related