<jupyter_start><jupyter_text>**READING IN KINESSO DATA**<jupyter_code>imp = pd.read_csv('impressions_one_hour.csv')
imp = imp[imp['country'] == 'Germany']
imp = imp[~imp['zip_code'].isna()]
imp['zip_code'] = imp['zip_code'].astype(str)<jupyter_output><empty_output><jupyter_text>**READING IN 2011 GERMANY CENSUS DATA**<jupyter_code># data from: https://www.suche-postleitzahl.org/downloads
zip_codes = pd.read_csv("plz_einwohner.csv")

def add_zero(x):
    if len(x) == 4:
        return '0'+ x
    else:
        return x<jupyter_output><empty_output><jupyter_text>**CORRECTING FORMATTING ERROR THAT REMOVED INITIAL '0' FROM ZIPCODES**<jupyter_code>zip_codes['zipcode'] = zip_codes['zipcode'].astype(str).apply(add_zero)
zip_codes.head()<jupyter_output><empty_output><jupyter_text>Real Population of Germany is 83.02 million<jupyter_code>np.sum(zip_codes['population'])<jupyter_output><empty_output><jupyter_text>**CALCULATING VALUE COUNTS FROM KINESSO DATA**<jupyter_code>val_cou = imp['zip_code'].value_counts()
val_counts = pd.DataFrame(columns=['zipcode', 'count'], data=val_cou)
val_counts['zipcode'] = val_cou.index.astype(str)
val_counts['count'] = val_cou.values.astype(int)
val_counts<jupyter_output><empty_output><jupyter_text>**MERGING TOGETHER KINESSO VALUE COUNTS WITH KINESSO DATA***ONLY 19 ZIPCODES DO NOT HAVE CENSUS DATA*<jupyter_code>population_count = val_counts.merge(right=zip_codes, right_on='zipcode', left_on='zipcode', how='outer')
population_count_f = population_count.dropna()
#only 19 zipcodes without data
len(population_count[population_count['population'].isna()])<jupyter_output><empty_output><jupyter_text>Here count is the observed number from the Kinesso dataset and population is the expected number from census dataset<jupyter_code>population_count_f<jupyter_output><empty_output><jupyter_text>**CALCULATING DEVICE FREQUENCIES FOR EACH ZIPCODE**<jupyter_code>imp['count'] = [1] * len(imp)
device_model_make_counts = imp.groupby(['zip_code', 'device_make', 'device_model'], as_index=False).count()[['zip_code', 'device_make', 'device_model', 'count']]
total_calc = device_model_make_counts.groupby(['zip_code']).sum()

percent_calc = []
for i in device_model_make_counts.index:
    zipc = device_model_make_counts.iloc[i]['zip_code']
    percent_calc = np.append(percent_calc, device_model_make_counts.iloc[i]['count']/total_calc.loc[zipc])
device_model_make_counts['device % freq']= percent_calc *100
device_model_make_counts['combined'] = device_model_make_counts['device_make'] + ' ' + device_model_make_counts['device_model']
device_model_make_counts['zip_code'] = device_model_make_counts['zip_code'].astype(str).apply(add_zero)
device_model_make_counts<jupyter_output><empty_output><jupyter_text>**CALCULATING PERCENT DIFFERENCE BETWEEN EXPECTED AND OBSERVED POPULATIONS FOR EACH ZIPCODE**<jupyter_code>population_count_f['population % expected'] = (population_count_f['population']/sum(population_count_f['population']))*100
population_count_f['population % observed'] = (population_count_f['count']/sum(population_count_f['count']))*100
population_count_f['% difference'] = population_count_f['population % observed'] - population_count_f['population % expected']
population_count_f = population_count_f.rename(columns={'count':'observed population', 'population':'expected population'})
population_count_f<jupyter_output><ipython-input-46-cfa73adf08fd>:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  population_count_f['population % expected'] = (population_count_f['population']/sum(population_count_f['population']))*100
<ipython-input-46-cfa73adf08fd>:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  population_count_f['population % observed'] = (population_count_f['count']/sum(population_count_f['count']))*100
<ipython-input-46-cfa73adf08fd>:3: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from [...]<jupyter_text>*MERGING TOGETHER WITH DEVICE FREQUENCY DATA*<jupyter_code>combo = device_model_make_counts.merge(right=population_count_f, right_on='zipcode', left_on='zip_code', how='outer').drop(['count', 'device_make', 'device_model'], axis=1)
combined_impressions = combo.sort_values('% difference', ascending=False)

combined_impressions<jupyter_output><empty_output><jupyter_text>*GROUPING TO IDENTIFY MOST COMMONLY USED DEVICE*<jupyter_code>most_common_device = combined_impressions.groupby(['zip_code']).max()
most_common_device<jupyter_output><empty_output><jupyter_text>*IDENTIFYING MOST UNDER REPRESENTED ZIP CODES*<jupyter_code>underrepresented = most_common_device.sort_values('% difference').head(1000)
underrepresented.head(10)
underrepresented['combined'].value_counts()<jupyter_output><empty_output><jupyter_text>*IDENTIFYING MOST OVER REPRESENTED ZIP CODES*<jupyter_code>overrepresented = most_common_device.sort_values('% difference', ascending=False).head(1000)
overrepresented.head(10)
overrepresented['combined'].value_counts()<jupyter_output><empty_output><jupyter_text>**I actually decided not to look to closely into the device frequency numbers because for the underrepresented zipcodes there's only like 8-9 people Kinesso advertised to-- and mostly to Apple users interestingly. Instead I did some digging into the top 10 and bottom 10 in a seperate google docs titled: top 10 zipcode investigation** *quick summary: **over represented** zip codes belong to large urban cities with more industries and probably higher incomes but idk because I couldn't find zip code specific salary data**under represented:** zip codes belong to small cities with industries like coal, tourism, and power plants. Also I suspect lower incomes, but idk for sure<jupyter_code>sns.distplot(most_common_device['% difference'])<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12241296326006738, 'fraction_numerical': 0.011711856248997272, 'mean_word_length': 9.270181219110379, 'pattern_counts': {'":': 0, '<': 59, '<?xml version=': 0, '>': 59, 'https://': 3, 'lorem ipsum': 0, 'www.': 1, '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': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '668287', 'license': 'Apache-2.0', 'n_tokens_mistral': 2224, 'n_tokens_neox': 2096, 'n_words': 405, 'path': 'student-projects/fall-2020/Kinesso-AdShift-Diversifies-Marketing-Audiences/eda/[DEPRECATED] international_eda/germany/germany_eda.ipynb', 'repo_name': 'UCBerkeley-SCET/DataX-Berkeley', 'size': '6233'}