<jupyter_start><jupyter_text>Load Dataset<jupyter_code>def parse_json(data):
    """ function to parse json data """
    session_id = []
    timestamp = []
    cities = []
    user_id = []
    joining_date = []
    country = []
    
    # parse each item
    for item in data:
        session_id.append(item['session_id'][0])
        timestamp.append(item['unix_timestamp'][0])
        cities.append(item['cities'][0])
        user_id.append(item['user'][0][0]['user_id'])
        joining_date.append(item['user'][0][0]['joining_date'])
        country.append(item['user'][0][0]['country'])
    
    # create DataFrame
    maps = {'session_id': session_id, 'timestamp': timestamp, 'cities': cities, 
            'user_id': user_id, 'joining_date': joining_date, 'country': country}
    columns=['session_id', 'timestamp', 'cities', 'user_id', 'joining_date', 'country']
    
    return pd.DataFrame(maps, columns=columns)
with open('./data/city_search.json', 'r') as f:
    json_data = json.load(f)

data = parse_json(json_data)
data['joining_date'] = pd.to_datetime(data['joining_date'])
data.head()
data.info()
print('Unique session:\t', len(data['session_id'].unique()))
print('Unique users \t', len(data['user_id'].unique()))<jupyter_output>Unique session:	 20022
Unique users 	 5777<jupyter_text>Questions  Question 1There was a bug in the code and one country didn't get logged. It just shows up as an empty field (""). Can you guess which country was that? How?<jupyter_code># check the value country
data['country'].value_counts().reset_index().rename(columns={'index': 'country', 'country': 'count'})<jupyter_output><empty_output><jupyter_text>Given the current dataset, since there are little information about the dataset, one information that could be used to guess the country is the search time. Since people in different countries will search in similar local time, we can use this information to guess the country.<jupyter_code># extract search time information
data['time'] = pd.to_datetime(data['timestamp'], unit='s')
data['hour'] = data['time'].apply(lambda x: x.hour)
# visualization
country_list = ['US', 'DE', 'UK', '', 'FR', 'ES', 'IT']
order = list(range(0, 23))
fig, ax = plt.subplots(nrows=7, ncols=1, figsize=(18, 12))
for i in range(len(country_list)):
    sns.countplot(x='hour', data=data[data['country'] == country_list[i]], ax=ax[i], order=order)
    ax[i].set_ylabel(country_list[i], fontsize=12)
plt.tight_layout()
plt.show()<jupyter_output><empty_output><jupyter_text>Based on above figure, we can see that, for the unknown country, their peak search time has around 12 hours difference with United States, and around 5 hours difference with Europe. Based on the geographic locations of each country, it seems that this missing country is in `Asia`. Possible countries include `China` and `Japan`.   Question 2For each city, find the most likely city to be also searched for within the same session.<jupyter_code>data.head()<jupyter_output><empty_output><jupyter_text>For this question, we will create a matrix that calculate the city-city similarity.<jupyter_code># find unique cities
city_pair = data['cities'].apply(lambda x: x.split(', '))

index = 0
maps = {}
city_list = []
for item in city_pair:
    for city in item:
        if city not in maps:
            maps[city] = index
            index += 1
            city_list.append(city)
            
print('Unique cities:\t', len(city_list))
# calculate matrix similarity
pair_matrix = np.zeros((89, 89))

for item in city_pair:
    if len(item) == 1:
        continue
    for i in range(len(item) - 1):
        index1 = maps[item[i]]
        for j in range(i + 1, len(item)):
            index2 = maps[item[j]]
            pair_matrix[index1, index2] += 1
            pair_matrix[index2, index1] += 1
# create dataframe
df_similarity = pd.DataFrame(pair_matrix, index=city_list, columns=city_list)
df_similarity.head()
# visualization
fig, ax = plt.subplots(figsize=(10, 8))
sns.heatmap(df_similarity, ax=ax)
ax.axis('image')
plt.tight_layout()
plt.show()
# get result
result = df_similarity.apply(np.argmax, axis=0).reset_index()
result = result.rename(columns={'index': 'city', 0: 'most similar'})
result<jupyter_output><empty_output><jupyter_text>There is another version of solution that use user instead of session to calculate the similarity. Reference: [Link](https://github.com/stasi009/TakeHomeDataChallenges/blob/master/13.CitySimilarity/city_search.ipynb)  Question 3Travel sites are browsed by two kinds of users. Users who are actually planning a trip and users who just dream about a vacation. The first ones have obviously a much higher purchasing intent. Users planning a trip often search for cities close to each other, while users who search for cities far away from each other are often just dreaming about a vacation. That is, a user searching for LA, SF and Las Vegas in the same session is much more likely to book a hotel than a user searching for NY, Paris, Kuala Lumpur (makes sense, right?). Based on this idea, come up with an algorithm that clusters sessions into two groups: high intent and low intent. Explain all assumptions you make along the way. For this question, one way is to actually calculate the geographic distance for actual searched cities. However, this solution is not very practical for this small project. Another choice is to calculate the distance based on the city-city similarity in Question 2.<jupyter_code>def search_distance(cities):
    cities = cities.split(', ')
    
    if len(cities) == 1:
        return 0  # user only search one city, distance 0
    
    distance = 0
    for i in range(len(cities) - 1):
        city1 = cities[i]
        for j in range(i + 1, len(cities)):
            city2 = cities[j]
            distance += df_similarity.loc[city1, city2]
            
    return distance / len(cities)

# calculate distance
data['distance'] = data['cities'].apply(search_distance)
# visualization
warnings.filterwarnings('ignore')
fig, ax = plt.subplots(figsize=(8, 5))
sns.distplot(data['distance'], ax=ax, bins=50)
plt.tight_layout()
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1205604431410883, 'fraction_numerical': 0.012544802867383513, 'mean_word_length': 4.33362293657689, 'pattern_counts': {'":': 0, '<': 24, '<?xml version=': 0, '>': 24, 'https://': 1, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '345937', 'license': 'MIT', 'n_tokens_mistral': 1914, 'n_tokens_neox': 1843, 'n_words': 619, 'path': '13. Json City Similarities.ipynb', 'repo_name': 'sarasun97/DS-Take-Home', 'size': '6138'}