ValueError: closed only implemented for datetimelike and offset based windows

[Anonymous]

Hi,

I am trying to create a Python visual with pandas and seaborn. the visual works perfect in Powerbi Desktop but when published to service gives the following error:

[S-e52e3692-02ab-4112-808f-bdb2e6bc303a][S-e52e3692-02ab-4112-808f-bdb2e6bc303a]ValueError: closed only implemented for datetimelike and offset based windows

Please let me know if any more data is needed as of now or not.

[Anonymous]

That solved my problem!! Thank you so much for your support, I wasted so much time trying to solve this problem I want to give you 10x thumbs up!! 🙂 🙂

[Anonymous]

The code: 

# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script:

# dataset = pandas.DataFrame(PO Creation Date, Commitment Value EUR)

# dataset = dataset.drop_duplicates()

# Paste or type your script code here:

import matplotlib.pyplot as plt

import seaborn as sns

import pandas as pd

import numpy as np

ltforecast = []

dfmindex = pandas.DataFrame ()

dfyindex = pandas.DataFrame ()

dffc = pandas.DataFrame ()

df = pandas.DataFrame({'dates': dataset["PO Creation Date"], 'spend': dataset["Commitment Value EUR"]})

df['dates'] = df['dates'].str[:10]

df['year'] = pd.DatetimeIndex(df['dates']).year

df['month'] = pd.DatetimeIndex(df['dates']).month

dfmindex['month'] = df.groupby(['month'])['spend'].mean().reset_index()['month']

dfmindex['mindex'] = df.groupby(['month'])['spend'].mean().reset_index()['spend']

dfmindex['std'] = df.groupby(['month'])['spend'].std().reset_index()['spend']

average = df['spend'].mean()

dfmindex['mindex'] = dfmindex['mindex']/average

dfyindex['year'] = df.groupby(['year'])['spend'].mean().reset_index()['year']

dfyindex['yindex'] = df.groupby(['year'])['spend'].mean().reset_index()['spend']

dfyindex['yindex'] = dfyindex['yindex']/average

slope_intercept = np.polyfit(pd.to_datetime(df['dates']).dt.strftime("%Y%m%d").astype(int),df['spend'],1)

dffc['dates'] = (pandas.date_range(start=df.iloc[-1]['dates'], periods=120, freq= 'D'))

datetonum = pd.to_datetime(dffc['dates']).dt.strftime("%Y%m%d").astype(int)

for index, row in dffc.iterrows():

ltforecast.append((slope_intercept[0]*datetonum[index])+slope_intercept[1])

# ltforecast.append(100)

# print(dfmindex)

dffc['ltforecast'] = ltforecast

dffc['year'] = pd.DatetimeIndex(dffc['dates']).year

dffc['month'] = pd.DatetimeIndex(dffc['dates']).month

dffc1 = pd.merge(dffc,dfmindex,on ='month',how ='left')

dffc = pd.merge(dffc1,dfyindex,on ='year',how ='left')

dffc['sfc'] = (dffc['ltforecast'])*dffc['mindex']*dffc['yindex']

dffc['highpoint'] = dffc['sfc']+dffc['std']

dffc['movingavg'] = dffc['sfc'].rolling(5,closed='both').mean()

prevpeaks = pandas.DataFrame()

prevpeaks['dates']=dffc['dates']-pd.Timedelta(365, unit='D')

prevpeaks = prevpeaks.drop_duplicates()

df.groupby(['dates'])['spend'].sum().reset_index()

df['dates']=df['dates'].astype('datetime64')

prevpeaks = pd.merge(prevpeaks,df,on='dates',how='left')

prevpeaks['dates'] = prevpeaks['dates']+pd.Timedelta(365, unit='D')

plt.legend(labels=["Forecast","Upper confidence line","Last year's spend"],bbox_to_anchor = (1,1),loc=2)

sns.despine()

sns.lineplot(x=dffc['dates'],y=dffc['movingavg'])

sns.lineplot(x=dffc['dates'],y=dffc['highpoint'])

if prevpeaks['spend'].iloc[1]>=0:

sns.lineplot(x=prevpeaks['dates'],y=prevpeaks['spend'],hue=2)

plt.show()

[v-kkf-msft]

