<jupyter_start><jupyter_text><jupyter_code>import requests
import subprocess
import glob

import pandas as pd
import seaborn as sns

from bs4 import BeautifulSoup
#Getting the CSV file download links from TRAI website using beautiful soup

url = "https://myspeed.trai.gov.in/"
response = requests.get(url)
soup = BeautifulSoup(response.text, 'html.parser')
links = soup.select('a[href^="download"]')
csv_urls = [url+link['href'] for link in links]

#Download all csv files
for csvs in csv_urls:
   subprocess.call(['wget', csvs])
#Exploring the file structure of each file before aggregating
filenames = glob.glob('*.csv')

for filename in filenames:
  print(filename, pd.read_csv(filename, nrows=1).columns)

#Some of the files had headers and some don't
#exclude files with a different structure. May-18 is also excluded for continuity. 
exclude_files = ['march18_myspeed.csv', 'april18_publish.csv','may18_publish.csv','june18_publish.csv','july18_publish.csv','aug18_publish.csv']

columns = ['ServiceProvider', 'Technology', 'TestType', 'DataSpeed', 'RxLevel', 'LSA']
li = []
for filename in filenames:
  if filename not in exclude_files:
    tmp_df = pd.read_csv(filename,header=None, names=columns)
    monthyear = filename.replace('_publish.csv','')
    tmp_df['month'] = pd.to_datetime(monthyear[:3]+monthyear[-2:],format='%b%y')
    li.append(tmp_df)

df = pd.concat(li, axis=0, ignore_index=True)

print(df.shape)
df.head(3)
#Data clean up and preprocessing
df.drop(df[(df['RxLevel']=='na')].index, inplace=True)
df.drop(df[(df['Technology']!='4G')].index, inplace=True)
df.drop(df[(df['ServiceProvider']=='na')].index, inplace=True)
df['RxLevel'] =  pd.to_numeric(df['RxLevel'])
print(df.shape)

aggr_df = df.groupby(['ServiceProvider','TestType', 'LSA', 'month']).mean().reset_index()
aggr_df.head(3)
sns.relplot(x='RxLevel', y='DataSpeed', hue='month', data=aggr_df, aspect=2, height=6);<jupyter_output><empty_output><jupyter_text>The scatter plot shows a divergent pattern. In general Data Speed increases with better RxLevel (Signal Strength/Coverage)Actionable Insight:1) Points where signal strength is poor (-100 or less) and speed is less requires coverage to be improved2) Point where signal strength is good (-90 or better) and still speed is less means capacity has to be increased.<jupyter_code>sns.relplot(x='month', y='DataSpeed', data=aggr_df, kind='line', aspect=3, height=4);<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14626129827444537, 'fraction_numerical': 0.013557929334428924, 'mean_word_length': 6.705696202531645, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 9, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '623263', 'license': 'MIT', 'n_tokens_mistral': 844, 'n_tokens_neox': 815, 'n_words': 213, 'path': 'Telecom_LTE_Data_Speed_Analysis.ipynb', 'repo_name': 'ms-shyamkumar/Exploratory-Data-Analysis', 'size': '2434'}