<jupyter_start><jupyter_text>Projekt IUM  Ogólne informacje Realizujący:* <NAME>* <NAME> <NAME>zymane:* Baza użytkowników ("users.json")* Katalog produktów ("delivery.json")* Historia sesji ("sessions.json")* Dostawy produktów ("deliveries.json")  Analiza danych<jupyter_code>import pandas as pd
import numpy as np
import itertools
import hashlib

# visualization
import matplotlib.pyplot as plt
import seaborn as sns

# place for sklearn<jupyter_output><empty_output><jupyter_text>Import danych<jupyter_code>users_data = pd.read_json("proper_data/users.json")
deliveries_data = pd.read_json("proper_data/deliveries.json")
events_data = pd.read_json("proper_data/sessions.json")
products_data = pd.read_json("proper_data/products.json")


# new data
new_users_data = pd.read_json("proper_data/users2.json")
new_deliveries_data = pd.read_json("proper_data/deliveries2.json")
new_events_data = pd.read_json("proper_data/sessions2.json")
new_products_data = pd.read_json("proper_data/products2.json")<jupyter_output><empty_output><jupyter_text>Users Atrybuty:* **user_id** - id użytkownika* **name** - imię użytkownika; atrybut tekstowy* **city** - nazwa miasta, z którego pochodzi użytkownik; atrybut tekstowy* **street** - adres użytkownika w tym mieście; atrybut tekstowy<jupyter_code>print(users_data.head())

print()
print(new_users_data.head())
users_data.info()

new_users_data.info()
city_counts = pd.DataFrame(users_data.city
                           .value_counts()
                           .reset_index()
                           .rename(columns={"city":"count_users", "index": "city"})).sort_values('city')
city_counts
sns.barplot(x=city_counts['city'], y=city_counts['count_users'])<jupyter_output><empty_output><jupyter_text>Nie mamy braków wartości  Products Atrybuty:* **product_name** - nazwa produktu wraz z marką i modelem* **category_path** - ścieżka kategorii produktu w postaci breadcrumbs; atrybut tekstowy* **price** - całkowita wartość produktu w jednostkach pieniężnych; atrybut liczbowy ciągły<jupyter_code>products_data.head()
products_data.info()
# Sprawdzanie poprawności pod względem ceny

bad_products_data = products_data[(products_data.price <= 0) | (products_data.price > 1000000)]
cleaned_products = products_data.drop(bad_products_data.index)

print(f"Cleaned data is {round(100 * len(cleaned_products) / len(products_data), 3)}% of the total")

products_data = cleaned_products
# rozkład cen produktów
sns.histplot(products_data.price)<jupyter_output><empty_output><jupyter_text>Nie mamy braków wartości  Eventy<jupyter_code>events_data.info()
events_data['day'] = events_data.timestamp.dt.date
# Poprawa nulli user_id
print(events_data.session_id.nunique())

events = []
i = 0
for event in events_data.iterrows():
    if i % 100 == 0:
        print(i)
    i += 1
    
    if np.isnan(event[1]['user_id']):
        helping_df = events_data[events_data['session_id'] == event[1]['session_id']]
        for helping_event in helping_df.iterrows():
            if not np.isnan(helping_event[1]['user_id']):
                event[1]['user_id'] = helping_event[1]['user_id']
                break
    events.append(event[1])
    

events_data = pd.DataFrame(events)
events_data.info()

# TODO: Some users may be absent mutually!
# Add a dictionary for used ids
# wyrzucenie nieobecnych userów i produkty
bad_events_users = events_data[events_data['user_id'].isnull()]
bad_events_products = events_data[events_data['product_id'].isnull()]

bad_events = pd.concat([bad_events_users, bad_events_products]).drop_duplicates()

events_data = events_data.drop(bad_events.index)

events_data
# since we have only BUYING and VIEWING sessions, why not to split it to 2 dataframes

buying_events = events_data[events_data['event_type'] == "BUY_PRODUCT"]
viewing_events = events_data[events_data['event_type'] == "VIEW_PRODUCT"]

