<jupyter_start><jupyter_text>1.  Data ETL  1.1 Data LoadingLoad weblog files from Google Drive into a Pandas dataframe and monitor number of files processed<jupyter_code># To access files on Google Drive
from google.colab import drive

drive.mount('/content/drive') 
# Location of the tar file on Google Drive
file_name = '/content/drive/MyDrive/weblog.tar.gz'
!tar -tzf $file_name
import tarfile
import pandas as pd

# Empty list to hold dataframe for each file
data = []

# Variable to store number of files processed 
filecount = 0

# Define column headers
col_names=['date','time','s-sitename','s-ip','cs-method','cs-uri-stem',
           'cs-uri-query','s-port','cs-username', 'c-ip','cs(User-Agent)',
           'cs(Referer)','sc-status','sc-substatus','sc-win32-status']

with tarfile.open(file_name) as tar:
  for logfile in [n for n in tar.getnames() if n.endswith('.log')]:
    print('Processing: ', logfile)
    df = pd.read_csv(tar.extractfile(logfile), 
                     delim_whitespace=True, # fields are space delimited
                     comment= '#', # To remove 4 info lines at top of logfile
                     header = None, # Header has extra 'Fields:' column
                     na_values='-', # NA columns are marked with - in logfile
                     names=col_names, # Set column headers
                     error_bad_lines=False, encoding = 'iso-8859-1')
    data.append(df)
    filecount+= 1

# Cleanup
del(df)

print('Number of files processed: ', filecount)

# Concatenate all dataframes in the list into a single dataframe
df_web = pd.concat(data)
# View 5 rows of the data
df_web.head()
# View data types and total rows for the dataframe
df_web.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 295612 entries, 0 to 77462
Data columns (total 15 columns):
 #   Column           Non-Null Count   Dtype  
---  ------           --------------   -----  
 0   date             295612 non-null  object 
 1   time             295612 non-null  object 
 2   s-sitename       295612 non-null  object 
 3   s-ip             295612 non-null  object 
 4   cs-method        295612 non-null  object 
 5   cs-uri-stem      295612 non-null  object 
 6   cs-uri-query     18883 non-null   object 
 7   s-port           295612 non-null  int64  
 8   cs-username      0 non-null       float64
 9   c-ip             295612 non-null  object 
 10  cs(User-Agent)   295554 non-null  object 
 11  cs(Referer)      238989 non-null  object 
 12  sc-status        295439 non-null  float64
 13  sc-substatus     295439 non-null  float64
 14  sc-win32-status  295439 non-null  float64
dtypes: float64(4), int64(1), object(10)
memory usage: 36.1+ MB<jupyter_text>1.2 Data CleaningRemove columns with > 15% NAs then remove remaining records with any NAs.<jupyter_code># Find NAs in each column
df_web.isna().sum()
# Find % of NAs in each column
round(df_web.isna().sum()/len(df_web) * 100)<jupyter_output><empty_output><jupyter_text>The following columns have a high percentage of NAs and need to be dropped: *   cs-uri-query        94%*   cs-username        100%*   cs(Referer)          19%<jupyter_code># Drop 3 columns with > 15% NAs
df_web = df_web.drop(['cs-uri-query', 'cs-username', 'cs(Referer)'], axis=1)
df_web.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 295612 entries, 0 to 77462
Data columns (total 12 columns):
 #   Column           Non-Null Count   Dtype  
---  ------           --------------   -----  
 0   date             295612 non-null  object 
 1   time             295612 non-null  object 
 2   s-sitename       295612 non-null  object 
 3   s-ip             295612 non-null  object 
 4   cs-method        295612 non-null  object 
 5   cs-uri-stem      295612 non-null  object 
 6   s-port           295612 non-null  int64  
 7   c-ip             295612 non-null  object 
 8   cs(User-Agent)   295554 non-null  object 
 9   sc-status        295439 non-null  float64
 10  sc-substatus     295439 non-null  float64
 11  sc-win32-status  295439 non-null  float64
dtypes: float64(3), int64(1), object(8)
memory usage: 29.3+ MB<jupyter_text>Drop remaining rows containing NAs.<jupyter_code># Drop rows with NAs
df_web.dropna(axis=0, how= 'any', inplace= True)
# Find updated number of NAs in each column
df_web.isna().sum()
# Updated data counts
df_web.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 295381 entries, 0 to 77462
Data columns (total 12 columns):
 #   Column           Non-Null Count   Dtype  
