# -*- coding: utf-8 -*-
"""
Spyder Editor

This is a temporary script file.
"""
import pandas as pd
import numpy as np
#import xlrd
#mport re
file=pd.ExcelFile('C:/Users/1012031/MRM-Aug.xlsm')
worksheets = file.sheet_names
print('All worksheets',worksheets)

#ata_sheet_list =\
#x for x in worksheets if re.search('r',x,re.IGNORECASE)]
#rint('sheet containing sales',data_sheet_list)
 
each_data_worksheet ={}

for data in worksheets:
    each_data_worksheet[data]=pd.read_excel('C:/Users/1012031/MRM-Aug.xlsm',\
    sheet_name = data,header = 1,index_col=0,usecols="B:M")

length =len(worksheets)
#print(length)
# the dataframe to be concatenated should be of same dimension else error msg
df= pd.DataFrame()
if length > 1:
    for data in worksheets:
        df= pd.concat([df,each_data_worksheet[data]],axis =1,sort=False)     
else:
    for data in worksheets:
        df= each_data_worksheet[data]
#print(Sales & Marketing)       
#print(df.head())
print(df.columns)      
#df.to_excel('C:/Users/1012031/MRM_outt1.xlsx')
# user input for the columns
mylist =[]
Title =(input("Enter Department Name"))
numbers=int(input("Enter number of columns "))
#print(numbers)
for i in range(numbers):
    name =str(input('Enter the column Name '))
    if name not in df.columns:
#    if name not in['Sales Cost ($)','Marketing Cost ($)','Total Cost',\
#                  'YTD cost as a % of Revenue']:
        print("!!!!! Incorrect inputs !!!!!!!")
        break
    mylist.append(name)
   

print(mylist)  
df1 = df[df.columns.intersection(mylist)]
#print(df1)
for ind,row in df1.iterrows():
    value =row[0]+row[1]
#    value1 =value.cumsum()
    df1.loc[ind,"Total Cost"]= value
    df1["YTD Cost"]=df1['Total Cost'].cumsum()
    df1["YTD Revenue"]=df1['Revenue_SM ($)'].cumsum()
    df1["YTD Cost_% of Revenue"]=round(100*df1["YTD Cost"]/\
       df1["YTD Revenue"],2)
#    df1.loc[ind,"YTD Cost_% of Revenue)"]= value.cumsum(axis=None)
    pd.options.display.float_format = "{:,.0f}".format
#    df1.round(decimals=2)
    df_plot1=df1.iloc[0:13]
print(df_plot1)   
#______________ graph ploting variable_____________________________

import matplotlib.pyplot as plt
#%matplotlib inline
plt.rcParams["figure.figsize"] = [18, 12]
plt.clf()

fig1, (ax1,ax2) = plt.subplots(2,sharex=True)
ax22= ax2.twinx()
#fig2, ax1 = plt.subplots()
ax22.plot (df_plot1.index,df_plot1.iloc[:,numbers+3],label='YTD cost %',\
           marker='o')
ax2.bar (df_plot1.index,df_plot1.iloc[:,0],label= name,\
                                 edgecolor ='black', color = 'cornflowerblue')
#cornflowerblue
ax2.bar (df_plot1.index,df_plot1.iloc[:,1],label=name,\
        bottom=df_plot1.iloc[:,0],edgecolor ='black',color = 'coral')
#'coral'
#ax2.plot(df_new.index,df_new['Total Cost'],label='Total Cost',\
#edgecolor ='black', color = 'blue')
ax1.plot(df_plot1.index,df_plot1.iloc[:,numbers],label='Total Cost',\
         marker ='s',markersize=15, color = 'brown') 
#--------- annotation for columns----------------
for p in ax2.patches:
    width, height = p.get_width(), p.get_height()
    x, y = p.get_xy()
    ax2.text(x+width/1.8,
            y+height/1.8,
            '{:,.0f}'.format(height),
            horizontalalignment='center',
            verticalalignment='center',
            fontsize = 12,color ='black')
    # fontweight='bold'
#-------------annotation for total cost---------------------   
for x,y in zip(df_plot1.index,df_plot1['Total Cost']):
    label ="{:,.0f} ".format(y)
  
    ax1.annotate(label, (x,y),textcoords="offset points",                    
                        xytext=(-10,15),ha='center',fontsize = 12,color='red')
    ax1.grid(axis ='x')
 #---------------------annotation for YTD %------
for x,y in zip(df_plot1.index,df_plot1["YTD Cost_% of Revenue"]):
    label ="{:,.01f} %".format(y)
  
    plt.annotate(label, (x,y),textcoords="offset points",                    
                        xytext=(0,10),ha='center',fontsize = 12,color='red')
 #--------------------------
ax1.set_title(Title,fontsize=20)
ax22.set_ylim([3.5,6.0])
ax1.legend()
ax2.legend()
ax2.set_ylabel('Cost in $ X1000')
plt.show()
fig1.savefig(Title+'.jpg',bbox_inches='tight', dpi=100) 

# -*- coding: utf-8 -*-
"""
Spyder Editor

This is a temporary script file.
"""
import pandas as pd
import numpy as np
#import xlrd
#mport re
file=pd.ExcelFile('C:/Users/1012031/MRM-Aug.xlsm')
worksheets = file.sheet_names
print('All worksheets',worksheets)

#ata_sheet_list =\
#x for x in worksheets if re.search('r',x,re.IGNORECASE)]
#rint('sheet containing sales',data_sheet_list)
 
each_data_worksheet ={}