viewing_events = viewing_events.drop('purchase_id', axis=1)
buying_events
viewing_events
# sanity check here
print("The records with null values in the user_id: ", len(events_data[events_data['user_id'].isnull()]))
def get_min_max_timestamp(series):
    
    print("Minimum timestamp: ", series.min())
    print("Maximum timestamp: ", series.max())

    
get_min_max_timestamp(events_data.timestamp)

events_data['day'] = pd.to_datetime(events_data.timestamp.dt.date)
buying_sessions = events_data[events_data['event_type'] == 'BUY_PRODUCT']

events_data.day.hist()
buying_sessions.day.hist()

events_data<jupyter_output>Minimum timestamp:  2020-03-15 09:17:22
Maximum timestamp:  2021-03-15 08:20:57<jupyter_text>Wysyłki<jupyter_code>deliveries_data['purchase_timestamp'] = pd.to_datetime(deliveries_data.purchase_timestamp)
deliveries_data['delivery_timestamp'] = pd.to_datetime(deliveries_data.delivery_timestamp)
deliveries_data
print("Unique purchasing identifiers:\t", deliveries_data.purchase_id.nunique())
print("All purchases:\t\t\t", len(deliveries_data))
# how much data is correct?
deliveries_data['is_successful'] = [1 if elem else 0 for elem in deliveries_data['delivery_timestamp']]
print(f"Delivered packs: {round(100 * sum(deliveries_data.is_successful) / len(deliveries_data), 3)} %.")
get_min_max_timestamp(deliveries_data.purchase_timestamp)
print()
get_min_max_timestamp(deliveries_data[deliveries_data['is_successful'] == 1]['delivery_timestamp'])<jupyter_output>Minimum timestamp:  2020-03-15 09:30:47
Maximum timestamp:  2021-03-15 07:12:27

Minimum timestamp:  2020-03-18 16:22:58
Maximum timestamp:  2021-03-20 06:24:30<jupyter_text>What company is responsible for pack leekage?<jupyter_code>deliveries_data[deliveries_data['is_successful'] == 0].delivery_company.value_counts()<jupyter_output><empty_output><jupyter_text>Przygotowanie danych treningowych<jupyter_code>events_data['year'] = events_data.timestamp.dt.year
events_data['month'] = events_data.timestamp.dt.month


years = list(events_data.year.unique())
months = list(events_data.month.unique())
user_ids = list(users_data.user_id.unique())

print(years)
print(months)
print(user_ids)


triplets = []
for triplet in itertools.product(years, months, user_ids):
    triplets.append(triplet)
    
processed_data = pd.DataFrame(triplets, columns=['year', 'month', 'user_id'])
# processed_data = processed_data.sort_values(['year', 'month', 'user_id'])

