' + df_to_print + '
' return df_to_print # Bowery Ballroom and Mercury Lounge use the same website def pull_bowery_mercury(HEADERS,which_venue): if which_venue=='Bowery Ballroom': url = 'https://mercuryeastpresents.com/boweryballroom/' pages = 4 elif which_venue=='Mercury Lounge': url = 'https://mercuryeastpresents.com/mercurylounge/' pages = 5 df = [] # 1 page at a time for n in np.arange(pages): # initial page = requests.get(url+'page/'+str(n+1)+'/',headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') event_calendar = soup.find_all('div',{'class':"tw-details-container"}) # organize entries for item in event_calendar: # get event name, url, and date event_name = item.find('a').string event_url = item.find('a')['href'] event_date = item.find('span',{'class':"tw-event-date"}).string # indicate whether the event is sold out if 'SOLD OUT' in event_name: is_sold_out = 1 else: is_sold_out = 0 # remove the *SOLD OUT* string for neatness event_name = event_name.replace("*SOLD OUT* ","") # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out] }) ) df = pd.concat(df) df.reset_index(drop=True,inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df # Hotel Cafe (LA) def pull_hotelcafe(HEADERS): df = [] which_months = [ '', # current month (today + relativedelta(months=1)).strftime('%Y-%m'), (today + relativedelta(months=2)).strftime('%Y-%m'), (today + relativedelta(months=3)).strftime('%Y-%m') ] for each_month in which_months: url = f'https://new.hotelcafe.com/events/month/{each_month}' page = requests.get(url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') long_string = soup.find('script',{'type':'application/ld+json'}).string.replace('\n','').replace('\t','') event_calendar = json.loads(long_string) for item in event_calendar: event_name = item['name'] event_url = item['url'] event_date = item['startDate'].split("T")[0] if 'offers' in item.keys(): is_sold_out = True if item['offers']['availability']=='SoldOut' else False else: is_sold_out = False # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out] }) ) df = pd.concat(df) df.reset_index(drop=True,inplace=True) df.drop_duplicates(inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df # Elsewhere Zone 1 def pull_elsewhere(HEADERS): url = 'https://www.elsewhere.club/events' df = [] # initial pull page = requests.get(url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') event_calendar = soup.find_all("time",{"class":"listing-compact_listing-compact__date__XDK6C font-t4"}) # organize entries for item in event_calendar: # get event name, url, and date event_date = item.string event_name = item.next_sibling.span.string event_url = 'https://www.elsewhere.club' + item.next_sibling.a['href'] # indicate whether the event is sold out if item.next_sibling.find('li',{'class':'listing-badge-list_listing-badge-list__sold-out__vwA99 badge'}) is not None: is_sold_out = 1 else: is_sold_out = 0 # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out] }) ) df = pd.concat(df) df.reset_index(drop=True,inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_cafedunord_swedishamericanhall(HEADERS): url = 'https://cafedunord.com/' df = [] # initial pull page = requests.get(url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') event_calendar = soup.find_all('div',{'class':'row'}) # organize entries for item in event_calendar: try: # get event name, url, and date event_date = item.find('span',{'class':'tw-event-date'}).string event_name = item.find('div',{'class':['six columns']}).find('span').string event_url = item.find('div',{'class':['six columns']}).find('a')['href'] event_location = item.find('span',{'class':'tw-venue-name'}).string.replace('\n','').strip() except: continue # indicate whether the event is sold out if ( item.find('div',{'class':['three columns']}).find('a',{'class':'button button-primary tw-buy-tix-btn tw_soldout'}) or item.find('div',{'class':['three columns']}).find('a',{'class':'button button-primary tw-buy-tix-btn tw_wait list'}) ): is_sold_out = 1 else: is_sold_out = 0 # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out], 'VENUE': [event_location], 'REGION': ['San Francisco'] }) ) df = pd.concat(df) df.reset_index(drop=True,inplace=True) # this result will have duplicates - remove here df = df.drop_duplicates(['NAME','DATE']).reset_index(drop=True) cafedunord = df.loc[df['VENUE']=='Cafe Du Nord'].reset_index(drop=True) swedish = df.loc[df['VENUE']!='Cafe Du Nord'].reset_index(drop=True) ################# do this twice bc 2 venues ################## # count number of shows & number of sold out shows cafedunord['N_SHOWS'] = cafedunord.groupby('NAME')['IS_SOLD_OUT'].transform('count') cafedunord['N_SOLD_OUT'] = cafedunord.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out cafedunord['ALL_SOLD_OUT'] = cafedunord['N_SHOWS'] == cafedunord['N_SOLD_OUT'] ################# do this twice bc 2 venues ################## # count number of shows & number of sold out shows swedish['N_SHOWS'] = swedish.groupby('NAME')['IS_SOLD_OUT'].transform('count') swedish['N_SOLD_OUT'] = swedish.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out swedish['ALL_SOLD_OUT'] = swedish['N_SHOWS'] == swedish['N_SOLD_OUT'] return cafedunord,swedish def pull_tveye(HEADERS): df = [] # this website's listings are on separate pages so we have to check each page until there are no more pages. # i did try to get total number of pages but they only show 7 pages per view page_numbers = 20 # there shouldn't be more than this much # per page for which_page in np.arange(page_numbers): which_page_url = f'https://tveyenyc.com/calendar/?list1page={which_page+1}' # initial to get event urls page = requests.get(which_page_url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') # stop if thishas a message (would say "No events on sale at this time") if len(soup.find_all('p', class_="no-events"))>0: break events_raw = soup.find_all('p',class_="fs-18 bold mb-12 title") event_names = [event_raw.get_text() for event_raw in events_raw] event_urls = [event_raw.a['href'] for event_raw in events_raw] dates = soup.find_all('p',class_="fs-18 bold mt-1r date") event_dates = [each_date.get_text() for each_date in dates] # times = soup.find_all('p',class_="fs-12 doortime-showtime") # event_times = [each_time.get_text() for each_time in times] sold_out_status_raw = soup.find_all('a',class_="seetickets-buy-btn") sold_out_status = [each_status.get_text() for each_status in sold_out_status_raw] df.append(pd.DataFrame({ 'NAME': event_names, 'DATE': event_dates, # 'TIME': event_times, 'URL': event_urls, 'SOLD_OUT_STATUS': sold_out_status })) df = pd.concat(df) df.reset_index(drop=True,inplace=True) # df['DATE'] = df['DATE'] + ' ' + df['TIME'] df['IS_SOLD_OUT'] = np.where( df['SOLD_OUT_STATUS']=='Buy Tickets', 0, 1 ) df = df[['NAME','DATE','URL','IS_SOLD_OUT']] # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_theecho(HEADERS): url = f'https://www.theecho.com/shows' # initial page = requests.get(url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') event_names_raw = soup.find_all('p',class_="chakra-text css-zvlevn") event_names = [event_name.get_text() for event_name in event_names_raw] event_dates_raw = soup.find_all('p',class_="chakra-text css-lfdvoo") event_dates = [event_date.get_text() for event_date in event_dates_raw] event_urls_raw = soup.find_all('a',class_="chakra-button css-1q88drx") event_urls = [event_url["href"] for event_url in event_urls_raw] df = pd.DataFrame({ 'NAME': event_names, 'DATE': event_dates, 'URL': event_urls }) # sold out based on whether the event name says so df['IS_SOLD_OUT'] = np.where( df['NAME'].str.upper().str.contains('SOLD OUT'), 1, 0 ) # need to remove " - SOLD OUT" from event_name otherwise won't match previous listing in snowflake table df['NAME'] = df['NAME'].str.replace(r' - SOLD OUT', '', case=False, regex=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_troubadour(HEADERS): url = 'https://troubadour.com/calendar/' # initial page = requests.get(url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') event_names_raw = soup.find_all('p',class_="fs-12 bold m-0") event_names = [event_name.get_text() for event_name in event_names_raw] event_dates_raw = soup.find_all('div',class_="seetickets-buy-btn") sold_out_status = [event_date.find('a').get_text() for event_date in event_dates_raw] event_dates = [event_date.find('a').get('aria-label') for event_date in event_dates_raw] event_urls = [event_date.find('a').get('href') for event_date in event_dates_raw] df = pd.DataFrame({ 'NAME':event_names, 'DATE_STR':event_dates, 'SOLD_OUT_STR':sold_out_status, 'URL':event_urls }) df['DATE'] = df['DATE_STR'].str[-6:] df['IS_SOLD_OUT'] = np.where( df['SOLD_OUT_STR']=='Sold Out', 1, 0 ) df = df[['NAME','DATE','URL','IS_SOLD_OUT']] # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_basement(HEADERS): url = 'https://www.thebasementnashville.com/basement/calendar/' # initial page = requests.get(url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') # Step 1: Find all script blocks with JavaScript script_tags = soup.find_all('script', type='text/javascript') target_script_content = None # Step 2: Identify the correct script tag that contains EventData for script in script_tags: if script.string and 'EventData.events.push' in script.string: target_script_content = script.string break # Stop once we've found the relevant one pattern = r'EventData\.events\.push\((\{.*?\})\);' matches = re.findall(pattern, target_script_content, re.DOTALL) events = [json.loads(match) for match in matches] event_names = [each_event['value'] for each_event in events] event_urls = [each_event['data']['url'] for each_event in events] df = pd.DataFrame({ 'NAME':event_names, 'URL':event_urls }) # sold out based on whether the event name says so df['IS_SOLD_OUT'] = np.where( df['NAME'].str.upper().str.contains('SOLD OUT'), 1, 0 ) # need to remove " - SOLD OUT" from event_name otherwise won't match previous listing in snowflake table df['NAME'] = df['NAME'].str.replace(r'SOLD OUT! ', '', case=False, regex=True) df['DATE'] = df['NAME'].str[-5:] df['NAME'] = df['NAME'].str[:-6] df = df[['NAME','DATE','URL','IS_SOLD_OUT']] # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_philamoca(HEADERS): url = 'https://www.philamoca.org/' # initial page = requests.get(url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') event_names_raw = soup.find_all('span',class_="event__title") event_names = [event_name.get_text() for event_name in event_names_raw] event_dates_raw = soup.find_all('time',class_="event__date") event_dates = [event_date['datetime'] for event_date in event_dates_raw] sold_out_str_raw = soup.find_all('li',class_="event__detail event__detail--tickets") sold_out_str = [each_str.find('span',class_="event__detail-label").get_text() for each_str in sold_out_str_raw] urls_raw = soup.find_all('a',class_="event") event_urls = [each_url["href"] for each_url in urls_raw] df = pd.DataFrame({ 'NAME':event_names, 'DATE':event_dates, 'SOLD_OUT_STATUS':sold_out_str }) if len(event_urls) != len(event_names): # remove private events before attaching URL (because private events won't have URL avail) to_drop = df.loc[df['SOLD_OUT_STATUS'].str.upper().str.contains('INVITATION')].index df.drop(to_drop,inplace=True) df['URL'] = event_urls df['IS_SOLD_OUT'] = np.where( df['SOLD_OUT_STATUS'].str.contains('Sold Out'), 1, 0 ) df = df[['NAME','DATE','URL','IS_SOLD_OUT']] # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_johnnybrendas(HEADERS): url = 'https://johnnybrendas.com/events/' page = requests.get(url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') event_calendar = soup.find_all('div',{'class':'rhpSingleEvent'}) df = [] for item in event_calendar: event_name = item.find('a')['title'] event_url = item.find('a')['href'] event_date = item.find('div',{'class':"mb-0"}).string.strip('\n\t') is_sold_out = True if item.find('span', class_=['col-12', 'rhp-event-cta']).get('class', [])[1]=='sold-out' else False # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out] }) ) df = pd.concat(df) df.reset_index(drop=True,inplace=True) df.drop_duplicates(inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_theearl(HEADERS): urls = ['https://badearl.com/','https://badearl.com/?sf_paged=2','https://badearl.com/?sf_paged=3'] df = [] for which_url in urls: page = requests.get(which_url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') event_calendar = soup.find_all('div',{'class':'cl-element cl-element-section cl-element--instance-1023 show-listing-right'}) for item in event_calendar: event_name = item.find('div',{'class':'show-listing-headliner'}).string event_url = item.find('a',{'class':'cl-element-link__anchor'})['href'] event_date = item.find('p',{'class':'show-listing-date'}).string if item.find('div',{'class':'sold-out'}) is not None: is_sold_out = True else: is_sold_out = False # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out] }) ) df = pd.concat(df) df.reset_index(drop=True,inplace=True) df.drop_duplicates(inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_aisle5(HEADERS): url = 'https://aisle5atl.com/calendar/' page = requests.get(url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') event_calendar = soup.find_all('div',{'class':'seetickets-list-event-content-container'}) df = [] for item in event_calendar: event_url = item.find('a')['href'] event_name = item.find('a').string event_date = item.find('p',{'class':'event-date'}).string is_sold_out = True if item.find('a',{'class':'button-soldout'}) is not None else False # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out] }) ) df = pd.concat(df) df.reset_index(drop=True,inplace=True) df.drop_duplicates(inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_eddiesattic(HEADERS): url = 'https://eddiesattic.com/api/plot/v1/listings?currentpage=1¬Loaded=false&listingsPerPage=48&_locale=user' page = requests.get(url,headers=HEADERS,timeout=5) event_calendar = page.json() df = [] for item in event_calendar: event_name = item['title'] event_url = item['permalink'] event_date = item['dateTime'].lstrip('').rstrip('') is_sold_out = True if item['ticket']['text']=='Sold out' else False # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out] }) ) df = pd.concat(df) df.reset_index(drop=True,inplace=True) df.drop_duplicates(inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_sinclairboston(HEADERS): url = 'https://www.sinclaircambridge.com/events/all' page = requests.get(url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content, 'html.parser') event_calendar = soup.find_all(class_="entry") df = [] for item in event_calendar: event_name = item.find('h3').find('a').string.replace('\t','').replace('\n','') event_url = item.find('a')['href'] event_date = item.find('span',{'class':'date'}).get_text(separator=" ", strip=True) ticket_status = item.find('a',{'class':'btn-tickets'})['title'] if ticket_status in ['Buy Tickets','Get Tickets','Box Office Only','Cancelled','Coming Soon']: is_sold_out = False else: is_sold_out = True # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out] }) ) df = pd.concat(df) df.reset_index(drop=True,inplace=True) df.drop_duplicates(inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_babysallright(HEADERS): urls = [ 'https://donyc.com/venues/baby-s-all-right/month_events/', 'https://donyc.com/venues/baby-s-all-right/month_events/?period=1', 'https://donyc.com/venues/baby-s-all-right/month_events/?period=2' ] df = [] for which_url in urls: page = requests.get(which_url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content, 'html.parser') event_calendar = soup.find_all('div',{'class':'ds-listing'}) for item in event_calendar: event_name = item.find('span',{'class':'ds-listing-event-title-text'}).string event_url = 'https://donyc.com' + item.find('div').parent['data-permalink'] pattern = r'/events/(\d{4}/\d{1,2}/\d{1,2})/' match = re.search(pattern, event_url) event_date = match.group(1) is_sold_out = True if item.find('li',{'class':'ds-listing-soldout'}) is not None else False # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out] }) ) time.sleep(2) df = pd.concat(df) df.reset_index(drop=True,inplace=True) df.drop_duplicates(inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_purgatory(HEADERS): urls = [ 'https://donyc.com/venues/purgatory/month_events/', 'https://donyc.com/venues/purgatory/month_events/?period=1', 'https://donyc.com/venues/purgatory/month_events/?period=2' ] df = [] for which_url in urls: page = requests.get(which_url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content, 'html.parser') event_calendar = soup.find_all('div',{'class':'ds-listing'}) for item in event_calendar: event_name = item.find('span',{'class':'ds-listing-event-title-text'}).string event_url = 'https://donyc.com' + item.find('div').parent['data-permalink'] pattern = r'/events/(\d{4}/\d{1,2}/\d{1,2})/' match = re.search(pattern, event_url) event_date = match.group(1) is_sold_out = True if item.find('li',{'class':'ds-listing-soldout'}) is not None else False # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out] }) ) time.sleep(2) df = pd.concat(df) df.reset_index(drop=True,inplace=True) df.drop_duplicates(inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_schubas_lh(HEADERS): url = 'https://lh-st.com' df = [] page = requests.get(url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content, 'html.parser') event_calendar = soup.find_all('div',{'class':'card'}) for item in event_calendar: event_venue = item.find('div').parent['data-venue'] event_url = item.find('a')['href'] event_date = item.find('span',{'class':'date'}).string + ' - ' + event_venue event_name = item.find('h4',{'class':'card-title'}).string event_id = item.find('div').parent['id'] # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'ID': [event_id] }) ) df = pd.concat(df) df.reset_index(drop=True,inplace=True) df.drop_duplicates(inplace=True) id_list = '%2C'.join(df['ID'].astype(str)) temp_headers = {'User-Agent': 'Mozilla/5.0 (iPad; CPU OS 12_2 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148','SessionId':'RUFBQUFPMkd0NmlvVXNPeE5lUjRTWVliNkJHNGdHaHVDQTRBNVlHQ1VYazZqbU1UcENLdEsvWnFYQW9BdzN2MUU0VHRyYmFkaXNobG5VLy9YMjFBSS93Z3VzQT0='} url = 'https://tickets.lh-st.com/api/v1/products?ids=' + id_list page = requests.get(url,headers=temp_headers,timeout=5) page = page.json() md = pd.DataFrame({ 'ID':[item['frontendId'] for item in page], 'IS_SOLD_OUT':[item['isSoldOut'] for item in page] }) # merge by id df = df.merge(md,how='inner',on='ID') df.drop('ID',axis=1,inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_emptybottle(HEADERS): url = 'https://app.ticketmaster.com/discovery/v2/events.json?size=200&apikey=GmC9AB6l4pDhA5yhg4dgD3G0AEDK8wmL&venueId=rZ7HnEZ178gfg&venueId=KovZpZAId16A&venueId=rZ7HnEZ17aJ47&source=ticketmaster,ticketweb' page = requests.get(url,headers=HEADERS,timeout=5) event_calendar = json.loads(page.content)['_embedded']['events'] df = [] for item in event_calendar: event_name = item['name'] event_date = item['dates']['start']['localDate'] event_url = item['url'] is_sold_out = True if 'sold out' in event_name.lower() else False event_name = event_name.replace('*SOLD OUT* ','') # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out] }) ) df = pd.concat(df) df.reset_index(drop=True,inplace=True) df.drop_duplicates(inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_bellwether(HEADERS): url = 'https://thebellwetherla.com/listing/' page = requests.get(url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') event_calendar = soup.find_all('div',{'class':'content-information'}) df = [] for item in event_calendar: event_date = item.find('div',{'class':'date-show'})['content'] event_name = item.find('h2',{'class':'show-title'}).string event_url = item.find('div',{'class':'entry'}).find('a')['href'] is_sold_out = True if 'sold out' in item.find('a',{'class':'button'}).string.lower() else False # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out] }) ) df = pd.concat(df) df.reset_index(drop=True,inplace=True) df.drop_duplicates(inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_broadberryco(HEADERS): url = 'https://thebroadberry.com/events/' page = requests.get(url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') event_calendar = soup.find_all('div',{'class':'eventWrapper'}) df = [] for item in event_calendar: event_url = item.find('a')['href'] event_name = item.find('a')['title'] event_date = item.find('div',{'id':'eventDate'}).string.replace('\n\t ','') is_sold_out = True if 'sold out' in item.find('a',{'class':'btn'}).string.lower() else False event_venue = item.find('a',{'class':'venueLink'})['title'] # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out], 'WHICH_VENUE': [event_venue] }) ) df = pd.concat(df) df.reset_index(drop=True,inplace=True) df.drop_duplicates(inplace=True) df = df.loc[df['WHICH_VENUE'].isin(['Richmond Music Hall','The Broadberry'])] df['DATE'] = df['DATE'] + ' - ' + df['WHICH_VENUE'] df.drop('WHICH_VENUE',axis=1,inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_thecamel(HEADERS): url = 'https://www.thecamel.org/shows' page = requests.get(url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') event_calendar = soup.find_all('div',{'class':'uui-layout88_item'}) df = [] for item in event_calendar: event_url = 'https://www.thecamel.org/shows' + item.find('a')['href'] event_date = item.find('div',{'class':'event-month-multi'}).string event_name = item.find('h3',{'class':'uui-heading-xxsmall-2'}).string is_sold_out = True if len(item.find_all('img',{'loading':'lazy'})[1]['class'])==1 else False # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out] }) ) df = pd.concat(df) df.reset_index(drop=True,inplace=True) df.drop_duplicates(inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_thecrocodile(HEADERS): df = [] for which_page in [1,2,3,4,5]: url = f'https://www.ticketweb.com/venue/the-crocodile-seattle-wa/10352?page={which_page}' page = requests.get(url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') long_string = soup.find('script',{'type':'application/ld+json'}).string.replace('\n','').replace('\t','') event_calendar = json.loads(long_string) for item in event_calendar: event_name = item['name'] event_url = item['url'] event_date = item['startDate'].split("T")[0] is_sold_out = True if item['offers']['availability']=='SoldOut' else False # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out] }) ) df = pd.concat(df) df.reset_index(drop=True,inplace=True) df.drop_duplicates(inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_themint(HEADERS): url = 'https://www.ticketweb.com/venue/the-mint-los-angeles-ca/206385' page = requests.get(url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') long_string = soup.find('script',{'type':'application/ld+json'}).string.replace('\n','').replace('\t','') event_calendar = json.loads(long_string) df = [] for item in event_calendar: event_name = item['name'] event_url = item['url'] event_date = item['startDate'].split("T")[0] is_sold_out = True if item['offers']['availability']=='SoldOut' else False # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out] }) ) df = pd.concat(df) df.reset_index(drop=True,inplace=True) df.drop_duplicates(inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_themusicbox(HEADERS): df = [] for which_page in [1,2,3]: url = f'https://www.ticketweb.com/venue/music-box-san-diego-ca/420435?page={which_page}' page = requests.get(url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') long_string = soup.find('script',{'type':'application/ld+json'}).string.replace('\n','').replace('\t','') event_calendar = json.loads(long_string) for item in event_calendar: event_name = item['name'] event_url = item['url'] event_date = item['startDate'].split("T")[0] is_sold_out = True if item['offers']['availability']=='SoldOut' else False # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out] }) ) df = pd.concat(df) df.reset_index(drop=True,inplace=True) df.drop_duplicates(inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_somasidestage(HEADERS): df = [] url = 'https://www.ticketweb.com/venue/soma-sidestage-san-diego-ca/434165' page = requests.get(url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') long_string = soup.find('script',{'type':'application/ld+json'}).string.replace('\n','').replace('\t','') event_calendar = json.loads(long_string) for item in event_calendar: event_name = item['name'] event_url = item['url'] event_date = item['startDate'].split("T")[0] is_sold_out = True if item['offers']['availability']=='SoldOut' else False # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out] }) ) df = pd.concat(df) df.reset_index(drop=True,inplace=True) df.drop_duplicates(inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def pull_middleeastupstairs(HEADERS): df = [] for which_page in [1,2,3]: url = f'https://www.ticketweb.com/venue/middle-east-upstairs-cambridge-ma/18394?page={which_page}' page = requests.get(url,headers=HEADERS,timeout=5) soup = BeautifulSoup(page.content,'html.parser') long_string = soup.find('script',{'type':'application/ld+json'}).string.replace('\n','').replace('\t','') event_calendar = json.loads(long_string) for item in event_calendar: event_name = item['name'] event_url = item['url'] event_date = item['startDate'].split("T")[0] is_sold_out = True if item['offers']['availability']=='SoldOut' else False # append result df.append( pd.DataFrame({ 'NAME': [event_name], 'DATE': [event_date], 'URL': [event_url], 'IS_SOLD_OUT': [is_sold_out] }) ) df = pd.concat(df) df.reset_index(drop=True,inplace=True) df.drop_duplicates(inplace=True) # count number of shows & number of sold out shows df['N_SHOWS'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('count') df['N_SOLD_OUT'] = df.groupby('NAME')['IS_SOLD_OUT'].transform('sum') # Indicate whether all events from the same artist are sold out df['ALL_SOLD_OUT'] = df['N_SHOWS'] == df['N_SOLD_OUT'] return df def setup_logger(): import logging logger = logging.getLogger() logger.setLevel(logging.INFO) # Add a logger handler if none exists if not logger.hasHandlers(): handler = logging.StreamHandler() formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s') handler.setFormatter(formatter) logger.addHandler(handler) return logger ################################################### ################################################### def handler(event,context): logger = setup_logger() logger.info('Starting handler function...') import snowflake.connector sf_params = init_secrets() sf_params['snowflake'] = snowflake email_params = setup_email_params() ################################################### ################################################### # READ/ORGANIZE DATA ################################################### ################################################### destination_email_list = orcd_query('select * from awal.awal_ar.email_alerts',sf_params) run_artist_engagement = True run_sold_out_shows = True if run_artist_engagement: # Main result adj_result = orcd_query(f""" select a.* from awal.awal_ar.discovery_artist_table_cleaned a left join awal.awal_ar.discovery_daily_streams d1 on d1.song_id=a.biggest_song_id left join awal.awal_ar.discovery_daily_streams d2 on d2.song_id=a.latest_song_id WHERE 1=1 and ((d1.gl_day_7>5000) or (d2.gl_day_7>5000)) and NOT ( -- ARTIST ( -- completely contain special characters REGEXP_LIKE(ARTIST_NAME, '^[Ͱ-Ͽ]+$') -- greek or REGEXP_LIKE(ARTIST_NAME, '^[Ѐ-ӿ]+$') -- russian or REGEXP_LIKE(ARTIST_NAME, '^[Ā-ɏ]+$') -- turkish or REGEXP_LIKE(ARTIST_NAME, '^[Ⴀ-ჿ]+$') -- georgian or REGEXP_LIKE(ARTIST_NAME, '^[՛-֏]+$') -- armenian or REGEXP_LIKE(ARTIST_NAME, '^[ក-]+$') -- cambodian or REGEXP_LIKE(ARTIST_NAME, '^[က-႟]+$') -- burmese or REGEXP_LIKE(ARTIST_NAME, '^[-]+$') -- tamil or REGEXP_LIKE(ARTIST_NAME, '^[ऀ-ॿ]+$') -- hindi/sanskrit -- contain special characters or REGEXP_LIKE(ARTIST_NAME, '.*[-ۿ].*') -- arabic or REGEXP_LIKE(ARTIST_NAME, '.*[-].*') -- hebrew or REGEXP_LIKE(ARTIST_NAME, '.*[ᄀ-ᇿ-가-힣].*') -- korean or REGEXP_LIKE(ARTIST_NAME, '.*[一-鿿].*') -- chinese or REGEXP_LIKE(ARTIST_NAME, '.*[ぁ-ゟ゠-ヿ一-鿿].*') -- japanese or REGEXP_LIKE(ARTIST_NAME, '.*[ĩứưẤÅåắăặạậệâỗốờơộșȘ±¨].*') -- Vietnamese / Portuguese ) ) and ( (COUNTRY_OF_ORIGIN not in ('TR','RU')) or (COUNTRY_OF_ORIGIN is null) ) """,sf_params) adj_result.rename(columns={'ARTIST_NAME':'ARTIST'},inplace=True) latest_date_main = adj_result['LATEST_DATE_MAIN'].iloc[0] date_pulled_main = adj_result['DATE_PULLED_MAIN'].iloc[0] date_pulled_weekly_streams = adj_result['DATE_PULLED_WEEKLY_STREAMS'].iloc[0] date_pulled_daily_streams = adj_result['DATE_PULLED_DAILY_STREAMS'].iloc[0] adj_result.drop(['LATEST_DATE_MAIN','DATE_PULLED_MAIN','DATE_PULLED_WEEKLY_STREAMS','DATE_PULLED_DAILY_STREAMS'],axis=1,inplace=True) adj_result['LATEST_SONG_DATA'] = f"https://app.luminatedata.com/song/" + adj_result['LATEST_SONG_ID'].astype('str') + f"?g=AA&d=YTD&stf=Q018U0UsSU5ULFBDfENOLFZJfC8%3D&ssf=Lw%3D%3D&psf=Lw%3D%3D&a=ST&b=CM&sd=2025-01-03&ed={date_pulled_main}&stgl=R0xCLVBWL1NFfEJTLENOfEJTLENNfEJT&psgl=R0xCLVNULw%3D%3D&ga=&m=VVNNLy9OQVRJT05BTA%3D%3D" adj_result['BIGGEST_SONG_DATA'] = f"https://app.luminatedata.com/song/" + adj_result['BIGGEST_SONG_ID'].astype('str') + f"?g=AA&d=YTD&stf=Q018U0UsSU5ULFBDfENOLFZJfC8%3D&ssf=Lw%3D%3D&psf=Lw%3D%3D&a=ST&b=CM&sd=2025-01-03&ed={date_pulled_main}&stgl=R0xCLVBWL1NFfEJTLENOfEJTLENNfEJT&psgl=R0xCLVNULw%3D%3D&ga=&m=VVNNLy9OQVRJT05BTA%3D%3D" ################################### # # active awal core releases # awal_core_active = orcd_query(f""" # with pre_table as ( # select # s.song_id, # a.spotify_artist_id, # a.title, # a.INGESTION_COMPLETED_DATE ingestion_date # from awal.awal_ar.awal_core_active a # join awal.awal_ar.isrc_map_song_id s on s.isrc=a.isrc # where a.spotify_artist_id is not null # ), # ranked AS ( # SELECT *, # ROW_NUMBER() OVER (PARTITION BY song_id ORDER BY ingestion_date DESC) AS rn # FROM pre_table # ) # SELECT spotify_artist_id,title,ingestion_date # FROM ranked # WHERE rn = 1; # """) # awal_core_active['INGESTION_DATE'] = pd.to_datetime(awal_core_active['INGESTION_DATE']).dt.strftime('%m/%d/%y') # awal_core_active['CORE'] = np.where( # awal_core_active['INGESTION_DATE'].isna(), # awal_core_active['TITLE'], # awal_core_active['TITLE'] + ' (as of ' + awal_core_active['INGESTION_DATE'] + ')' # ) # awal_core_active.drop(['TITLE','INGESTION_DATE'],axis=1,inplace=True) # awal_core_active = awal_core_active.groupby(['SPOTIFY_ARTIST_ID'], as_index=False).agg(CORE=('CORE', ', '.join)) ################################### adj_result['LATEST_SONG_WTD'] = adj_result['LATEST_SONG_WTD'] *100 / adj_result['GL_WTD_TP'] adj_result['BIGGEST_SONG_WTD'] = adj_result['BIGGEST_SONG_WTD'] *100 / adj_result['GL_WTD_TP'] # # daily streams plot # for region in ['GL']: # adj_result[region+'_DAILY'] = adj_result[[ # region+'_DAY_1',region+'_DAY_2',region+'_DAY_3',region+'_DAY_4',region+'_DAY_5',region+'_DAY_6',region+'_DAY_7' # ]].values.tolist() # # weekly streams plot # for region in ['GL']: # adj_result[region+'_WEEKLY'] = adj_result[[ # region+'_LW4',region+'_LW3',region+'_LW2',region+'_LW1' # ]].values.tolist() # CHG # # LW adj_result['GL_LW_CHG'] = adj_result['GL_LW1'] - adj_result['GL_LW2'] adj_result['GL_LW2_ADJUSTED'] = adj_result['GL_LW2'].clip(lower=0.0001) adj_result['GL_LW_PCT'] = adj_result['GL_LW_CHG'] *100 / adj_result['GL_LW2_ADJUSTED'] # cap % at 999% adj_result['GL_LW_PCT'].clip(lower=-999,upper=999,inplace=True) # WTD adj_result['GL_WTD_CHG'] = adj_result['GL_WTD_TP'] - adj_result['GL_WTD_LP'] adj_result['GL_WTD_LP_ADJUSTED'] = adj_result['GL_WTD_LP'].clip(lower=0.0001) adj_result['GL_WTD_PCT'] = adj_result['GL_WTD_CHG'] *100 / adj_result['GL_WTD_LP_ADJUSTED'] # cap % at 999% adj_result['GL_WTD_PCT'].clip(lower=-999,upper=999,inplace=True) #################### SOCIALS #################### followers_dict={} followers_dates={} for platform in ['SPOTIFY','INSTAGRAM','TIKTOK']: followers_dict[platform] = orcd_query(f""" select * from awal.awal_ar.discovery_{platform}_followers """,sf_params) followers_dates[platform] = followers_dict[platform]['DATE_UPDATED'].iloc[0] followers_dict[platform].drop(['DATE_UPDATED'],axis=1,inplace=True) followers_dict[platform].rename(columns={ 'TOTAL_FOLLOWERS':f'{platform}_TOTAL_FOLLOWERS', 'FOLLOWERS_7DAY':f'{platform}_FOLLOWERS_7DAY', 'FOLLOWERS_7DAY_GROWTH':f'{platform}_FOLLOWERS_7DAY_GROWTH', },inplace=True) try: # spotify doesn't have this followers_dict[platform]['URL'] = followers_dict[platform]['URL'].str.rstrip('/') + '/' followers_dict[platform].rename(columns={ 'URL':f'{platform}_PROFILE', },inplace=True) except: pass followers_dict[platform][f'{platform}_FOLLOWERS_7DAY_GROWTH'] = followers_dict[platform][f'{platform}_FOLLOWERS_7DAY_GROWTH'] * 100 # cap % at 999% followers_dict[platform][f'{platform}_FOLLOWERS_7DAY_GROWTH'].clip(lower=-999,upper=999,inplace=True) ## Tiktok engagement via chartmetric calc tiktok_engagement = orcd_query('select * from awal.awal_ar.discovery_tiktok_engagement',sf_params) followers_dict['TIKTOK'] = followers_dict['TIKTOK'].merge(tiktok_engagement,how='outer',on='SPOTIFY_ARTIST_ID') followers_dict['TIKTOK']['TIKTOK_PROFILE'] = followers_dict['TIKTOK']['TIKTOK_PROFILE'].fillna(followers_dict['TIKTOK']['URL']) followers_dict['TIKTOK'] = followers_dict['TIKTOK'].drop(columns='URL') ######################################## # # get core applicants # applicant_ids = orcd_query("select * from awal.awal_ar.awal_applicants where STATUS not like '%Rejected%'") # applicant_ids['STATUS'] = applicant_ids['STATUS'].str.replace('Artist ','') # applicant_ids.rename(columns={'LATEST_UPDATE':'CORE_LATEST_UPDATE'},inplace=True) # rearrange columns adj_result = adj_result[[ 'SPOTIFY_ARTIST_ID','ARTIST', 'LABEL','TRACK_URL','N_SONGS', 'GL_WTD_TP','GL_WTD_PCT','GL_WTD_CHG', # 'GL_DAILY', 'GL_DAY_1','GL_DAY_2','GL_DAY_3','GL_DAY_4','GL_DAY_5','GL_DAY_6','GL_DAY_7', 'GL_LW1','GL_LW_PCT','GL_LW_CHG', # 'GL_WEEKLY', 'GL_LW2','GL_LW3','GL_LW4', 'LATEST_SONG','LATEST_SONG_WTD','LATEST_RELEASE_DATE','LATEST_SONG_DATA', 'BIGGEST_SONG', 'BIGGEST_SONG_WTD','BIGGEST_RELEASE_DATE','BIGGEST_SONG_DATA', 'GENRE', 'PREVIEW_URL','COUNTRY_OF_ORIGIN' ]] adj_result.sort_values('GL_WTD_PCT',ascending=False,inplace=True) if date_pulled_main==date_pulled_weekly_streams==date_pulled_daily_streams: data_is_accurate = True else: data_is_accurate = False # daily growth (streams) # greatest_gainers_us,steady_growth_us = group_songs(adj_result,'US') greatest_gainers_gl,steady_growth_gl = group_songs(adj_result,'GL') greatest_gainers = [] # greatest_gainers.extend(greatest_gainers_us) greatest_gainers.extend(greatest_gainers_gl) greatest_gainers = list(set(greatest_gainers)) # drop duplicates steady_growth = [] # steady_growth.extend(steady_growth_us) steady_growth.extend(steady_growth_gl) steady_growth = list(set(steady_growth)) # drop duplicates ## Setup dates ## # NOTE - Daily/weekly streams are pulled based on the main table so as long as the pull-script ran, then LW1 or DAY_7 correspond to the expected dates. # Just need to check that all my tables were updated on the same day. if data_is_accurate==False: logger.info('Data not up to date. Skipping this section.') else: # current building week today = date_pulled_main bw_start_date_tp = today - relativedelta(days=today.weekday()) + relativedelta(days=4, weeks=-1) bw_end_date_tp = latest_date_main # today - relativedelta(days=2) # luminate latest day typically 2 days behind n_building_days = (bw_end_date_tp - bw_start_date_tp).days + 1 # LP (last week) bw_start_date_lw = bw_start_date_tp + relativedelta(weeks=-1) bw_end_date_lw = bw_start_date_lw + relativedelta(days=6) # LP (2 weeks ago) bw_start_date_2w = bw_start_date_lw + relativedelta(weeks=-1) bw_end_date_2w = bw_start_date_2w + relativedelta(days=6) # LP (3 weeks ago) bw_start_date_3w = bw_start_date_2w + relativedelta(weeks=-1) bw_end_date_3w = bw_start_date_3w + relativedelta(days=6) # LP (4 weeks ago) bw_start_date_4w = bw_start_date_3w + relativedelta(weeks=-1) bw_end_date_4w = bw_start_date_4w + relativedelta(days=6) ################################################### ################################################### # DAILY ARTIST EMAIL ################################################### ################################################### email_head = f"""\
A&R Research Report: Artists {today}