for data in worksheets:
    each_data_worksheet[data]=pd.read_excel('C:/Users/1012031/MRM-Aug.xlsm',\
    sheet_name = data,header = 1,index_col=0,usecols="B:M")

length =len(worksheets)
#print(length)
# the dataframe to be concatenated should be of same dimension else error msg
df= pd.DataFrame()
if length > 1:
    for data in worksheets:
        df= pd.concat([df,each_data_worksheet[data]],axis =1,sort=False)     
else:
    for data in worksheets:
        df= each_data_worksheet[data]
#print(Sales & Marketing)       
#print(df.head())
print(df.columns)      
#df.to_excel('C:/Users/1012031/MRM_outt1.xlsx')
# user input for the columns
mylist =[]
Title =(input("Enter Department Name"))
numbers=int(input("Enter number of columns "))
#print(numbers)
for i in range(numbers):
    name =str(input('Enter the column Name '))
    if name not in df.columns:
#    if name not in['Sales Cost ($)','Marketing Cost ($)','Total Cost',\
#                  'YTD cost as a % of Revenue']:
        print("!!!!! Incorrect inputs !!!!!!!")
        break
    mylist.append(name)
   

print(mylist)  
df1 = df[df.columns.intersection(mylist)]
#print(df1)
for ind,row in df1.iterrows():
    value =row[0]+row[1]
#    value1 =value.cumsum()
    df1.loc[ind,"Total Cost"]= value
    df1["YTD Cost"]=df1['Total Cost'].cumsum()
    df1["YTD Revenue"]=df1['Revenue_SM ($)'].cumsum()
    df1["YTD Cost_% of Revenue"]=round(100*df1["YTD Cost"]/\
       df1["YTD Revenue"],2)
#    df1.loc[ind,"YTD Cost_% of Revenue)"]= value.cumsum(axis=None)
    pd.options.display.float_format = "{:,.0f}".format
#    df1.round(decimals=2)
    df_plot1=df1.iloc[0:13]
print(df_plot1)   
#______________ graph ploting variable_____________________________

import matplotlib.pyplot as plt
#%matplotlib inline
plt.rcParams["figure.figsize"] = [18, 12]
plt.clf()

fig1, (ax1,ax2) = plt.subplots(2,sharex=True)
ax22= ax2.twinx()
#fig2, ax1 = plt.subplots()
ax22.plot (df_plot1.index,df_plot1.iloc[:,numbers+3],label='YTD cost %',\
           marker='o')
ax2.bar (df_plot1.index,df_plot1.iloc[:,0],label= name,\
                                 edgecolor ='black', color = 'cornflowerblue')
#cornflowerblue
ax2.bar (df_plot1.index,df_plot1.iloc[:,1],label=name,\
        bottom=df_plot1.iloc[:,0],edgecolor ='black',color = 'coral')
#'coral'
#ax2.plot(df_new.index,df_new['Total Cost'],label='Total Cost',\
#edgecolor ='black', color = 'blue')
ax1.plot(df_plot1.index,df_plot1.iloc[:,numbers],label='Total Cost',\
         marker ='s',markersize=15, color = 'brown') 
#--------- annotation for columns----------------
for p in ax2.patches:
    width, height = p.get_width(), p.get_height()
    x, y = p.get_xy()
    ax2.text(x+width/1.8,
            y+height/1.8,
            '{:,.0f}'.format(height),
            horizontalalignment='center',
            verticalalignment='center',
            fontsize = 12,color ='black')
    # fontweight='bold'
#-------------annotation for total cost---------------------   
for x,y in zip(df_plot1.index,df_plot1['Total Cost']):
    label ="{:,.0f} ".format(y)
  
    ax1.annotate(label, (x,y),textcoords="offset points",                    
                        xytext=(-10,15),ha='center',fontsize = 12,color='red')
    ax1.grid(axis ='x')
 #---------------------annotation for YTD %------
for x,y in zip(df_plot1.index,df_plot1["YTD Cost_% of Revenue"]):
    label ="{:,.01f} %".format(y)
  
    plt.annotate(label, (x,y),textcoords="offset points",                    
                        xytext=(0,10),ha='center',fontsize = 12,color='red')
 #--------------------------
ax1.set_title(Title,fontsize=20)
ax22.set_ylim([3.5,6.0])
ax1.legend()
ax2.legend()
ax2.set_ylabel('Cost in $ X1000')
plt.show()
fig1.savefig(Title+'.jpg',bbox_inches='tight', dpi=100) 

# -*- coding: utf-8 -*- Spyder Editor
 
 This is a temporary script file. #import xlrd #mport re #ata_sheet_list =\ #x for x in worksheets if re.search('r',x,re.IGNORECASE)] #rint('sheet containing sales',data_sheet_list) #print(length) # the dataframe to be concatenated should be of same dimension else error msg #print(Sales & Marketing) #print(df.head()) #df.to_excel('C:/Users/1012031/MRM_outt1.xlsx') # user input for the columns #print(numbers) #    if name not in['Sales Cost ($)','Marketing Cost ($)','Total Cost',\ #                  'YTD cost as a % of Revenue']: #print(df1) #    value1 =value.cumsum() #    df1.loc[ind,"YTD Cost_% of Revenue)"]= value.cumsum(axis=None) #    df1.round(decimals=2) #______________ graph ploting variable_____________________________ #%matplotlib inline #fig2, ax1 = plt.subplots() #cornflowerblue #'coral' #ax2.plot(df_new.index,df_new['Total Cost'],label='Total Cost',\ #edgecolor ='black', color = 'blue') #--------- annotation for columns---------------- # fontweight='bold' #-------------annotation for total cost--------------------- #---------------------annotation for YTD %------ #--------------------------