Hi @Anonymous ,

If you want to show the Python visual in Power BI Service, you need to use personal gateway for the dataset.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for your response. I've installed the personal gateway but I have no clue how to configure it. Please help me out 🙂 🙂 🙂

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Sorry for the late reply. Based on my testing, I could reproduce your problem.

 

I found that you are using rolling(5,closed='both'), which causes the error. If I deleted these two lines in the code, then it will be able to display properly in the Power BI Service.

#dffc['movingavg'] = dffc['sfc'].rolling(5,closed='both').mean()

#sns.lineplot(x=dffc['dates'],y=dffc['movingavg'])

Based on my understanding, if you want to use the closed parameter with fixed windows, you need at least version 1.2.0 of pandas. but only version 1.0.1 of pandas is supported in the Power BI Service.

So I think you need to remove the closed parameter, and it will be displayed normally.

import matplotlib.pyplot as plt
import seaborn as sns
import pandas as pd
import numpy as np

ltforecast = []
dfmindex = pandas.DataFrame ()
dfyindex = pandas.DataFrame ()
dffc = pandas.DataFrame ()
df = pandas.DataFrame({'dates': dataset["PO Creation Date"], 'spend': dataset["Commitment Value EUR"]})

df['dates'] = df['dates'].str[:10]
df['year'] = pd.DatetimeIndex(df['dates']).year
df['month'] = pd.DatetimeIndex(df['dates']).month

dfmindex['month'] = df.groupby(['month'])['spend'].mean().reset_index()['month']
dfmindex['mindex'] = df.groupby(['month'])['spend'].mean().reset_index()['spend']
dfmindex['std'] = df.groupby(['month'])['spend'].std().reset_index()['spend']
average = df['spend'].mean()
dfmindex['mindex'] = dfmindex['mindex']/average


dfyindex['year'] = df.groupby(['year'])['spend'].mean().reset_index()['year']
dfyindex['yindex'] = df.groupby(['year'])['spend'].mean().reset_index()['spend']
dfyindex['yindex'] = dfyindex['yindex']/average

slope_intercept = np.polyfit(pd.to_datetime(df['dates']).dt.strftime("%Y%m%d").astype(int),df['spend'],1)

dffc['dates'] = (pandas.date_range(start=df.iloc[-1]['dates'], periods=120, freq= 'D'))


datetonum = pd.to_datetime(dffc['dates']).dt.strftime("%Y%m%d").astype(int)

for index, row in dffc.iterrows():
    ltforecast.append((slope_intercept[0]*datetonum[index])+slope_intercept[1])

# ltforecast.append(100)



# print(dfmindex)
dffc['ltforecast'] = ltforecast

dffc['year'] = pd.DatetimeIndex(dffc['dates']).year
dffc['month'] = pd.DatetimeIndex(dffc['dates']).month

dffc1 = pd.merge(dffc,dfmindex,on ='month',how ='left')
dffc = pd.merge(dffc1,dfyindex,on ='year',how ='left')

dffc['sfc'] = (dffc['ltforecast'])*dffc['mindex']*dffc['yindex']
dffc['highpoint'] = dffc['sfc']+dffc['std']
dffc['movingavg'] = dffc['sfc'].rolling(5).mean()
prevpeaks = pandas.DataFrame()
prevpeaks['dates']=dffc['dates']-pd.Timedelta(365, unit='D')
prevpeaks = prevpeaks.drop_duplicates()

df.groupby(['dates'])['spend'].sum().reset_index()
df['dates']=df['dates'].astype('datetime64')
prevpeaks = pd.merge(prevpeaks,df,on='dates',how='left')
prevpeaks['dates'] = prevpeaks['dates']+pd.Timedelta(365, unit='D')

plt.legend(labels=["Forecast","Upper confidence line","Last year's spend"],bbox_to_anchor = (1,1),loc=2)
sns.despine()
sns.lineplot(x=dffc['dates'],y=dffc['movingavg'])
sns.lineplot(x=dffc['dates'],y=dffc['highpoint'])
if prevpeaks['spend'].iloc[1]>=0:
    sns.lineplot(x=prevpeaks['dates'],y=prevpeaks['spend'],hue=2)
plt.show()

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.