pandas.core.indexes.period.PeriodIndex error when returning pandas df to excel using Run_Python

[deltahedgetech]

OS (e.g. Windows 10 or macOS Sierra)

mac

Versions of xlwings, Excel and Python (e.g. 0.11.8, Office 365, Python 3.7)

0.15.5

Describe your issue (incl. Traceback!)

Trying to return two data frames to excel
Looking at the code below, summ_df to B2 works and displays in excel.

It is the final_df in B12 that causes xlwings to error.
This code works when running in standalone mode i.e. outside of xlwings and returns both dataframes

# Your traceback here
Traceback (most recent call last):

  File "/Users/stuartclark/anaconda3/lib/python3.6/site-packages/aeosa/aem/aemcodecs.py", line 322, in pack

    return self.encoders[data.__class__](data) # quick lookup by type/class

KeyError: <class 'pandas.core.indexes.period.PeriodIndex'>
During handling of the above exception, another exception occurred:
Traceback (most recent call last):

  File "<string>", line 1, in <module>

  File "/Users/stuartclark/Documents/PortfolioManagement/Libraries/manu_pmi.py", line 99, in main

    sht.range('B12').value = final_df.index

  File "/Users/stuartclark/anaconda3/lib/python3.6/site-packages/xlwings/main.py", line 1525, in value

    conversion.write(data, self, self._options)

  File "/Users/stuartclark/anaconda3/lib/python3.6/site-packages/xlwings/conversion/__init__.py", line 43, in write

    pipeline(ctx)

  File "/Users/stuartclark/anaconda3/lib/python3.6/site-packages/xlwings/conversion/framework.py", line 66, in __call__

    stage(*args, **kwargs)

  File "/Us��Êèˇ�

Include a minimal code sample to reproduce the issue (and attach a sample workbook if required!)

# Your code here

    df = df1.fillna(method='ffill')    
    AVG_12M = pd.DataFrame(df.loc[M12:today].mean(),columns=['12M Avg']).round(2)
    AVG_3M=pd.DataFrame(df.loc[M3:today].mean(),columns=['3M Avg']).round(2)
    MIN_12M=pd.DataFrame(df.loc[M12:today].min(),columns=['Min 12M']).round(2)
    MAX_12M = pd.DataFrame(df.loc[M12:today].max(),columns=['Min 12M']).round(2)
    MM=np.subtract(df[-1:],df[-2:-1]).T.round(2)
    MM.columns = ['M/M']
    LAST_AVG12M = np.subtract(df[-1:].T, AVG_12M).round(2)
    LAST_AVG12M.columns = ['Latest-12M Avg']
    
    summ_df=pd.concat([AVG_12M,AVG_3M,MIN_12M,MAX_12M,LAST_AVG12M,MM],axis=1).T
    #display(summ_df)
    #display(df1.iloc[::-1].head(15))
    
    sht.range('B2').value = summ_df
    
    final_df = df1.iloc[::-1].head(15).dropna()
    
    sht.range('B12').value = final_df

[fzumstein]

Can you please change the code so that it is self-contained and I can try to reproduce? thanks

[deltahedgetech]

I have amended the code so that it doesn't throw the error by
a) adding this line in the read_db function "df.index = pd.DatetimeIndex(df.index)"
b) removing this line in the same function #df.index = df.index.to_period('M'). This is a bit annoying as I want to display only month and year as dataframe index, ignoring the day.

us_manu_pmi.xlsx

from future import division
import pandas as pd
import numpy as np
import xlwings as xw
import datetime
from dateutil.relativedelta import relativedelta
#from arctic import Arctic

"""
Created on Mon Apr 22 15:30:45 2019

@author: stuartclark
"""