---  ------           --------------   -----  
 0   date             295381 non-null  object 
 1   time             295381 non-null  object 
 2   s-sitename       295381 non-null  object 
 3   s-ip             295381 non-null  object 
 4   cs-method        295381 non-null  object 
 5   cs-uri-stem      295381 non-null  object 
 6   s-port           295381 non-null  int64  
 7   c-ip             295381 non-null  object 
 8   cs(User-Agent)   295381 non-null  object 
 9   sc-status        295381 non-null  float64
 10  sc-substatus     295381 non-null  float64
 11  sc-win32-status  295381 non-null  float64
dtypes: float64(3), int64(1), object(8)
memory usage: 29.3+ MB<jupyter_text>Data has been cleaned.  Number of rows remaining is 295,381   2. Data Analysis<jupyter_code>import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>2.1 Traffic AnalysisGroup web requests by hour and day to determine hourly traffic trends.<jupyter_code># Group number of requests by hour of day

# Create a new column with the hour values e.g., 00, 01, 02, .., 23
df_web['hour'] = df_web['time'].str.slice(0, 2)

# Count number of requests by hour
hour_distr = df_web['hour'].value_counts().sort_index()
hour_distr
# Create a bar chart of the web traffic distribution by hour
%matplotlib inline

plt.style.use('seaborn-pastel')

# Figure Size
fig = plt.figure(figsize =(10, 7))

plt.bar(hour_distr.index, hour_distr.values)
plt.xlabel('Hour of Day', fontweight ='bold', fontsize = 15)
plt.ylabel('Number of Web Requests', fontweight ='bold', fontsize = 15)
plt.title('Requests per Hour', fontweight ='bold', fontsize = 18)
plt.show()
# Group number of requests by hour and day

# Create a new column with the date and hour values e.g., 2006-11-0100, 2006-11-0101, 2006-11-0102, ..
df_web['date_hour'] = df_web.date.str.cat(df_web.hour)

# Count number of requests by date and hour
date_hour_distr = df_web['date_hour'].value_counts().sort_index()
date_hour_distr
# Create a bar chart of the web traffic distribution by date and hour
%matplotlib inline

plt.style.use('seaborn-pastel')

# Figure Size
fig = plt.figure(figsize =(10, 7))

plt.bar(date_hour_distr.index, date_hour_distr.values)
plt.xlabel('Hourly Traffic: 1 - 5 Nov 2006', fontweight ='bold', fontsize = 15)
plt.xticks([])
plt.ylabel('Number of Web Requests', fontweight ='bold', fontsize = 15)
plt.title('Requests per Hour', fontweight ='bold', fontsize = 18)
plt.show()
# Filter for times with traffic above 5000 requests per hour
df_5k = date_hour_distr[(date_hour_distr >= 5000)].sort_index().reset_index()
df_5k.columns = ['Date_Hour', 'Traffic']
df_5k
# Filter for times with traffic below 200 requests per hour
df_low = date_hour_distr[(date_hour_distr < 200)].sort_index().reset_index()
df_low.columns = ['Date_Hour', 'Traffic']
df_low
# Find least request in an hour

print('Least request per hour is', date_hour_distr.min(), 'at', date_hour_distr.idxmin())<jupyter_output>Least request per hour is 171 at 2006-11-0321<jupyter_text>2.2 Server AnalysisView server responses breakdown using the sc-status column.<jupyter_code>df_web['sc-status'] = df_web['sc-status'].astype(int)

# Group number of requests by server code status
srv_code_distr = df_web['sc-status'].value_counts()
srv_code_distr
# how many types of status reported?
len(srv_code_distr)
# list all server status codes
srv_code_distr.index.sort_values()
# only a few status codes are returned frequently 
# the remaining codes happen only make up a small percentage
# save data into a dict for extraction into 'other' category
sc_dict = srv_code_distr.to_dict()

# separate the frequent codes 
big_slice = { k:v for (k,v) in sc_dict.items() if v >= 1000 }
big_slice
# collect the remaining codes into the 'other' category
other_slice = { k:v for (k,v) in sc_dict.items() if v < 1000 }
other_slice
# add the 'other' category into the big_slice dict
big_slice['other'] =  sum(other_slice.values())
big_slice
# Create a pie chart of the Server Response Code distribution
%matplotlib inline

# create a figure with 2 side by side plots
fig, (ax1, ax2) = plt.subplots(nrows= 1, ncols= 2, figsize=(12, 7),
                               gridspec_kw={'width_ratios': [3, 1]})

# Pie chart
# explode the wedge for the smallest values
explode = [0, 0, 0, 0.1, 0.7]
# line properties
wedge_props = {'linewidth': 1, 'edgecolor': 'gray'}
# set pie chart text to bold 
text_props = {'weight': 'bold'}
ax1.pie(big_slice.values(), autopct='%.2f%%', labels=big_slice.keys(), 
        explode = explode, wedgeprops=wedge_props, 
        startangle=40, textprops=text_props)
