Save iteration in dataframe-CodePudding

I have two dataframes:

import numpy as np
import pandas as pd
from sklearn.metrics import r2_score    

df = pd.DataFrame([{'A': -4, 'B': -3, 'C': -2, 'D': -1, 'E': 2, 'F': 4, 'G': 8, 'H': 6, 'I': -2}])

df2 looks like this (just a cutout; in total there are ~100 rows).

df2 = pd.DataFrame({'Date': [220412004, 220412004, 220412004, 220412006], 'A': [-0.15584, -0.11446, -0.1349, -0.0458], 'B': [-0.11826, -0.0833, -0.1025, -0.0216], 'C': [-0.0611, -0.0413, -0.0645, -0.0049], 'D': [-0.04461, -0.022693, -0.0410, 0.0051], 'E': [0.0927, 0.0705, 0.0923, 0.0512], 'F': [0.1453, 11117, 0.1325, 0.06205], 'G': [0.30077, 0.2274, 0.2688, 0.1077], 'H': [0.2449, 0.1860, 0.2274, 0.09328], 'I': [-0.0706, -0.0612, -0.0704, -0.02953]})

    Date          A           B            C          D          E         F         G           H       I
3   220412004   -0.15584    -0.11826    -0.0611    -0.04461    0.0927   0.1453    0.30077    0.2449   -0.0706
4   220412004   -0.11446    -0.0833     -0.0413    -0.022693   0.0705   0.11117   0.2274     0.1860   -0.0612
5   220412004   -0.1349     -0.1025     -0.0645    -0.0410     0.0923   0.1325    0.2688     0.2274   -0.0704
7   220412006   -0.0458     -0.0216     -0.0049     0.0051     0.0512   0.06205   0.1077     0.09328  -0.02953

Then I set:

df2 = df2.set_index('Date')
df2 = df2.astype(float)

Now I iterate through all rows of df2 and make a linear regression:

for index, row in df2.iterrows():
reg = np.polyfit(df.values[0], row.values, 1)
predict = np.poly1d(reg) # Slope and intercept
trend = np.polyval(reg, df)
std = row.std() # Standard deviation
r2 = np.round(r2_score(row.values, predict(df.T)), 5) #R-squared

so far so good.

Now I would like to store the results into df2. This is my approach:

df3 = pd.DataFrame([predict])
df4 = pd.DataFrame([r2])
df5 = pd.concat([df3, df4], axis = 1)
df5.columns = ['Slope', 'Intercept', 'r2']
result = pd.concat([df2, df5], axis = 1)

However, this will just add a single new row to "result". But I want the result to be stored to the corresponding index. Any ideas?

Edit: to make it clearer, this is how I want result to look like:

result = pd.DataFrame({'Date': [220412004, 220412004, 220412004, 220412006], 'A': [-0.15584, -0.11446, -0.1349, -0.0458], 'B': [-0.11826, -0.0833, -0.1025, -0.0216], 'C': [-0.0611, -0.0413, -0.0645, -0.0049], 'D': [-0.04461, -0.022693, -0.0410, 0.0051], 'E': [0.0927, 0.0705, 0.0923, 0.0512], 'F': [0.1453, 11117, 0.1325, 0.06205], 'G': [0.30077, 0.2274, 0.2688, 0.1077], 'H': [0.2449, 0.1860, 0.2274, 0.09328], 'I': [-0.0706, -0.0612, -0.0704, -0.02953], 'Slope': [0.03834244, 235.48473307, 0.03481399, 0.01286896], 'Intercept': [0.00294672, 1025.92034249, 0.00324312, 0.01272759], 'r2': [0.99615000, 0.07415000, 0.99447000, 0.97297000]})

CodePudding user response：

You can store the result in every loop

for index, row in df2.iterrows():
    reg = np.polyfit(df.values[0], row.values, 1)
    predict = np.poly1d(reg) # Slope and intercept
    trend = np.polyval(reg, df)
    std = row.std() # Standard deviation
    r2 = np.round(r2_score(row.values, predict(df.T)), 5) #R-squared

    df2.loc[[index], 'Slope and intercept'] = pd.Series([predict], index=[index])
    df2.loc[index, 'r2'] = r2

print(df2)

                 A        B       C         D       E            F        G        H        I     Slope and intercept       r2
Date
220412004 -0.15584 -0.11826 -0.0611 -0.044610  0.0927      0.14530  0.30077  0.24490 -0.07060   [0.03481399394856277]  0.99447
220412004 -0.11446 -0.08330 -0.0413 -0.022693  0.0705  11117.00000  0.22740  0.18600 -0.06120   [0.03481399394856277]  0.99447
220412004 -0.13490 -0.10250 -0.0645 -0.041000  0.0923      0.13250  0.26880  0.22740 -0.07040   [0.03481399394856277]  0.99447
220412006 -0.04580 -0.02160 -0.0049  0.005100  0.0512      0.06205  0.10770  0.09328 -0.02953  [0.012868956127080184]  0.97297

You can also try apply instead of iterating

def cal(row):
    reg = np.polyfit(df.values[0], row.values, 1)
    predict = np.poly1d(reg) # Slope and intercept
    trend = np.polyval(reg, df)
    std = row.std() # Standard deviation
    r2 = np.round(r2_score(row.values, predict(df.T)), 5) #R-squared
    #return pd.Series([predict, r2])
    return predict, r2

df2[['Slope and intercept', 'r2']] = df2.apply(cal, axis=1, result_type='expand')

CodePudding user response：

This is my solution:

import numpy as np
import pandas as pd
from sklearn.metrics import r2_score    

df = pd.DataFrame([{'A': -4, 'B': -3, 'C': -2, 'D': -1, 'E': 2, 'F': 4, 'G': 8, 'H': 6, 'I': -2}])

df2 = pd.DataFrame({'Date': [220412004, 220412004, 220412004, 220412006], 'A': [-0.15584, -0.11446, -0.1349, -0.0458], 'B': [-0.11826, -0.0833, -0.1025, -0.0216], 'C': [-0.0611, -0.0413, -0.0645, -0.0049], 'D': [-0.04461, -0.022693, -0.0410, 0.0051], 'E': [0.0927, 0.0705, 0.0923, 0.0512], 'F': [0.1453, 11117, 0.1325, 0.06205], 'G': [0.30077, 0.2274, 0.2688, 0.1077], 'H': [0.2449, 0.1860, 0.2274, 0.09328], 'I': [-0.0706, -0.0612, -0.0704, -0.02953]})

df2 = df2.set_index('Date')
df2 = df2.astype(float)


def cal(row):
    reg = np.polyfit(df.values[0], row.values, 1)
    predict = np.poly1d(reg) # Slope and intercept
    trend = np.polyval(reg, df)
    std = row.std() # Standard deviation
    r2 = np.round(r2_score(row.values, predict(df.T)), 5) #R-squared
    df3 = pd.DataFrame([predict])
    df3.columns = ['Slope', 'Intercept']
    Slope = df3['Slope'].to_string(dtype=False)
    Slope = Slope[3:]
    Intercept = df3['Intercept'].to_string(dtype=False)
    Intercept = Intercept[3:]
    print(Intercept)
    return Slope, Intercept, r2



df2[['Slope', 'intercept', 'r2']] = df2.apply(cal, axis=1, result_type='expand')
df2[['Slope', 'intercept']] = df2[['Slope', 'intercept']].astype(float)

Thanks to all that helped me here :)