processed_data<jupyter_output>[2021, 2020]
[2, 3, 12, 1, 11, 5, 7, 10, 9, 4, 6, 8]
[102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 199, 200, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 235, 236, 237, 238, 239, 240, 241, 242, 243, 244, 245, 246, 247, 248, 249, 250, 251, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261, 262, 263, 264, 265, 266, 267, 268, 269, 270, 271, 272, 273, 274, 275, 276, 277, 278, 279, 280, 281, 282, 283, 284, 285, 286, 287, 288, 289, 290, 2[...]<jupyter_text>Atrybuty wejsciowe<jupyter_code># total number of web events
web_events = events_data.groupby(['user_id', 'year', 'month']).count().reset_index()[['session_id', "year", "month", "user_id"]].rename(columns={"session_id": "total_web_events"})
processed_data = pd.merge(processed_data, web_events, how='left', left_on=['year', 'month', 'user_id'], right_on=['year', 'month', 'user_id'])

# the city to deliver to
processed_data = pd.merge(users_data[['user_id', 'city']], processed_data, on='user_id', how="outer")
processed_data

# number of buying events

buying_events['year'] = buying_events.timestamp.dt.year
buying_events['month'] = buying_events.timestamp.dt.month

# buying_events
buying = buying_events.groupby(['user_id', 'year', 'month']).count().reset_index()[['session_id', 'year', 'month', 'user_id']].rename(columns={"session_id": "buying_events"})
processed_data = pd.merge(processed_data, buying, how='left', left_on=['year', 'month', 'user_id'], right_on=['year', 'month', 'user_id'])
processed_data
# TODO: Merge buying events with processed_data

# num of dropped packs
deliveries_data.head()
deliveries_data['days_coming'] = (deliveries_data.delivery_timestamp - deliveries_data.purchase_timestamp)
deliveries_data['days_coming'] = [tdo.days for tdo in deliveries_data['days_coming']]

deliveries_data.info()

buying_with_delivery = pd.merge(deliveries_data, buying_events, on='purchase_id', how='inner')
buying_with_delivery['is_dropped'] = [int(not bool(elem)) for elem in buying_with_delivery['is_successful']]
dropped_packs = buying_with_delivery.groupby(["user_id", "year", "month"])['is_dropped'].sum().reset_index().rename(columns={'is_dropped': 'num_dropped_monthly'})

processed_data = pd.merge(processed_data, dropped_packs, how='left', left_on=['year', 'month', 'user_id'], right_on=['year', 'month', 'user_id'])
processed_data

# number of money spent in that month
boughts_with_prices = pd.merge(buying_events, products_data, on="product_id", how="inner")
boughts_with_prices = boughts_with_prices.rename(columns={"price": "total_price"})

boughts_with_prices['sum_after_discount'] = boughts_with_prices['total_price'] * (1 - boughts_with_prices['offered_discount'] / 100)
boughts_with_prices

sum_spend_per_month = boughts_with_prices.groupby(['user_id', 'year', 'month'])['sum_after_discount'].sum().reset_index().rename(columns={"sum_after_discounts":"total_monthly_sum"})
processed_data = pd.merge(processed_data, sum_spend_per_month, how='left', left_on=['year', 'month', 'user_id'], right_on=['year', 'month', 'user_id'])
processed_data
twenty_valid = (processed_data['year'] == 2020) & (processed_data['month'] >= 2)
twenty_one_valid = (processed_data['year'] == 2021) & (processed_data['month'] <= 3)

processed_data = processed_data[twenty_valid | twenty_one_valid]
processed_data.total_web_events.fillna(0, inplace=True)
processed_data.buying_events.fillna(0, inplace=True)
processed_data.num_dropped_monthly.fillna(0, inplace=True)
processed_data.sum_after_discount.fillna(0, inplace=True)
processed_data<jupyter_output><empty_output><jupyter_text>Zmienna docelowa<jupyter_code>valid_sums = []
i = 0
for row in processed_data.iterrows():
    
    if i % 100 == 0:
        print(i)
    i += 1
    
    row_data = dict(row[1])

    mask_user = (sum_spend_per_month['user_id'] == row_data['user_id'])
    mask_year = (sum_spend_per_month['year'] == row_data['year'])

    if row_data['month'] == 12:
        mask_year = [sum_spend_per_month['year'] == row_data['year'] + 1]


    mask_month = (sum_spend_per_month['month'] == row_data['month'] % 12 + 1)

    try:
        next_months_sum = list(sum_spend_per_month[mask_month & mask_year & mask_user]['sum_after_discount'])[0]
    except:
        next_months_sum = np.nan
    
    valid_sums.append(next_months_sum)
    
processed_data['next_months_sum'] = valid_sums
processed_data
processed_data.to_csv('processed_data.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14443952148870182, 'fraction_numerical': 0.06601683650863979, 'mean_word_length': 6.594885598923284, 'pattern_counts': {'":': 6, '<': 43, '<?xml version=': 0, '>': 43, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 21, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '657352', 'license': 'MIT', 'n_tokens_mistral': 4831, 'n_tokens_neox': 4094, 'n_words': 809, 'path': 'EDA.ipynb', 'repo_name': 'amasiukevich/IUM_shop', 'size': '11308'}