Python: outputting lists to excel

Question

For my master thesis, I need to calculate expected returns for x number of stocks on a given event date. I have written the following code, which does what I intends (match Fama & French factors with a sample of event dates). However, when I try to export it to excel I can't seem to get the correct output. I.e. it doesn't contain column headings such as Dates, names of fama & french factors and the corresponding rows.

Does anybody have a workaround for this? Any improvements are gladly appreciated. Here are my code:

import pandas as pd

# Data import
ff_five = pd.read_excel('C:/Users/MBV/Desktop/cmon.xlsx', 
infer_datetime_format=True)
df = pd.read_csv('C:/Users/MBV/Desktop/4.csv', parse_dates=True, 
infer_datetime_format=True)

# Converting dates to datetime
df['Date'] = pd.to_datetime(df['Date'], infer_datetime_format=True)

# Creating an empty placeholder
end_date = []

# Iterating over the event dates, creating a start and end date 60 months 
apart
for index, row in df.iterrows():
    end_da = row['Date']-pd.DateOffset(months=60)
    end_date.append(end_da)

end_date_df = pd.DataFrame(data=end_date)

m = pd.merge(end_date_df,df,left_index=True,right_index=True)

m.columns = ['Start','End']

ff_factors = []

for index, row in m.iterrows():
    ff_five['Date'] = pd.to_datetime(ff_five['Date'])  
    time_range= (ff_five['Date'] > row['Start']) & (ff_five['Date'] <= 
    row['End'])
    df = ff_five.loc[time_range]
    ff_factors.append(df)

EDIT: Here are my attempt at getting the data from python to excel.

ff_factors_df = pd.DataFrame(data=ff_factors)

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('estimation_data.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
ff_factors_df.to_csv(writer, sheet_name='Sheet1')

# Close the Pandas Excel writer and output the Excel file.
writer.save()

I think you can avoid the merging entirely, have a look at df.apply() which applies a function to every row / or column. You could use that, to avoid the df.iterrows() business. — Christian Sauer
– Christian Sauer, Commented Apr 16, 2017 at 21:10

Aklys · Accepted Answer · 2017-04-16 22:09:40Z

1

To output a dataframe to csv or excel should be able to be done with

ff_five.to_excel('Filename.xls')

Change excel to csv if you want it to a csv.

Ok I tried to interpret what you were trying to do without it being very clear. But if I was interpreting it correctly you are trying to create some addition columns based on other data. Instead of creating separate lists you could possibly just put them in as new columns and then just output the columns you want potentially. Something like this maybe (had to make some assumptions and create some fake data to see if this is on the right track):

import pandas as pd

ff_five = pd.DataFrame()

ff_five['Date'] = ["2012-11-01", "2012-11-30"]

df = pd.DataFrame()

df['Date'] = ["2012-12-01", "2012-12-30"]

df['Date'] = pd.to_datetime(df['Date'])

df['End'] = df['Date'] - pd.DateOffset(months=60)

df.columns = ['Start', 'End']

ff_five['Date'] = pd.to_datetime(ff_five['Date'])

df['ff_factor'] = (ff_five['Date'] > df['Start']) & (ff_five['Date'] <= df['End'])

df.to_excel('estimation_data.xlsx', sheet_name='Sheet1')

edited Apr 16, 2017 at 22:09

answered Apr 16, 2017 at 21:13

Aklys

4811 gold badge5 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mbvisti Over a year ago

I used that, however, the output doesn't come out as it does before I change ff_factors to a dataframe

Christian Sauer Over a year ago

I unable to understand your code fully, can you provide some sample data? As I said in my first comment, I think you can avoid your whole iteration bsuiness enterily, using apply(). Merging has the potential to cause headaches with column names, so I would recommend that,.

mbvisti Over a year ago

Sure! I was busy looking at the apply(). The last iteration produces the following: Date Mkt-RF SMB HML RMW CMA RF 236 2010-03-01 6.43 -0.72 4.15 0.31 -0.29 0.01 237 2010-04-01 -1.92 3.35 -0.28 1.55 -1.30 0.01 238 2010-05-01 -12.05 -0.67 -3.65 0.34 -0.80 0.01 239 2010-06-01 -0.98 -0.17 -2.21 -1.17 -0.96 0.01 240 2010-07-01 11.85 -1.65 4.90 -2.84 1.00 0.01 ...] When i convert ff_factors to a dataframe, the above data turns out like this: 1629 Date Mkt-RF SMB HML RMW CMA...

Aklys Over a year ago

Well it looks like you are appending a DataFrame object to the list ff_factors so I'd expect this to not output as expected if that's what you are trying to output that to a file. I'd recommend you display the dataframe to see what you are trying to export to file first if you haven't already. As it's unclear what you are writing to get the export to work correctly as it's doesn't seem to be shown in your code above.

mbvisti Over a year ago

I added the code I've used to output the data to excel.

Collectives™ on Stack Overflow

Python: outputting lists to excel

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related