<jupyter_start><jupyter_text>INFO 6210 Ski Resort Recommend  Introduction Skiing is a popular sport in the United States. Most people would like to the ski resort to spending vacations with their families. However, there are many choices for them to choose a good ski resort, like weather, location, hill heights, and so on, that all these factors will affect their skiing time. Besides, people go to vacation may not only for skiing purpose, they maybe still interested in different scenes and views in the same city.  Looking for a good vacation place including ski resorts seems to be a task which involve bunch of research works. People even need to check the weather forecast for each of dream resort. As a result, we want to build a database about all information about ski resorts and other kind of information for the cities, weather forecast, scenes and so on. The database will help people evaluate the comprehensive destination that satisfies ski purpose. It helps people to make a better choice.  Data Resource Ski resort information from Kaggle![1](https://user-images.githubusercontent.com/47193601/56831275-a0fd7a80-6836-11e9-83ed-7f6c846ef5a4.jpg)Ski resort information from Twitter![2](https://user-images.githubusercontent.com/47193601/56831327-becadf80-6836-11e9-9fc3-bc53683a2428.png) ![3](https://user-images.githubusercontent.com/47193601/56831347-cc806500-6836-11e9-91fc-b0a0cc2694d5.png)Consumer information from Instagram![4](https://user-images.githubusercontent.com/47193601/56831377-e752d980-6836-11e9-9d74-831fec9f3d4c.png) ![5](https://user-images.githubusercontent.com/47193601/56831388-e9b53380-6836-11e9-9a3f-ecd27db031c6.png)Ski resort information from google map![6](https://user-images.githubusercontent.com/47193601/56831391-ec178d80-6836-11e9-9549-652bb585edbe.png)![7](https://user-images.githubusercontent.com/47193601/56831393-ede15100-6836-11e9-8ad5-021f37c1591d.jpg)Ski resort information from Dark Sky website![8](https://user-images.githubusercontent.com/47193601/56831401-f5a0f580-6836-11e9-898c-2fc8700d4d8f.png)cloudcover![9](https://user-images.githubusercontent.com/47193601/56831404-f76ab900-6836-11e9-893d-bea424e64f7e.jpg)humidity![10](https://user-images.githubusercontent.com/47193601/56831416-018cb780-6837-11e9-9536-1dbcdf3adaca.jpg)pressure![11](https://user-images.githubusercontent.com/47193601/56832811-f471c780-683a-11e9-89e3-715f7ae88916.jpg)temperature_max![12](https://user-images.githubusercontent.com/47193601/56832815-f76cb800-683a-11e9-81d4-1c1cb8c440a9.jpg)temperature_min![13](https://user-images.githubusercontent.com/47193601/56832817-f9367b80-683a-11e9-9169-674d33c462d1.jpg)visibility![14](https://user-images.githubusercontent.com/47193601/56832819-fb98d580-683a-11e9-99be-e70043c8b214.jpg)windspeed![15](https://user-images.githubusercontent.com/47193601/56832824-fd629900-683a-11e9-8b3e-7fdd239f7418.jpg)  ER Digram ![16](https://user-images.githubusercontent.com/47193601/56832832-ff2c5c80-683a-11e9-87db-cb7e8aa6deb4.png)  News and Trend We just extract those consumers information from Instagram who post posts during 24 hours in 500 hashtags, everyday we will get total about 1000 posts to MySQL and we have get 5000 posts till today.And same situation, we extract weather information from Dark sky website during 7 days. Every 7 days, we will update the information in database.![17](https://user-images.githubusercontent.com/47193601/56833939-115bca00-683e-11e9-86f1-d07e68ced0ac.jpg)![18](https://user-images.githubusercontent.com/47193601/56833946-1456ba80-683e-11e9-93cd-035258737db4.jpg)![19](https://user-images.githubusercontent.com/47193601/56833952-16207e00-683e-11e9-91d5-0611b6f5e965.jpg)  Import MySQL<jupyter_code>import pymysql
db = pymysql.connect("localhost","root","19960226","infoproject")
c = db.cursor()
c.execute("""SELECT * FROM skiresort_usa""")
for row in c.fetchall():
    print(row)
c.execute("""SELECT * FROM project_twitter WHERE followers_count > 1000""")
for row in c.fetchall():
    print(row)
def ct(i):
    c.execute("SELECT COUNT(*) FROM project_twitter WHERE followers_count >  ")
    for row in c.fetchall():
        print(row)
sql_1 = """SELECT * FROM skiresort_usa"""
c.execute(sql_1)
for row in c.fetchall():
    print(row)
sql_2 = "SELECT COUNT(*) FROM project_twitter WHERE followers_count >= %s "
g = 1000
c.execute(sql_2 , (g))
for row in c.fetchall():
    print(row)
c.execute("SELECT COUNT(*) FROM project_twitter")
for row in c.fetchall():
    print(row)<jupyter_output>(297,)<jupyter_text>Implement Function<jupyter_code>#count the number of twitter_accounts with followers over a particular number
def f1():
    h = input("Please input followers number : ")
    c.execute(sql_2 , h)
    for row in c.fetchall():
        print(row,)
f1()
sql_3 = """SELECT skiresort_usa.Resort_Name , skiresort_usa.State , skiresort_usa.Altitude , project_twitter.followers_count FROM skiresort_usa JOIN project_twitter on project_twitter.twitter_screen_name = skiresort_usa.twitter_screen_name WHERE skiresort_usa.Altitude >= %s AND skiresort_usa.State LIKE %s ORDER BY project_twitter.followers_count DESC"""
#select all the resorts have altitude over a particular number and in one GIVEN state
def f2():
    b = input('Please input altitude :') 
    d = input('Please input state :')
    c.execute(sql_3, (b,d))
    for row in c.fetchall():
        print(row)
f2()
c.execute("""SELECT * FROM hash WHERE record_id > 1000""")
sql_4 = """SELECT COUNT(*) FROM hash WHERE hashtag LIKE %s AND post_date LIKE %s """
#get the number of posts about a hashtag in a given day
def f3():
    x = input('Please input the Hashtag name : ')
    y = input('Please input the date : ')
    c.execute(sql_4 , (x,y))
    for row in c.fetchall():
        print(row)
f3()
sql_5 = """SELECT AVG(post_likes) FROM hash WHERE hashtag LIKE %s AND post_date LIKE %s """
#get the averange likes of all posts about a hashtag in a given day
def f4():
    x = input('Please input the Hashtag name : ')
    y = input('Please input the date : ')
    c.execute(sql_4 , (x,y))
    for row in c.fetchall():
        print(row)
#db.close()
f4()
sql_6 = """SELECT Resort_Name , State , Adult_ticket FROM skiresort_usa WHERE Adult_ticket >= 120 AND State LIKE %s"""
sql_7 = """SELECT Resort_Name , State , Adult_ticket FROM skiresort_usa WHERE State LIKE %s AND Adult_ticket >= 60 AND Adult_ticket < 120"""
sql_8 = """SELECT Resort_Name , State , Adult_ticket FROM skiresort_usa WHERE Adult_ticket < 60 AND State LIKE %s"""
#get the associated ski resorts in a given state around the given adult ticket price
def f5():
    d = input('please input your state : ')
    a = input('please input your expected ticket price : ')
    b = int(a)
    if b >= 120:
        c.execute(sql_6, d)
        for row in c.fetchall():
            print(row,'expensive');
    elif 60 <= b < 120:
        c.execute(sql_7, d)
        for row in c.fetchall():
            print(row,'medium') ;
    elif b < 60:
        c.execute(sql_8, d)
        for row in c.fetchall():
            print(row,'cheap')  ;       
        
f5()
sql_7 = """SELECT twitter_name , twitter_screen_name , location , followers_count FROM project_twitter WHERE followers_count < 3000"""
sql_8 = """SELECT twitter_name , twitter_screen_name , location , followers_count FROM project_twitter WHERE followers_count >= 3000 AND followers_count <10000"""
sql_9 = """SELECT twitter_name , twitter_screen_name , location , followers_count FROM project_twitter WHERE followers_count >= 10000 AND followers_count < 30000"""
sql_10 = """SELECT twitter_name , twitter_screen_name , location , followers_count FROM project_twitter WHERE followers_count >= 30000"""
#find a twitter account is famous or not
def f6():
    rep = input("Please input the reputation : unfamous, little_famous, state_famous, very_famous")
    re = str(rep)
    if re in 'unfamous':
        c.execute(sql_7)
        for row in c.fetchall():
            print(row,rep);
    elif re in 'little_famous':
        c.execute(sql_8)
        for row in c.fetchall():
            print(row,rep);
    elif re in 'state_famous':
        c.execute(sql_9)
        for row in c.fetchall():
            print(row,rep);
    elif re in 'very_famous':
        c,execute(sql_10)
        for row in c.fetchall():
            print(row,rep);
    
f6()<jupyter_output>Please input the reputation : unfamous, little_famous, state_famous, very_famousstate_famous
('Alta Ski Area', 'AltaSkiArea', 'Alta, Utah', 28724) state_famous
('Arizona Snowbowl', 'AZSnowbowl', 'High above Flagstaff, Arizona', 13642) state_famous
('Big Bear Mountain Resort', 'BigBearMtResort', 'Big Bear Lake', 21358) state_famous
('Big Sky Resort', 'bigskyresort', 'Montana', 26643) state_famous
('Crested Butte', 'skicrestedbutte', 'Crested Butte, Colorado', 22621) state_famous
('Crystal Mountain', 'CrystalMt', 'WA - Next to Mt. Rainier', 17401) state_famous
('Deer Valley Resort', 'Deer_Valley', 'Park City, UT', 20337) state_famous
('Sun Valley Resort', 'sunvalley', 'Sun Valley, Idaho USA', 21389) state_famous
('Grand Targhee Resort', 'grandtarghee', 'Alta, WY', 12857) state_famous
('Holiday Valley', 'holidayvalley', 'Ellicottville, NY', 14537) state_famous
('Jay Peak Resort', 'jaypeakresort', '<NAME>', 24078) state_famous
('Loon Mountain', 'loonmtn', 'Lincoln, NH', 16697) state_[...]
{'fraction_non_alphanumeric': 0.11665773968934119, 'fraction_numerical': 0.09983931440814141, 'mean_word_length': 4.641087613293052, 'pattern_counts': {'":': 0, '<': 16, '<?xml version=': 0, '>': 19, 'https://': 19, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '530140', 'license': 'MIT', 'n_tokens_mistral': 3696, 'n_tokens_neox': 3079, 'n_words': 843, 'path': 'ski_resort_project_portfolio.ipynb', 'repo_name': 'howardxjy/info6210_project_team_Cerberus', 'size': '9341'}