manu_pmi_dict = {
'U.S':'manufacturing-pmi-829',
'China':'chinese-manufacturing-pmi-594',
'Japan':'manufacturing-pmi-202',
'EZ':'manufacturing-pmi-201',
'Germany':'german-manufacturing-pmi-136',
'U.K': 'manufacturing-pmi-204',
'France': 'french-manufacturing-pmi-340',
'Spain':'spanish-manufacturing-pmi-667',
'Italy':'italian-manufacturing-pmi-832',
'Turkey':'turkish-manufacturing-pmi-1305',
'Switzerland':'svme-pmi-278',
'Austria':'austrian-manufacturing-pmi-1544',
'South Africa':'south-african-manufacturing-pmi-605',
'Brazil':'brazilian-markit-manufacturing-pmi-1476',
'Norway':'norwegian-pmi-manufacturing-642',
'Greece':'greek-manufacturing-pmi-1472',
'Mexico':'mexican-manufacturing-pmi-1303',
'India':'indian-nikkei-markit-manufacturing-pmi-754',
'Ireland':'irish-manufacturing-pmi-1481',
'Sweden':'swedish-manufacturing-pmi-798',
'Hong Kong':'hong-kong-manufacturing-pmi-1088',
'Canada':'rbc-manufacturing-pmi-1029',
'Australia':'manufacturing-pmi-1838',
'New Zealand':'business-nz-pmi-338',
'South Korea':'south-korean-nikkei-manufacturing-pmi-785',
}

#store = Arctic("localhost")
#library = store['Economic_Data']

def main():

sht = xw.Book.caller().sheets[0]
sht.range('B2').expand().clear_contents()
# User Inputs
#report = sht.range('E4').value
#from_date = sht.range('E5').value
#to_date = sht.range('E6').value


df1 = pd.DataFrame()

today = datetime.datetime.now()#.strftime("%Y-%m-%d")
M3  = (today - relativedelta(months=3)).strftime("%Y-%m-%d")
M12 = (today - relativedelta(months=12)).strftime("%Y-%m-%d")
today = today.strftime("%Y-%m-%d")
print (today,M3,M12)

for ctry, key in manu_pmi_dict.items():
    try:
        temp_df=read_db(ctry,key)
        df1 = pd.concat([df1,temp_df],axis=1)
        #break after one loop for debugging
        break
    except Exception as e:
        #print ('Did not work for ', ctry, e)
        continue
    
df = df1.fillna(method='ffill')    
AVG_12M = pd.DataFrame(df.loc[M12:today].mean(),columns=['12M Avg']).round(2)
AVG_3M=pd.DataFrame(df.loc[M3:today].mean(),columns=['3M Avg']).round(2)
MIN_12M=pd.DataFrame(df.loc[M12:today].min(),columns=['Min 12M']).round(2)
MAX_12M = pd.DataFrame(df.loc[M12:today].max(),columns=['Min 12M']).round(2)
MM=np.subtract(df[-1:],df[-2:-1]).T.round(2)
MM.columns = ['M/M']
LAST_AVG12M = np.subtract(df[-1:].T, AVG_12M).round(2)
LAST_AVG12M.columns = ['Latest-12M Avg']

summ_df=pd.concat([AVG_12M,AVG_3M,MIN_12M,MAX_12M,LAST_AVG12M,MM],axis=1).T
#display(summ_df)
#display(df1.iloc[::-1].head(15))

sht.range('B2').value = summ_df

final_df = df1.iloc[::-1].head(15)


sht.range('B12').value = final_df

#sht.range((1,i)).expand().clear_contents()
#if item.metadata['unit'] == 'Pct':
#    sht.range('B2:B1000').number_format = '0.00%'
#    sht.range((1,i)).value = df[df.index < today] [name]#.apply('{:.2%}'.format)
    
#else:
#    sht.range('B2:B1000').number_format = '0.00'
#    sht.range((1,i)).value = df[df.index < today] [name]
#    
#sht.range('A1').value = item.metadata['unit']
#sht.range('A1').api.Font.Bold = True

def read_db(ctry,key):

today = datetime.datetime.now().strftime("%Y-%m-%d")
df = library.read(key).data
#df.index = pd.DatetimeIndex(df.index)
df=pd.read_csv('/Users/stuartclark/Documents/PortfolioManagement/Data/us_manu_pmi.csv',index_col=0)
df['Revised'] = df['Previous'].shift(1)
df.loc[df.index == df.index.max(), 'Revised'] = df[df.index == df.index.max()]['Actual']
df = df[df.index < today]

df = df.groupby(pd.Grouper(freq="M")).mean()['Revised'].round(2)
#df.index = df.index.to_period('M')
df=pd.DataFrame(df)
df.columns = [ctry]

#df[df.index < today].plot(grid=True,figsize=(12,6))
return df

[deltahedgetech]

I didn't mean to close it.
Also, need to change it to pd.read_excel('your directory/us_manu_pmi.xlsx','index_col=0)

could upload a csv file for some reason

[deltahedgetech]

also df.index = pd.DatetimeIndex(df.index) needs to be uncommented for it to run