<jupyter_start><jupyter_text>Relevant for viz (data from DICT)* HH-012_1.txt - Regional Distribution of Households With and Without Internet Access* HH-004_1.txt - Regional Distribution of Households With and Without Television<jupyter_code>import pandas as pd

data = pd.read_csv('HH-012_1.txt', delimiter='\t', skiprows=2)
data.head()
data_tv = pd.read_csv('HH-004_1.txt', delimiter='\t', skiprows=2)
data_tv.head()
df_clean=pd.DataFrame(columns=['Region','With Internet', 'Without Internet', 'Total'])

# data to clean df
df_clean['Total'] = data[data.columns[0]].apply(lambda x:x.split()[-1])
df_clean['Without Internet'] = data[data.columns[0]].apply(lambda x: x.split()[-3])
df_clean['With Internet'] = data[data.columns[0]].apply(lambda x: x.split()[-5])
df_clean['Region'] = data[data.columns[0]].apply(lambda x: x.split()[:-5])
df_clean['Region'] = df_clean['Region'].apply(lambda x: ' '.join(x))
df_clean['Region'][2] = 'REGION I'

df_clean['With Internet'][6] = 52140
# TV data to clean df
df_clean.insert(3, 'With television', data_tv[data_tv.columns[0]].apply(lambda x: x.split()[-5]))
df_clean.insert(4, 'Without television', data_tv[data_tv.columns[0]].apply(lambda x: x.split()[-3]))

df_clean['With television'][6] = 501164
df_clean['With television'][11] = 514945
# remove commas and convert to numeric values
df_clean.replace(',', '', regex=True, inplace=True)
cols_to_conv = df_clean.columns[1:]

for col in cols_to_conv:
    df_clean[col] = pd.to_numeric(df_clean[col]).astype('int32')
    
df_clean.info()
hh_with = df_clean['With Internet'].sum()
hh_without = df_clean['Without Internet'].sum()
hh_total = hh_with + hh_without

print('national ICT Household Survey 2019')
print('Households With Access to Internet: ', hh_with)
print('Households Without Access to Internet: ', hh_without)
print('Households Surveyed: ', hh_total)

print(f'No access to Internet: {100 * hh_without / hh_total:.2f}%')
with_tv = df_clean['With television'].sum()
without_tv = df_clean['Without television'].sum()
tv_total = with_tv + without_tv

print('Households with TV: ', with_tv)
print('Households without TV: ', without_tv)
print('Households Surveyed: ', tv_total)

print(f'No access to television: {100 * without_tv / tv_total:.2f}%')
df_clean.drop(columns=['Total'], inplace=True)
df_clean
df_clean.to_excel('net_v_tv.xlsx', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16954143878838873, 'fraction_numerical': 0.02902818679007152, 'mean_word_length': 6.900332225913621, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '324365', 'license': 'MIT', 'n_tokens_mistral': 892, 'n_tokens_neox': 846, 'n_words': 184, 'path': 'ph_it.ipynb', 'repo_name': 'vanessapigwin/scrapingpractice', 'size': '2377'}