<jupyter_start><jupyter_text>Data descriptiontrain.csv - Train data.item_id - Ad id.user_id - User id.region - Ad region.city - Ad city.parent_category_name - Top level ad category as classified by Avito's ad model.category_name - Fine grain ad category as classified by Avito's ad model.param_1 - Optional parameter from Avito's ad model.param_2 - Optional parameter from Avito's ad model.param_3 - Optional parameter from Avito's ad model.title - Ad title.description - Ad description.price - Ad price.item_seq_number - Ad sequential number for user.activation_date- Date ad was placed.user_type - User type.image - Id code of image. Ties to a jpg file in train_jpg. Not every ad has an image.image_top_1 - Avito's classification code for the image.deal_probability - The target variable. This is the likelihood that an ad actually sold something. It's not possible to verify every transaction with certainty, so this column's value can be any float from zero to one.__notice__:        1. interpretation of param_1, _2, and _3?    2. price should be categorized with parent_category!        3. data_placed: mind the weekday or weekends of post data affect the dp!        4. what is the user_type?        5.   Data Explorationcombine the training and testing data into list to do manipulation the same time** discription data**full_des = [train_active, test_active]target column is the deal_probability<jupyter_code>train_dp = pd.read_csv(local_dir+'train.csv', parse_dates=["activation_date"])
test_dp= pd.read_csv(local_dir+'test.csv', parse_dates=["activation_date"])
full_dp = [train_dp,test_dp]
train_dp.head()<jupyter_output><empty_output><jupyter_text>print description info<jupyter_code># print percentage of NA value in each column
print ('Training Data')
train_summary = pd.DataFrame({'DataType': train_dp.dtypes,
                              'Non_Na':train_dp.notnull().sum(),
                              '% NULL':train_dp.isnull().mean(),
                              'Unique_values': train_dp.apply(lambda x: len(x.unique()))
                             })  

print (train_summary)
print ('*'*30)
print ('Testing Data')
test_summary = pd.DataFrame({'DataType': test_dp.dtypes,
                             'Not_Na':test_dp.notnull().sum(),
                              '%NULL':test_dp.isnull().mean(),
                              'Unique_values': test_dp.apply(lambda x: len(x.unique()))
                             })  
print (test_summary)<jupyter_output>Training Data
                        % NULL        DataType   Non_Na  Unique_values
item_id               0.000000          object  1503424        1503424
user_id               0.000000          object  1503424         771769
region                0.000000          object  1503424             28
city                  0.000000          object  1503424           1733
parent_category_name  0.000000          object  1503424              9
category_name         0.000000          object  1503424             47
param_1               0.040957          object  1441848            372
param_2               0.435368          object   848882            272
param_3               0.573734          object   640859           1220
title                 0.000000          object  1503424         788377
description           0.077341          object  1387148        1317103
price                 0.056778         float64  1418062          17007
item_seq_number       0.000000           int64  1503424        [...]<jupyter_text>Deal Probability__defination__: The target variable. This is the likelihood that an ad actually sold something. It's not possible to verify every transaction with certainty, so this column's value can be any float from zero to one.<jupyter_code>plt.figure(figsize=(12,8))

