Davis Chamber of Commerce Spreadsheet

In [404]:
# IMPORT PACKAGES
from bs4 import BeautifulSoup
import requests_cache
import requests
import numpy as np
import pandas as pd
import re
import random

# START CACHING
requests_cache.install_cache("demo_cache")
In [405]:
# STEP 1: RETRIEVE LIST OF URLS 

def get_links(url):
    baseurl = 'http://web.davischamber.com'
    soup = BeautifulSoup((requests.get(url)).text, "html.parser")
    companies = soup.find_all(name='li',attrs={'class':'ListingCategories_AllCategories_CATEGORY'})
    link = [x.find_all("a")[0]["href"] for x in companies]
    links = [x.encode('UTF8') for x in link]
    return ['http://web.davischamber.com' + i for i in links]
In [406]:
# STEP 2: GET CATEGORIES

links_list = get_links('http://web.davischamber.com/davis/allcategories')
categories = [links_list[i].split('/')[-1] for i in range(len(links_list))]
categories
Out[406]:
['Advertising,-Public-Relations,-Design',
 'Apartment-Communities',
 'Arts,-Culture,-Entertainment',
 'Automotive',
 'Banks-Credit-Unions',
 'Business-and-Professional-Services',
 'Community-Organizations-Nonprofits',
 'Computers,-Technology,-IT',
 'Construction-Property-Maintenance',
 'Education',
 'Finance-Insurance',
 'Food,-Beer,-Wine',
 'Funeral-Homes-Cemeteries',
 'Governmental-Public-Agencies',
 'Healthcare',
 'Hotels-Lodging',
 'Individual',
 'ManufacturingIndustry',
 'MeetingEvent-Facility',
 'Personal-CareBeautySpa',
 'Photography',
 'Real-Estate-Mortgage',
 'Shopping-and-Specialty-Retail',
 'Sports,-Recreation,-Fitness',
 'TravelTransportation',
 'VeterinariansPet-Care']
In [407]:
print links_list

['http://web.davischamber.com/davis/Advertising,-Public-Relations,-Design', 'http://web.davischamber.com/davis/Apartment-Communities', 'http://web.davischamber.com/davis/Arts,-Culture,-Entertainment', 'http://web.davischamber.com/davis/Automotive', 'http://web.davischamber.com/davis/Banks-Credit-Unions', 'http://web.davischamber.com/davis/Business-and-Professional-Services', 'http://web.davischamber.com/davis/Community-Organizations-Nonprofits', 'http://web.davischamber.com/davis/Computers,-Technology,-IT', 'http://web.davischamber.com/davis/Construction-Property-Maintenance', 'http://web.davischamber.com/davis/Education', 'http://web.davischamber.com/davis/Finance-Insurance', 'http://web.davischamber.com/davis/Food,-Beer,-Wine', 'http://web.davischamber.com/davis/Funeral-Homes-Cemeteries', 'http://web.davischamber.com/davis/Governmental-Public-Agencies', 'http://web.davischamber.com/davis/Healthcare', 'http://web.davischamber.com/davis/Hotels-Lodging', 'http://web.davischamber.com/davis/Individual', 'http://web.davischamber.com/davis/ManufacturingIndustry', 'http://web.davischamber.com/davis/MeetingEvent-Facility', 'http://web.davischamber.com/davis/Personal-CareBeautySpa', 'http://web.davischamber.com/davis/Photography', 'http://web.davischamber.com/davis/Real-Estate-Mortgage', 'http://web.davischamber.com/davis/Shopping-and-Specialty-Retail', 'http://web.davischamber.com/davis/Sports,-Recreation,-Fitness', 'http://web.davischamber.com/davis/TravelTransportation', 'http://web.davischamber.com/davis/VeterinariansPet-Care']
In [418]:
# STEP 3: GET INFO 

def get_info(url):
    soup = BeautifulSoup((requests.get(url)).text, "html.parser")
    companies = soup.find_all(name='div',attrs={'class':'ListingResults_All_CONTAINER ListingResults_Level4_CONTAINER'})
    
    # CREATE A DICTIONARY WITH WANTED INFO 
    cdict = {'company':'','address':'','phone':''} 
    
    # FIND COMPANY NAME 
    the_company = [soup.find_all('div',attrs={'class':'ListingResults_All_ENTRYTITLELEFTBOX'})[i].get_text() for i in range(len(companies))] #pulls out the first element in h1
    cdict['company'] = [x.encode('UTF8') for x in the_company]
    
    all_contact_info = [soup.find_all('div',attrs={'class':'ListingResults_Level4_MAINLEFTBOX'})[i].get_text() for i in range(len(companies))] #pulls out the first element in h1
        
    # CONVERT THE UNICODE TO STRING
    contact_info = [x.encode('UTF8') for x in all_contact_info]
       
    # POINTS OF CONTACT
    poc = [contact_info[i].split('\n')[0] for i in range(len(companies))]
    
    # ADDRESSES
    cdict['address'] = [" ".join(poc[i].split()[:-2]) for i in range(len(poc))]
      
    # PHONE
    cdict['phone'] = [contact_info[i].split('\n')[1] for i in range(len(companies))]
    
    return cdict