ax1.set_title('Server Status Codes', fontdict= {'fontweight': 'bold', 'fontsize': 18})

# Other bar
xpos = 0
bottom = 0
width = 2
colors = ['aqua', 'lightblue', 'plum', 'yellow', 'indigo', 'blue', 'red']
for i, (k,v) in enumerate(other_slice.items()):
    height = v
    ax2.bar(xpos, height, width, bottom=bottom, color= colors[i] )
    bottom += height

ax2.set_title('Other Status Codes')
ax2.legend(other_slice.keys())
ax2.axis('off')
ax2.set_xlim(- 1.5 * width, 2.5 * width)

plt.show()<jupyter_output><empty_output><jupyter_text>2.3 Geographic AnalysisUse **DbIpCity** from **ip2geotools** to find the geolocation information from given IPs for a single hour - 3rd Nov 2006 9pm.<jupyter_code># discard the stdout stream for this cell
%%capture  
!pip install ip2geotools<jupyter_output><empty_output><jupyter_text>2.3.1 Requests by Country<jupyter_code># Extract records for 3rd Nov 2006 21:00 - 21:59
df_03Nov_9pm = df_web[(df_web['date_hour'] == '2006-11-0321')]

# How many records?
print(len(df_03Nov_9pm.index))<jupyter_output>171<jupyter_text>There are 171 requests in total.Pass the IP Address for each row of data to DbIpCity.get().country method to extract source country for the IP Address. Store the Country information in a new column in the dataframe<jupyter_code>from ip2geotools.databases.noncommercial import DbIpCity

# for each row, lookup the country information using IP Address
df_03Nov_9pm['country'] = df_03Nov_9pm.loc[:, 'c-ip'].apply(lambda x: DbIpCity.get(x, api_key='free').country)
# Get distribution of requests by Country 
country_distr = df_03Nov_9pm['country'].value_counts()
country_distr
# Verify the sum of requests by countries matches number of rows in df 
country_distr.sum()
# Create a pie chart of the country distribution
%matplotlib inline

plt.style.use('seaborn-pastel')

# explode the wedge for the smallest values
explode = [0, 0, 0, 0.3, 0.5, 0.7]
# line properties
wedge_props = {'linewidth': 1, 'edgecolor': 'gray'}
# text properties
text_props = {'weight': 'bold'}
plt.pie(country_distr.values, labels=country_distr.index, 
        autopct='%.2f%%', explode=explode,
        wedgeprops=wedge_props, textprops=text_props,
        radius = 2.5)
plt.title('Request by Country', y = 1.6, 
          fontdict= {'fontweight': 'bold'})
plt.show()<jupyter_output><empty_output><jupyter_text>2.3.2 Requests by City<jupyter_code># discard the stdout stream for this cell
%%capture
# for each row, lookup the city information using IP Address
df_03Nov_9pm['city'] = df_03Nov_9pm.loc[:, 'c-ip'].apply(lambda x: DbIpCity.get(x, api_key='free').city)
# Get distribution of requests by Cityy 
city_distr = df_03Nov_9pm['city'].value_counts()
print(city_distr)
# Verify the sum of requests by cities matches number of rows in df 
city_distr.sum()
# how many origin cities
len(city_distr)
# Create a pie chart of the city distribution
plt.style.use('seaborn-pastel')

# explode the wedge for the smallest values
explode = [0, 0, 0, 0, 0.3, 0.6, 0.9, 1.2, 0.3, 0.6, 0.9]
plt.pie(city_distr.values, labels=city_distr.index, 
        autopct='%1.1f%%', explode=explode,
        wedgeprops=wedge_props, textprops=text_props,
        radius = 2.5)
plt.title('Request by City', 
          y = 1.6, 
          fontdict= {'fontweight': 'bold'})
plt.show()
# Get top 3 cities by number of request. 
# Since the series is already sorted, use head()
city_distr.head(3)
# View request distribution  by country and city
df_03Nov_9pm.groupby(['country', 'city']).size().reset_index(name='count')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11642050390964379, 'fraction_numerical': 0.054024168707053156, 'mean_word_length': 3.4916637105356507, 'pattern_counts': {'":': 0, '<': 45, '<?xml version=': 0, '>': 47, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '43468', 'license': 'MIT', 'n_tokens_mistral': 4781, 'n_tokens_neox': 4241, 'n_words': 1304, 'path': 'weblog_analysis_python.ipynb', 'repo_name': 'e-s-ball/Data-Science-Projects', 'size': '12661'}