def lead_content_read(self): try: logger.info( "Start Reading:- NDP - Sage NA Lead Gen - Content Synd Tracker.xlsx at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) read_lead_content = pd.read_excel( self.section_value[12] + "NDP - Sage NA Lead Gen - Content Synd Tracker.xlsx") logger.info( "Done Reading:- NDP - Sage NA Lead Gen - Content Synd Tracker.xlsx at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) read_lead_content.rename(columns={ "Region": "Market", "Media Type": "Channel", "Vendor": "Site", "Leads": "Conversions", "Spend": "Spend Local" }, inplace=True) self.read_lead_content = read_lead_content except IOError as e: logger.error(str(e)) pass
def main(self): self.subject_line() self.login(self.section_value[5], 'Password5') self.inbox() logger.info('start downloading emails at ' + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) self.email_check(self.section_value[12]) self.close_connection() logger.info('Emails Downloaded ' + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M")))
def writing_publisher_internal(self): logger.info("Writing Internal Publisher Data") writing_internal_publisher = self.internal_publisher_data_new.to_excel(self.writer_file, index=False, sheet_name="Publisher " "Provided Data", startrow=1, startcol=1) logger.info("Done")
def writing_social_internal(self): logger.info("Writing Social Data") writing_tableau_social_data = self.social_internal_pivot_reset.to_excel(self.writer_file, index=False, sheet_name="Social Performance", startrow=1, startcol=1) logger.info("Done")
def save_and_close_writer(self): """ To finally Save and close file :return: Nothing """ self.writer_file.save() self.writer_file.close() logger.info("File has been created at {}".format(self.path) + str(datetime.datetime.now(). strftime("%Y-%m-%d %H:%M")))
def writing_publisher_uk(self): logger.info("Writing UK Publisher Data") writing_uk_pub_data = self.uk_pub_data_reset.to_excel(self.writer_file, index=False, sheet_name="Publisher Provided Data", startrow=self.internal_publisher_data_new.shape[0]+4, startcol=self.uk_pub_data_reset.shape[1]+4) logger.info("Done")
def data_reader_ndp_raw(self): logger.info("Start Reading:- NdpRawDataFile.csv at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) read_ndp_data = pd.read_csv(self.section_value[9] + "NdpRawDataFile.csv") logger.info("Done Reading:- NdpRawDataFile.csv at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) self.read_ndp_data = read_ndp_data
def writing_social_twitter(self): logger.info("Writing twitter Data") writing_twitter_social_data = self.twitter_data_player_reset.to_excel(self.writer_file, index=False, sheet_name="Social Performance", startrow=1, startcol= self.social_internal_pivot_reset. shape[1]+2) logger.info("Done")
def writing_social_facebook(self): logger.info("Writing facebook Data") writting_facbook_social_data = self.facebook_social_data.to_excel(self.writer_file, index=False, sheet_name="Social Performance", startrow= self.twitter_data_player_reset.shape[0]+4, startcol= self.social_internal_pivot_reset.shape[1]+2) logger.info("Done")
def __init__(self): super(NdpData, self).__init__() now = datetime.datetime.now() last_month = now.month-1 if now.month > 1 else 12 last_year = now.year if now.month > 1 else now.year - 1 self.path = self.section_value[10] + "Data Audit_GDS_All_Markets-{}-{:02d}.xlsx".format(last_year, last_month) logger.info("Start creating NDPFile at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) self.writer_file = pd.ExcelWriter(self.path, engine="xlsxwriter", datetime_format="YYYY-MM-DD")
def writing_publisher_market_other_us(self): logger.info("Writing Other than US/CA Publisher Data") writing_us_ca_publisher_other = self.market_publisher_other_than_us.to_excel(self.writer_file, index=False, sheet_name= "Publisher Provided Data", startrow=1, startcol=self. internal_publisher_data_new. shape[1]+2) logger.info("Done")
def writing_publisher_market_us(self): logger.info("Writing US/CA Publisher Data") writng_us_ca_publisher_data = self.final_us_ca_publisher_data.to_excel(self.writer_file, index=False, sheet_name="Publisher " "Provided Data", startrow=self. internal_publisher_data_new.shape[0]+4, startcol=1) logger.info("Done")
def internal_data(self): # Removing unconditional rows from NDP Tableau Raw Data logger.info("Start removing channel from tableau data") self.read_ndp_data_platform = self.read_ndp_data remove_row_channel = self.read_ndp_data_platform[self.read_ndp_data_platform['Channel'].isin([ 'CONTENT SYNDICATION', 'OTHER', 'LEAD AGGREGATOR'])] self.read_ndp_data_platform = self.read_ndp_data_platform.drop(remove_row_channel.index, axis=0) logger.info("Start removing BR Market from tableau data") remove_row_market = self.read_ndp_data_platform[self.read_ndp_data_platform['Market'].isin(['BR'])] self.read_ndp_data_platform = self.read_ndp_data_platform.drop(remove_row_market.index, axis=0)
def main(self, file_path): body, names, emails, subjects, attachments, cc = self.get_contacts('/home/groupm/datadump/billingdata/' 'outlookReciepientsList.csv') # read contacts # set up the SMTP server logger.info('Setting up server with: {} '.format(self.username)) s = smtplib.SMTP(host='smtp-mail.outlook.com', port=587) s.ehlo() s.starttls() s.ehlo() s.login(self.username, self.password) # For each contact, send the email: attachment = '' try: for msg_body, name, email, subject, attachment, cc in zip(body, names, emails, subjects, attachments, cc): print(file_path+attachment, emails, cc) msg = MIMEMultipart() # create a message # setup the parameters of the message msg['From'] = self.username msg['To'] = email msg['Subject'] = subject msg['Cc'] = cc part = MIMEBase('application', 'octet-stream') part.set_payload(open(file_path + attachment, 'rb').read()) encoders.encode_base64(part) part.add_header('Content-Disposition', 'attachment', filename=attachment) msg.attach(part) # add in the message body msg.attach(MIMEText(msg_body, 'plain')) print(email, cc) # send the message via the server set up earlier. # logger.info('Sending email with Subject: {} to {} '.format(subject, email)) s.sendmail(msg['From'], (email, cc), msg.as_string()) # logger.info('Email sent to {}: '.format(name)) except OSError as e: logger.error(str(e) + attachment) pass # Terminate the SMTP session and close the connection s.close()
def internal_publisher_data(self): self.read_ndp_data_publisher = self.read_ndp_data remove_row_channel_publisher = self.read_ndp_data_publisher[self.read_ndp_data_publisher['Channel'].isin( ['DISPLAY', 'SEARCH', 'SOCIAL'])] self.read_ndp_data_publisher = self.read_ndp_data_publisher.drop(remove_row_channel_publisher.index, axis=0) logger.info("Start removing BR Market from tableau data") remove_row_market_publisher = self.read_ndp_data_publisher[self.read_ndp_data_publisher['Market'].isin(['BR'])] self.read_ndp_data_publisher = self.read_ndp_data_publisher.drop(remove_row_market_publisher.index, axis=0) publisher_data_merge_platform = [self.read_ndp_data_publisher, self.read_tableau_platform_mapping] merged_internal_data_publisher = reduce(lambda left, right: pd.merge(left, right, on='Channel'), publisher_data_merge_platform) publisher_data_merge_market = [merged_internal_data_publisher, self.read_tableau_advertiser_mapping] merged_publisher_data_advertiser = reduce(lambda left, right: pd.merge(left, right, on='Market'), publisher_data_merge_market) self.merged_publisher_data_advertiser = merged_publisher_data_advertiser
def dcm_data_reader(self): logger.info("Start Reading:- DMC_Report.zip at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) read_dmc_data_zf = zipfile.ZipFile(self.section_value[12] + "DMC_Report.zip") read_dmc_data = pd.read_csv(read_dmc_data_zf.open( zipfile.ZipFile.namelist(read_dmc_data_zf)[0]), skiprows=9, skipfooter=1, engine='python', encoding="utf-8", parse_dates=['Date']) read_dmc_data = read_dmc_data[ (read_dmc_data['Date'].dt.year == self.last_year) & (read_dmc_data['Date'].dt.month == self.last_month)] logger.info("Done Reading:- DMC_Report.zip at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) self.read_dmc_data = read_dmc_data
def ndp_static_conversion_reader(self): logger.info("Start Reading:- DMC_Static_Conversions.csv at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) read_dmc_static_zf = zipfile.ZipFile(self.section_value[12] + "DMC_Static_Conversions.zip") read_dmc_static = pd.read_csv(read_dmc_static_zf.open( zipfile.ZipFile.namelist(read_dmc_static_zf)[0]), skiprows=9, skipfooter=1, engine='python', encoding="utf-8", parse_dates=['Date']) # read_conversion_raw_file = pd.read_csv(self.section_value[12] + "DMC_Static_Conversions.csv", # encoding="ISO-8859-1") logger.info("Done Reading:- DMC_Static_Conversions.csv at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) self.read_conversion_raw_file = read_dmc_static
def writing_performance(self): logger.info("Writing Internal Performance Data") writing_internal_performance = self.internal_data_performance.to_excel(self.writer_file, index=False, sheet_name= 'Display Performance', startcol=1, startrow=1) logger.info("Done") logger.info("Writing DBM Performance Data") writing_dcm_performance = self.dbm_dcm_data.to_excel(self.writer_file, index=False, sheet_name='Display Performance', startrow=1, startcol=self.internal_data_performance.shape[1]+2) logger.info("Done")
def dbm_data_reader(self): logger.info("Start Reading:- DBM_Report.zip at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) read_dbm_data_zf = zipfile.ZipFile(self.section_value[12] + "DBM_Report.zip") read_dbm_data = pd.read_csv(read_dbm_data_zf.open( zipfile.ZipFile.namelist(read_dbm_data_zf)[0]), engine='python', encoding="utf-8", error_bad_lines=False) read_dbm_data = read_dbm_data[:read_dbm_data['Date'].isnull().idxmax()] read_dbm_data['Date'] = pd.to_datetime(read_dbm_data['Date']) read_dbm_data = read_dbm_data[ (read_dbm_data['Date'].dt.year == self.last_year) & (read_dbm_data['Date'].dt.month == self.last_month)] logger.info("Done Reading:- DBM_Report.zip at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) self.read_dbm_data = read_dbm_data
def publisher_data_read(self): try: logger.info( "Start Reading:- Sage Global - Publisher Data - Daily.xlsx at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) read_publisher_data = pd.read_excel( self.section_value[12] + "Sage Global - Publisher Data - Daily.xlsx", sheet_name='Publisher Provided Data Sheet') read_publisher_data = read_publisher_data[:read_publisher_data[ 'Date'].isnull().idxmax()] logger.info( "Done Reading:- Sage Global - Publisher Data - Daily.xlsx at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) self.read_publisher_data = read_publisher_data except IOError as e: logger.error(str(e)) pass
def uk_publisher_data(self): logger.info("Start Reading:- UK Publisher files at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) path = self.section_value[14] files = os.listdir(path) files_xlsx = [f for f in files if f[-4:] == 'xlsx'] df = pd.DataFrame() for f in files_xlsx: logger.info('Start Reading filename: - ' + f + ' Sheet Name - Sheet1 ') try: data = pd.read_excel(self.section_value[14] + f, 'Sheet1') data['WorkbookName'] = f df = df.append(data, ignore_index=True, sort=True) except xlrd.biffh.XLRDError as e: pass logger.error('Sheet Name Not available at file ' + f + str(e)) logger.info( "Done Reading:- UK Publisher file " + f + ' Sheet Name - Sheet1 ' + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) df['Week'] = df.loc[:, 'Week Number'].astype(str) df['NewWeek'] = df['Week'].str.extract('(\d+)').astype(float) df = df.dropna(subset=['Market'], how='all') # df['Year'] = df['Year'].astype(datetime) # df = df[(df['Year'].dt.year == self.last_year)] df.rename(columns={ "Inquiries/Leads Delivered": "Conversions", "Inquiries/Leads ACCEPTED": "Leads ACCEPTED", "Inquiries/Leads Booked": "Leads Booked" }, inplace=True) df['NConversions'] = df['Conversions'].str.replace('["/" " "]', '-') df['NConversions'].fillna((df['Conversions']), inplace=True) df.fillna(0, inplace=True) df = df[(df['Year'] == self.last_year)] self.publisher_data_uk = df.loc[:, [ 'NewWeek', 'Market', 'WorkbookName', 'Delivered Budget', 'NConversions' ]] self.publisher_data_uk.to_excel(self.writer_file, sheet_name='UKPublisherData') self.writer_file.save() exit()
def main(self): self.get_projects() logger.info('reading issue Description' + " at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) self.issue_description() logger.info('Done!reading issue Description' + " at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) logger.info('Start!creating issue for DE' + " at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) self.de() logger.info('Done!creating issue for DE' + " at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) logger.info('Start!creating issue for FR' + " at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) self.fr() logger.info('Done!creating issue for FR' + " at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) logger.info('Start!creating issue for ES' + " at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) self.es() logger.info('Done!creating issue for ES' + " at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) logger.info('Start!creating issue for US' + " at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) self.us() logger.info('Done!creating issue for US' + " at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) logger.info('Start!creating issue for UK' + " at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) self.uk() logger.info('Done!creating issue for UK' + " at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) logger.info('Start!creating issue for MEA' + " at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) self.mea() logger.info('Done!creating issue for MEA' + " at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) logger.info('Start!creating issue for AUS' + " at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) self.aus() logger.info('Done!creating issue for AUS' + " at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) logger.info('Start!creating issue for APAC' + " at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) self.apac() logger.info('Done!creating issue for APAC' + " at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M")))
def ndp_mapping_reader(self): logger.info("Start Reading:- staticActivityConversionMapping.csv at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) read_static_site_conversion = pd.read_csv( self.section_value[9] + "staticActivityConversionMapping.csv") logger.info("Done Reading:- staticActivityConversionMapping.csv at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) logger.info("Start Reading:- socialplatformtableumapping.csv at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) read_social_site_platform = pd.read_csv( self.section_value[9] + "socialplatformtableumapping.csv") logger.info("Done Reading:- socialplatformtableumapping.csv at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) logger.info("Start Reading:- siteStaticConversionMapping.csv at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) read_site_dcm_platform_mapping = pd.read_csv( self.section_value[9] + "siteStaticConversionMapping.csv") logger.info("Done Reading:- siteStaticConversionMapping.csv at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) logger.info("Start Reading:- advertiserMarketMapping.csv at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) read_advertiser_mapping = pd.read_csv(self.section_value[9] + "advertiserMarketMapping.csv", encoding="utf-8") logger.info("Done Reading:- advertiserMarketMapping.csv at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) logger.info("Start Reading:- advertiserMappingTableau.csv at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) read_tableau_advertiser_mapping = pd.read_csv( self.section_value[9] + "advertiserMappingTableau.csv") logger.info("Done Reading:- advertiserMappingTableau.csv at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) logger.info("Start Reading:- tableauPlatformMapping.csv at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) read_tableau_platform_mapping = pd.read_csv( self.section_value[9] + "tableauPlatformMapping.csv") logger.info("Done Reading:- tableauPlatformMapping.csv at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) logger.info("Start Reading:- siteDisplayPerformanceMapping.csv at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) read_site_display_mapping = pd.read_csv( self.section_value[9] + "siteDisplayPerformanceMapping.csv") logger.info("Done Reading:- siteDisplayPerformanceMapping.csv at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) self.read_static_site_conversion = read_static_site_conversion self.read_site_dcm_platform_mapping = read_site_dcm_platform_mapping self.read_advertiser_mapping = read_advertiser_mapping self.read_tableau_advertiser_mapping = read_tableau_advertiser_mapping self.read_tableau_platform_mapping = read_tableau_platform_mapping self.read_site_display_mapping = read_site_display_mapping self.read_social_site_platform = read_social_site_platform
def writing_conversion(self): logger.info("Start Writing Internal Conversions") write_internal_data = self.internal_conversions_new.to_excel(self.writer_file, sheet_name='Conversions', index=False, startrow=1, startcol=1) logger.info("Done") logger.info("Start Writing Static Conversion") writing_static_conversion = self.data_static_conversion_new.to_excel(self.writer_file, sheet_name='Conversions', index=False, startrow=1, startcol=self.internal_conversions_new. shape[1] + 2) logger.info("Done") logger.info("Start Writing Dynamic Conversion") writing_dynamic_conversion = self.dynamic_conversion.to_excel(self.writer_file, sheet_name='Conversions', index=False, startrow=1, startcol=self.internal_conversions_new. shape[1] + 2 + self.data_static_conversion_new. shape[1] + 1) logger.info("Done")
def file_reader(self): logger.info("Start Reading:- Adwords files at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))) path = self.section_value[35] report_path = self.section_value[37] files = os.listdir(path) files_csv = [f for f in files if f[-3:] == 'csv'] for f in files_csv: logger.info('Start Reading filename: - ' + f) try: data = pd.read_csv(path + f, skiprows=1, skipfooter=1, engine='python', encoding="utf-8") data['WorkbookName'] = f data_new = pd.pivot_table(data, index=['Campaign'], values=['Clicks', 'Cost'], aggfunc=pd.np.sum) adwords_data = data_new.reset_index() adwords_data = adwords_data[(adwords_data[['Clicks']] != 0).all(axis=1)] adwords_data['Cost'] = adwords_data['Cost'] / 1000000 adwords_file_creation = pd.ExcelWriter( report_path + os.path.splitext(f)[0] + ".xlsx", engine="xlsxwriter", datetime_format="YYYY-MM-DD") adwords_data.to_excel(adwords_file_creation, index=False, startrow=6) data_info = pd.read_csv(self.section_value[34] + 'Client_id_mcc.csv', engine='python', encoding="utf-8") data_info = data_info.drop('MCCID', 1) # data_info_new = data_info.transpose() data_info_new = data_info.set_index('Client').T # data_info_new.reset_index() print(data_info_new) # exit() # for index, row in data_info.iterrows(): data_info_new.to_excel(adwords_file_creation, index=True, startrow=0, startcol=0) workbook = adwords_file_creation.book worksheet = adwords_file_creation.sheets['Sheet1'] worksheet.hide_gridlines(2) worksheet.set_zoom(75) format_header = workbook.add_format({ "bold": True, "bg_color": "#00B0F0", "border": 1 }) worksheet.conditional_format(6, 0, 6, adwords_data.shape[1], { "type": "no_blanks", "format": format_header }) border_row = workbook.add_format({"border": 1}) bold_format = workbook.add_format({ "bold": True, "bg_color": "#00B0F0", "border": 1, "num_format": "#,##0" }) worksheet.conditional_format(7, 0, adwords_data.shape[0] + 7, adwords_data.shape[1], { "type": "no_blanks", "format": border_row }) number_format = workbook.add_format({"num_format": "#,##0"}) dollar_format = workbook.add_format({"num_format": "#,##0.00"}) worksheet.conditional_format(7, 1, adwords_data.shape[0] + 7, 1, { "type": "no_blanks", "format": number_format }) worksheet.conditional_format(7, 2, adwords_data.shape[0] + 7, 2, { "type": "no_blanks", "format": dollar_format }) worksheet.write_string(adwords_data.shape[0] + 7, 0, 'Total', bold_format) worksheet.write_string(0, 0, 'Client') worksheet.write_formula( adwords_data.shape[0] + 7, 1, '=SUM(B{}:B{})'.format(8, adwords_data.shape[0] + 7), bold_format) worksheet.write_formula( adwords_data.shape[0] + 7, 2, '=SUM(C{}:C{})'.format(8, adwords_data.shape[0] + 7), bold_format) adwords_file_creation.save() adwords_file_creation.close() except (pandas.io.common.EmptyDataError, KeyError): pass logger.error(f + "is empty") logger.info( "Done Reading:- Adwords file " + f + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M")))