<jupyter_start><jupyter_text>Analysis of NASA Apache Web Logs and feature recommendation for website optimization Problem statement: Website optimization to predict traffic load; so that company can manage resources for handling peak traffic load Segment visitors according to how they engage with the website, map these segments to known customers in order to feed them into the right channels---*  Author - <NAME>*  Date: 29/11/2019*  Environment: Python 3.6.1 and Jupyter notebook ---- Table of contents 1 - Data gathering and reading in Python 2 - Data wrangling 3 - EDA performed in Tableau 4 - Creating new Features 5 - Conclusison and recommendation ----  1- Data gathering and reading in Python<jupyter_code># Importing all necessary Libraries

import warnings
from datetime import datetime, timedelta
import swifter
warnings.filterwarnings("ignore")
import pandas as pd
import csv


import socket
from geolite2 import geolite2
import numpy as np<jupyter_output><empty_output><jupyter_text>Reading the two .tsv files, and converting them into dataframe<jupyter_code>raw_tsv_files = glob.glob('*.tsv') # access all the files in the directory
base_df = pd.DataFrame() # create a dataframe to store all the values


for each_file in raw_tsv_files:
    total_data=[]
    with open(each_file, encoding='utf8', errors='ignore') as tsvfile:
        reader = csv.reader(tsvfile,delimiter='\t')
        for row in reader:
            total_data.append(row)
        
        df=pd.DataFrame(total_data)
        ############
        # Making the first row as dataframe headers
        df.columns = df.iloc[0]
        df = df[1:]
        ############
        base_df=pd.concat([base_df,df])
# replacing all space with nulls

base_df = base_df.replace(r'^\s*$', np.nan, regex=True)

main_df = base_df.copy()
main_df.isnull().sum()<jupyter_output><empty_output><jupyter_text>From above, we can observe that collumns = referer and useragent are nulls, hence selecting only required columns<jupyter_code>main_df=main_df[['host', 'time', 'method', 'url', 'response', 'bytes']]
main_df['response']=main_df.response.astype('int64')
main_df['bytes']=main_df.bytes.astype('int64')
main_df['method'].unique().tolist()
main_df['response'].unique().tolist()<jupyter_output><empty_output><jupyter_text>----  2- Data wrangling 2.1) Filtering only valid URL patternThe columns URL contains invalid URL, filtering them from the main data<jupyter_code>print(main_df[~main_df.url.str.contains('[A-Za-z]')]['url'].unique().tolist())
main_df = main_df[main_df.url.str.contains('[A-Za-z]')]
main_df.shape<jupyter_output><empty_output><jupyter_text>2.2)  Converting seconds to date-time formatSince the original data is given in secons, converting it to date time format through a custom function<jupyter_code>"""
Function - convert_time()
input - accept an integer
output - date time format
"""

def convert_time(date_str):
    date_str=int(date_str)
    new_date_str = (datetime.utcfromtimestamp(date_str) -  timedelta(hours=5, minutes=0)).strftime('%Y-%m-%d %H:%M:%S')
    return new_date_str
main_df['time']=main_df.swifter.apply(lambda x:convert_time(x['time']),axis=1)
main_df.head()<jupyter_output><empty_output><jupyter_text>2.3) From host_name/ IPaddress identifying the user's country<jupyter_code>"""
Function - getip()
input - accept hostname / IP address
output - Country ISO code

