CODEWITHSUNDEEP
pythonexcel
mbvisti
mbvisti

Reputation: 17

Python: outputting lists to excel

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()

Upvotes: 0

Views: 121

Answers (1)

Aklys
Aklys

Reputation: 481

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')

Upvotes: 1

Related Questions