sns.distplot(train_dp['deal_probability'], bins =100, kde = False)
plt.xlabel('likelihood that an ad actually sold something', fontsize=12)
plt.title("Histogram of likelihood that an ad actually sold something")
plt.show() 
plt.figure(figsize = (12, 8))
plt.scatter(range(train_dp.shape[0]), np.sort(train_dp.deal_probability.values))
plt.xlabel('likelihood that an ad actually sold something', fontsize=12)
plt.title("Distribution of likelihood that an ad actually sold something")
plt.show()<jupyter_output>/home/zehai/.local/lib/python3.6/site-packages/matplotlib/axes/_axes.py:6462: UserWarning: The 'normed' kwarg is deprecated, and has been replaced by the 'density' kwarg.
  warnings.warn("The 'normed' kwarg is deprecated, and has been "<jupyter_text>It is interesting to view if there is full overlapping of training and testing data<jupyter_code>train_summary.index
joint_df = train_dp.append(test_dp)[test_dp.columns.tolist()]
joint_summary = pd.DataFrame({
                              'Unique_values': joint_df.apply(lambda x: len(x.unique()))
                             }) 
joint_summary['OverLapping_Data'] = train_summary['Unique_values']+test_summary['Unique_values']-joint_summary['Unique_values']
joint_summary['%_in_trainning']= joint_summary['OverLapping_Data']/train_summary['Unique_values']*100
joint_summary['%_in_test']= joint_summary['OverLapping_Data']/test_summary['Unique_values']*100
joint_summary.round(2)

train_user_id = train_dp['user_id'].tolist()
test_user_id = test_dp['user_id'].tolist()
shared_id = np.intersect1d(train_user_id,test_user_id)<jupyter_output><empty_output><jupyter_text>No overlapping of items but same users in training and testing group Cities not overlapping in training and test data Posted data are data from one month|  Observations    Catagorical Features    suitable catagorical features are:                                                  1. __region__,                           2. __parent_category_name__,                           3. __category_name__                           4. __user_type__       Overlapping ImageThe over lapping data is Na  1 Region dataThe translation data comes from [sban's kernel](https://www.kaggle.com/shivamb/in-depth-analysis-visualisations-avito/code)<jupyter_code># @hidden_cell
parent_category_name_map = {"Личные вещи" : "Personal belongings",
                            "Для дома и дачи" : "For the home and garden",
                            "Бытовая электроника" : "Consumer electronics",
                            "Недвижимость" : "Real estate",
                            "Хобби и отдых" : "Hobbies & leisure",
                            "Транспорт" : "Transport",
                            "Услуги" : "Services",
                            "Животные" : "Animals",
                            "Для бизнеса" : "For business"}

region_map = {"Свердловская область" : "Sverdlovsk oblast",
            "Самарская область" : "Samara oblast",
            "Ростовская область" : "Rostov oblast",
            "Татарстан" : "Tatarstan",
            "Волгоградская область" : "Volgograd oblast",
            "Нижегородская область" : "Nizhny Novgorod oblast",
            "Пермский край" : "Perm Krai",
            "Оренбургская область" : "Orenburg oblast",
            "Ханты-Мансийский АО" : "Khanty-Mansi Autonomous Okrug",
            "Тюменская область" : "Tyumen oblast",
            "Башкортостан" : "Bashkortostan",
            "Краснодарский край" : "Krasnodar Krai",
            "Новосибирская область" : "Novosibirsk oblast",
            "Омская область" : "Omsk oblast",
            "Белгородская область" : "Belgorod oblast",
            "Челябинская область" : "Chelyabinsk oblast",
            "Воронежская область" : "Voronezh oblast",
            "Кемеровская область" : "Kemerovo oblast",
            "Саратовская область" : "Saratov oblast",
            "Владимирская область" : "Vladimir oblast",
            "Калининградская область" : "Kaliningrad oblast",
            "Красноярский край" : "Krasnoyarsk Krai",
            "Ярославская область" : "Yaroslavl oblast",
            "Удмуртия" : "Udmurtia",
            "Алтайский край" : "Altai Krai",
            "Иркутская область" : "Irkutsk oblast",
            "Ставропольский край" : "Stavropol Krai",
            "Тульская область" : "Tula oblast"}


category_map = {"Одежда, обувь, аксессуары":"Clothing, shoes, accessories",
"Детская одежда и обувь":"Children's clothing and shoes",
"Товары для детей и игрушки":"Children's products and toys",
"Квартиры":"Apartments",
"Телефоны":"Phones",
"Мебель и интерьер":"Furniture and interior",
"Предложение услуг":"Offer services",
"Автомобили":"Cars",
"Ремонт и строительство":"Repair and construction",
"Бытовая техника":"Appliances",
"Товары для компьютера":"Products for computer",
"Дома, дачи, коттеджи":"Houses, villas, cottages",
"Красота и здоровье":"Health and beauty",
"Аудио и видео":"Audio and video",
"Спорт и отдых":"Sports and recreation",
"Коллекционирование":"Collecting",
"Оборудование для бизнеса":"Equipment for business",
"Земельные участки":"Land",
"Часы и украшения":"Watches and jewelry",
"Книги и журналы":"Books and magazines",
"Собаки":"Dogs",
"Игры, приставки и программы":"Games, consoles and software",
"Другие животные":"Other animals",
"Велосипеды":"Bikes",
"Ноутбуки":"Laptops",
"Кошки":"Cats",
"Грузовики и спецтехника":"Trucks and buses",
"Посуда и товары для кухни":"Tableware and goods for kitchen",
"Растения":"Plants",
"Планшеты и электронные книги":"Tablets and e-books",
"Товары для животных":"Pet products",
"Комнаты":"Room",
"Фототехника":"Photo",
"Коммерческая недвижимость":"Commercial property",
"Гаражи и машиноместа":"Garages and Parking spaces",
"Музыкальные инструменты":"Musical instruments",
"Оргтехника и расходники":"Office equipment and consumables",
"Птицы":"Birds",
"Продукты питания":"Food",
"Мотоциклы и мототехника":"Motorcycles and bikes",
"Настольные компьютеры":"Desktop computers",
"Аквариум":"Aquarium",
"Охота и рыбалка":"Hunting and fishing",
"Билеты и путешествия":"Tickets and travel",
"Водный транспорт":"Water transport",
"Готовый бизнес":"Ready business",
"Недвижимость за рубежом":"Property abroad"}
# map translation to the trainning data\
train_dp['region_en'] = train_dp['region'].apply(lambda x : region_map[x])
train_dp['parent_category_name_en'] = train_dp['parent_category_name'].apply(lambda x : parent_category_name_map[x])
train_dp['category_name_en'] = train_dp['category_name'].apply(lambda x : category_map[x])
# snap_shot of the training data
train_dp.head()
print(train_dp.image[100])<jupyter_output>2707593481ee0d272038e87763ca30bbea7f48939e45ab19571d9784e38ea52a<jupyter_text>The major challenge for this dataset is to make sence of Russian vocabulary- Either get translation API to translated in to English- Deal with synnonymous in Russian database<jupyter_code># how many category_name does test and train data possess
cata_group = train_dp[['item_id','parent_category_name_en']].groupby('parent_category_name_en')
cata_group.count().head()
g = plt.bar(cata_group.count().index,cata_group.count().item_id)
plt.xticks(rotation=90)
g = sns.factorplot(x="parent_category_name_en",y="deal_probability",data=train_dp,kind="bar", size = 5, aspect=2,
palette = "muted")
g.despine(left=True)
g.set_xticklabels(rotation=30)
g = g.set_ylabels("deal probability")
g = sns.factorplot(x="parent_category_name_en",y="price",data=train_dp,kind="bar", size = 5, aspect=2,
palette = "muted")
g.despine(left=True)
g.fig.get_axes()[0].set_yscale('log')
g.set_xticklabels(rotation=30)
g = g.set_ylabels("price (logscale)")
g = sns.boxplot(x="region_en",y="deal_probability",data=train_dp,palette = "muted")
plt.xticks(rotation='vertical')
g = sns.factorplot(x="user_type",y="deal_probability",data=train_dp,kind="bar", size = 5, aspect=1,
palette = "muted")
g.despine(left=True)
g = g.set_ylabels("deal probability")
# # date time features
# for data in full_dp:
#     data['weekday'] = data.activation_date.dt.weekday
#     data['month'] = data.activation_date.dt.month
#     data['day'] = data.activation_date.dt.day
#     data['week'] = data.activation_date.dt.week 
sns.set(rc={'figure.figsize':(15, 8)})
sns.boxplot(x="parent_category_name_en", y="deal_probability", hue="user_type",  palette="muted", data=train_dp)
plt.title("Deal probability by parent category and User Type")
plt.xticks(rotation=30)
plt.show()
train_dp.head()<jupyter_output><empty_output><jupyter_text>Deal with NA  1. Pricerescale the price by log and fill na with categorical mean<jupyter_code>for df in full_dp:
    df['price'] = np.log(df['price']+0.001)
g = sns.factorplot(x="parent_category_name_en",y="price",data=train_dp,kind="bar", size = 5, aspect=2,
palette = "muted")
g.despine(left=True)
g.set_xticklabels(rotation=30)
g = g.set_ylabels("log_price")
g = sns.factorplot(x="category_name_en",y="price",data=train_dp,kind="bar", size = 5, aspect=2,
palette = "muted")
g.despine(left=True)
g.set_xticklabels(rotation=90)
g = g.set_ylabels("log_price")
catagorical_meanPrice = train_dp.groupby('parent_category_name_en')['price'].mean()
catagorical_meanPrice['Personal belongings']
# fillna price with catagorical mean

test_dp = train_dp.iloc[:100]
na_test_dp = test_dp[test_dp['price'].isna()]
catagorical_meanPrice.index.tolist()
by_category = test_dp.groupby(['parent_category_name_en'])
def impute_median(series):
    return series.fillna(series.median())
test_dp.price = by_category.price.transform(impute_median)<jupyter_output>/home/zehai/.local/lib/python3.6/site-packages/pandas/core/generic.py:3643: 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: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  self[name] = value
{'fraction_non_alphanumeric': 0.11517635006585687, 'fraction_numerical': 0.02722084004097761, 'mean_word_length': 3.6470588235294117, 'pattern_counts': {'":': 47, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '441643', 'license': 'MIT', 'n_tokens_mistral': 4764, 'n_tokens_neox': 4657, 'n_words': 1047, 'path': 'notebook/.ipynb_checkpoints/EDA-checkpoint.ipynb', 'repo_name': 'wangz19/Avito_Demand_Prediction_Challenge', 'size': '13666'}