Logic:
For successful result store the country ISOcode or if unsuccessful, store null
"""


def getip(domain_str):
    try:
        ip = socket.gethostbyname(domain_str.strip())
        reader = geolite2.reader()      
        output = reader.get(ip)
        result = output['country']['iso_code']
    except:
        result = np.NAN
    return result
# Approach on how to implement above Function

#Step-1: Since we are interested in the country information for unqiue visitors, filter them first

unique_host_names_list = main_df['host'].unique().tolist()
len(unique_host_names_list)
# Step-2: Create an empy dictionary to store each vistior and respective country

country_dic =dict()

for each_host in unique_host_names_list:
    country_dic.update({each_host:getip(each_host)})
    
# Step-3: Now map the results back to main dataframe    
main_df['country'] = main_df['host'].map(country_dic)
main_df['country'].unique()<jupyter_output><empty_output><jupyter_text>In total, visitors represent 96 countires ; city and GeoPoint in further stages can be created similarly ![](images/countries.PNG) ------ 3 - Data Analysis & EDA  HTTP Status Code Analysis<jupyter_code>http_df = main_df.groupby(['response'])[['host']].count().sort_values(by='host',ascending=False)
http_df.reset_index(inplace=True)
http_df<jupyter_output><empty_output><jupyter_text>![](images/http.PNG) ------ Top 10 requested URLs  Count of number of times URLs have been requested (This information will help company to find out most popular pages and how frequently they are accessed)<jupyter_code>freq_url = main_df.groupby(['url'])[['host']].count().sort_values(by='host',ascending=False).head(10)
freq_url.reset_index(inplace=True)
freq_url<jupyter_output><empty_output><jupyter_text>Top Ten Error Endpoints This information is helpful for devops team to find out how many requests are failing so that appropriate action can be taken to fix the issue<jupyter_code>error_endpoints_freq_df = main_df[main_df['response']!= 200]\
                .groupby(['url'])[['host']].count().sort_values(by='host',ascending=False).head(10)


error_endpoints_freq_df.reset_index(inplace=True)
error_endpoints_freq_df<jupyter_output><empty_output><jupyter_text>Date analysis<jupyter_code>main_df['dayofweek']=main_df['time'].dt.weekday+1
main_df['day']=main_df['time'].dt.day
main_df['month']=main_df['time'].dt.month
main_df['date']=main_df['time'].dt.date
main_df['log_time']=main_df['time'].dt.time<jupyter_output><empty_output><jupyter_text>![](images/date.PNG) There is peak traffic on 13th of July and the reason is space mission was lauched![](images/mission.PNG) --- Weekly connections made ![](images/week.PNG)  From above we can observe that, weekedays the traffic is high but not on weekends _______ 4 - Creating new Features¶  4-1) Encoding categorical 'response'. Since majority of successful transcations are present, we can assign 0 and rest other unsuccessful to 1<jupyter_code>response_encode = {200:0,304:1,302:1,404:1,403:1,500:1,501:1}
main_df['response'] = main_df['response'].map(response_encode)<jupyter_output><empty_output><jupyter_text>4-2) Creating Country from hostname and IP address, based on requirement city and GeoCordinates can also be added  This step was completed in step- 2.3  4-3) From EDA, we observed that weekdays have high traffic and weekends have less, so we can encode these variables* weekdays as 0* weekends as 1<jupyter_code>weekday_encode = {1:0,2:0,3:0,4:0,5:0,6:1,7:1}
main_df['is_weekday'] = main_df['dayofweek'].map(weekday_encode)
main_df.drop('dayofweek', axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>4-4) Encoding HTTP method* GET as 0* POST as 1* HEAD as 2<jupyter_code>method_encode = {'GET':0,'POST':1,'HEAD':2}
main_df['method'] = main_df['method'].map(method_encode)

main_df.head()<jupyter_output><empty_output><jupyter_text>----- 4-5) Visitor AnalysisWe will now examine the behavior of website visitors across time (sessions).create a new Group recipe with visitor_id(ip address) as the column to group by. Output: *  average time in seconds spent by a customer in the website*  average data used by same customer<jupyter_code>def find_avg_values(df):
    
    visitor_df = df[['host','time','url','date','country','bytes']]
    grouped = visitor_df.groupby(['host','date'])
    another_df = pd.DataFrame()

    for x,y in grouped:
        if y.shape[0] >1:
            end_time = y['time'].max()
            start_time =y['time'].min()
            total_time = end_time - start_time
            y['time_spent'] = total_time.total_seconds()
            another_df=pd.concat([another_df,y])

        else:
            total_time = 30
            y['time_spent'] = total_time
            another_df=pd.concat([another_df,y])

    another_df = another_df[['host','date','time_spent','country','bytes']]
    another_df=another_df.drop_duplicates()


    visitor_avg_df = pd.DataFrame()

    second_group = another_df.groupby('host')

    for x,y in second_group:
        if y.shape[0]>1:
            y['avg_seconds']=y['time_spent'].mean()
            y['avg_data_used']=y['bytes'].mean()
            visitor_avg_df=pd.concat([visitor_avg_df,y])

        else:
            y['avg_seconds']=y['time_spent']
            y['avg_data_used']=y['bytes']
            visitor_avg_df=pd.concat([visitor_avg_df,y])

    visitor_avg_df = visitor_avg_df[['host','country','avg_seconds','avg_data_used']]
    visitor_avg_df=visitor_avg_df.drop_duplicates()
    visitor_avg_df = visitor_avg_df.sort_values(by=['avg_seconds','avg_data_used'], ascending=False)
    visitor_avg_df.reset_index(inplace=True,drop=True)
    
    
    return visitor_avg_df
sample_df=main_df.head(100000)

sample_df = find_avg_values(sample_df)
sample_df.shape
sample_df.head(10)
main_df.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12502717982170036, 'fraction_numerical': 0.014677103718199608, 'mean_word_length': 5.032131147540984, 'pattern_counts': {'":': 0, '<': 58, '<?xml version=': 0, '>': 60, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '551591', 'license': 'MIT', 'n_tokens_mistral': 3100, 'n_tokens_neox': 2967, 'n_words': 739, 'path': 'NASA_web_log_analysis.ipynb', 'repo_name': 'vikram-patil1289/NASA-web-logs-Analysis', 'size': '9206'}