0

Thanks for reading this. I am doing a regression for a group of stock and index and will be doing more than one group of stock and index. At the same time, the data of regression will be save into pd.dictionary by loop function -> results_df = {}

Now, I would like to output those all results_df to an excel whatever xlsx/csv file format. Since my dictionary dimensional is more that 1-D. it cannot be output to an Excel.

I've tried a lot method which is rasing a warrning as "raise ValueError(f"Must pass 2-d input. shape={values.shape}")".

Appreciate.

And the results seen as below: results_df

Out[1]: 
{'00001':           coeff     pvals     rsqur
 const  0.029415  0.017253  0.005603
 hsi_r  0.036091  0.171680  0.005603,
 '00002':           coeff         pvals    rsqur
 const  0.200074  6.853545e-89  0.41015
 hsi_r -0.234550  4.576146e-40  0.41015,
 '00003':           coeff         pvals     rsqur
 const -0.072899  1.268744e-03  0.139553
 hsi_r  0.353300  1.555612e-12  0.139553,
 '00004':           coeff         pvals     rsqur
 const  0.546624  1.299876e-44  0.160123
 hsi_r -0.570297  2.591136e-14  0.160123,
 '00005':           coeff         pvals     rsqur
 const  0.497876  1.810225e-73  0.432056
 hsi_r -0.716644  8.181176e-43  0.432056}

mypath = 'temp_excel/1D/'
files = [f.split('.')[0] for f in listdir(mypath) if isfile(join(mypath, f))]
dataframe={}
for file in files:
     dataframe[file] = pd.read_csv(mypath+file+'.csv',index_col=0, parse_dates=True)

df={}
for i in files:
    df[i] = pd.DataFrame.from_dict(dataframe[i])
    df[i]['r_' + i] = df[i]['close'].pct_change().cumsum()
    df[i] = pd.merge(df[i]['r_' + i], hsi, how='left', left_index=True, right_index=True)
    df[i] = df[i].dropna()

results = {}
for i in df:
    X = sm.add_constant(df[i]['hsi_r'])
    y = df[i]['r_' + i]
    model = sm.OLS(y, X)
    results[i] = model.fit()
    # print(results.summary())

result_list = ['00001', '00002','00003','00004','00005']
results_df={}
for i in result_list:
    pvals = results[i].pvalues
    coeff = results[i].params
    rsqur = results[i].rsquared
    results_df[i] = pd.DataFrame({"coeff":coeff,"pvals":pvals,"rsqur":rsqur})
Steven
  • 1
  • 1
  • This answer may help - https://stackoverflow.com/a/70894995/2570277 or this one which uses `csv.DictWriter` - https://stackoverflow.com/a/29400800/2570277 – Nick Jun 15 '22 at 10:42
  • @Nick Thank you Nick. I watched the post that you mentioned. Honestly, I still cannot use where answer of the posted to solve it this moment. But, you gave those content was very helpful. – Steven Jun 15 '22 at 15:49

0 Answers0