In [458]:
# STEP 4: CREATE DATAFRAME OF ALL INFO
def davis(num):
    df = pd.DataFrame(get_info(links_list[num]))
    df['category'] = categories[num]
    return df
In [479]:
frames = [davis(0), davis(1), davis(2), davis(3), davis(4), davis(5), davis(6), \
          davis(7), davis(8), davis(9), davis(10), davis(11), davis(12), davis(13), davis(14), \
         davis(15), davis(16), davis(17), davis(18), davis(19), davis(20), davis(21), davis(22), davis(23), davis(24), davis(25)]
dfs = pd.concat(frames)
dfs
Out[479]:
address company phone category
0 1623 Fifth Street, Ste. ADavis, CA 95616 Davis Media Access 530-757-2419 Advertising,-Public-Relations,-Design
1 1111 Drexel Dr Davis, CA Clarity Sound 530 756 3896 Advertising,-Public-Relations,-Design
2 555 Mason St., Ste. 245Vacaville, CA 95688 KUIC 95.3 Coast Radio Co., Inc. 707-446-0200 Advertising,-Public-Relations,-Design
3 1415 L Street, Ste. 900Sacramento, CA 95814 MEF Consulting 916-335-5626 Advertising,-Public-Relations,-Design
4 5232 El Cemonte AveDavis, CA 95618 Engage! Strategies 5307605270 Advertising,-Public-Relations,-Design
5 Davis, CA 95616 Smartz Graphics 530-574-7306 Advertising,-Public-Relations,-Design
6 221 G Street, Suite 202Davis, CA 95616 Abaton Consulting - Website Design Studio 530-392-8324 Advertising,-Public-Relations,-Design
7 1850 North Gateway Boulevard, Suite #152Fresno... Valley Yellow Pages 530-671-8440 Advertising,-Public-Relations,-Design
8 231 G StreetDavis, CA 95616 Mary A Grand Affair 530-747-2424 Advertising,-Public-Relations,-Design
9 PO Box 4085Davis, CA 95617 Yoursphere & Connectu Media 530-574-7204 Advertising,-Public-Relations,-Design
10 2020 F StreetDavis, CA 95616 The Davis Dirt 530-753-7368 Advertising,-Public-Relations,-Design
11 P.O. Box 275Davis, CA 95617 All Star Rents LLC 916-501-2654 Advertising,-Public-Relations,-Design
12 711 Main StreetWoodland, CA 95695 Aldrete Communications 530-662-5421 Advertising,-Public-Relations,-Design
13 315 G StreetDavis, CA 95616 The Daily Democrat 530-756-0800 Advertising,-Public-Relations,-Design
14 PO Box 188Elk Grove, CA 95759 The Davis Enterprise 916-217-0106 Advertising,-Public-Relations,-Design
15 2431 Mercantile DriveRancho Cordova, CA 95742 Town Planner Calendars 916-638-8400 Advertising,-Public-Relations,-Design
16 2515 Hepworth DriveDavis, CA 95618 River City Printers, LLC 000-000-0000 Advertising,-Public-Relations,-Design
17 P.O. Box 4715Davis, CA 95617 Musictraks 530-400-2512 Advertising,-Public-Relations,-Design
18 239 Third Street Davis, CA 95616 The People's Vanguard of Davis (707) 477-6384 Advertising,-Public-Relations,-Design
19 Davis, CA 95616 Ruud Media 530-601-9690 Advertising,-Public-Relations,-Design
20 2629 5th StreetSacramento, CA 95818 Talon Entertainment 916.233.3932 Advertising,-Public-Relations,-Design
0 515 Sycamore LaneDavis, CA 95616 University Court Apartments 530-753-5429 Apartment-Communities
1 105 E Street, Ste. 2KDavis, CA 95616 Davis Lofts, Inc. 530-756-8630 Apartment-Communities
2 4500 Alhambra DriveDavis, CA 95618 Alhambra Apartments 530-297-7368 Apartment-Communities
3 PO Box 2275 Davis, CA 95617 Star-Crossed Properties 530-757-7368 Apartment-Communities
4 1212 Alvarado Ave.Davis, CA 95616 Almondwood Apartments 530-753-2115 Apartment-Communities
5 1280 Olive Dr.Davis, CA 95616 Arbors Apartments 530-756-2424 Apartment-Communities
6 880 Alvarado Ave.Davis, CA 95616 Vaughn Management Group 530-753-5155 Apartment-Communities
0 Old Davis Road & Mrak Hall DriveDavis, CA 95616 UC Davis Mondavi Center for the Performing Arts 530-754-5000 x2 Arts,-Culture,-Entertainment
1 1919 F StreetDavis, CA 95616 Davis Arts Center 530-756-4100 Arts,-Culture,-Entertainment
... ... ... ... ...
7 650 G StreetDavis, CA 95616 Fat Face 530-758-3223 Shopping-and-Specialty-Retail
8 1411 West Covell Blvd Davis, CA 95616 Ken's Bike, Ski & Board 925-825-4001 Shopping-and-Specialty-Retail
9 26990 Rd. 95ADavis, CA 95616 Marketplace Center Inc. 530-756-8355 Shopping-and-Specialty-Retail
10 615 Second StreetDavis, CA 95616 Three Palms Nursery 530-758-6453 Shopping-and-Specialty-Retail
11 283 Main Street #190Woodland, CA 95695 Strelitzia Flower Co. 530-746-0588 Shopping-and-Specialty-Retail
12 227 G StreetDavis, CA 95616 Fleet Feet Sports 530.753.7775 Shopping-and-Specialty-Retail
13 Davis, CA 95616 Budget Blinds of Davis Member Since: 2017 Shopping-and-Specialty-Retail
14 2801 Spafford StreetDavis, CA 95618 The Vault 530-759-9648 Shopping-and-Specialty-Retail
15 4th & C Streets, Central ParkDavis, CA 95616 The Upper Crust Baking Co. 530-756-1695 Shopping-and-Specialty-Retail
16 2299 Bronze Star DriveWoodland, CA 95695 All Things Right & Relevant 530-406-2130 Shopping-and-Specialty-Retail
17 920 3rd Street, Suite FDavis, CA 95616 Davis Farmers Market 530-758-0544 Shopping-and-Specialty-Retail
18 217 F StreetDavis, CA 95616 Costco Wholesale #1003 530-753-5093 Shopping-and-Specialty-Retail
19 1300 East Covell Blvd., Suite 105Davis, CA 95616 Yolo County SPCA Thrift Store 530-753-1440 Shopping-and-Specialty-Retail
20 1671 E. Monte Vista #N-111Vacaville, CA 95687 The Paint Chip 707.449.8425 Shopping-and-Specialty-Retail
21 514 Third StreetDavis, CA 95616 Davis Ace Hardware 530-756-6247 Shopping-and-Specialty-Retail
0 302 G StreetDavis, CA 95616 Fit House 530-908-8052 Sports,-Recreation,-Fitness
1 2121 Second StreetDavis, CA 95616 Jazzercise Davis at Momentum 650-814-6839 Sports,-Recreation,-Fitness
2 1580 Flores DrivePacifica, CA 94044 Zip Line Rental 888.285.4874 Sports,-Recreation,-Fitness
3 1403 5th StreetDavis, CA 95616 SwimAmerica Davis 925-519-0579 Sports,-Recreation,-Fitness
4 775 N. East StreetWoodland, CA 95776 Fire Fit Inc 530.723.6885 Sports,-Recreation,-Fitness
5 1403 5th Street, Suite #DDavis, CA 95616 Velocity Island Park 6197218150 Sports,-Recreation,-Fitness
6 P.O. Box 921Davis, CA 95617 Iggy Training 530-757-7946 Sports,-Recreation,-Fitness
7 1562 Juliesse AveSacramento, CA 95815 Charles Davis Aquatic Masters, Inc. (877) 916-5867 Sports,-Recreation,-Fitness
8 912 5th St Suite 100Davis, CA 95616 Sacramento Party Jumps 530-207-0377 Sports,-Recreation,-Fitness
9 303 Ensenada Dr.Davis, CA 95618 The Max Challenge of Davis 530-753-5683 Sports,-Recreation,-Fitness
0 One Shields Ave., 5 South HallDavis, CA 95616 UC Davis Unitrans 530-752-2877 TravelTransportation
1 2911 Coho PlaceDavis, CA 95616 Knowing The Way Travel 530.574.3143 TravelTransportation
2 420 Second StreetDavis, CA 95616 Davisville Travel 530-758-4510 TravelTransportation
3 25170 Aviation AvenueDavis, CA 95616 Davis Flight Support 530.759.8766 TravelTransportation
4 P.O. Box 4625Davis, CA 95617 Davis Airporter 530-756-6715 TravelTransportation

423 rows × 4 columns

In [480]:
# STEP 5: WRITE TO CSV
dfs.to_csv('Davis_Chamber_of_Commerce.csv')