<jupyter_start><jupyter_text>Version 1.0.3  Pandas basics  Hi! In this programming assignment you need to refresh your `pandas` knowledge. You will need to do several [`groupby`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html)s and [`join`]()`s to solve the task.<jupyter_code>import pandas as pd
import numpy as np
import os
import matplotlib.pyplot as plt
%matplotlib inline 

DATA_FOLDER = '../data/'

transactions    = pd.read_csv(os.path.join(os.getcwd(), DATA_FOLDER, 'sales_train_v2.csv'))
items           = pd.read_csv(os.path.join(os.getcwd(), DATA_FOLDER, 'items.csv'))
item_categories = pd.read_csv(os.path.join(os.getcwd(), DATA_FOLDER, 'item_categories.csv'))
shops           = pd.read_csv(os.path.join(os.getcwd(), DATA_FOLDER, 'shops.csv'))<jupyter_output><empty_output><jupyter_text>The dataset we are going to use is taken from the competition, that serves as the final project for this course. You can find complete data description at the [competition web page](https://www.kaggle.com/c/competitive-data-science-final-project/data). To join the competition use [this link](https://www.kaggle.com/t/1ea93815dca248e99221df42ebde3540).  Grading We will create a grader instace below and use it to collect your answers. When function `submit_tag` is called, grader will store your answer *locally*. The answers will *not* be submited to the platform immediately so you can call `submit_tag` function as many times as you need. When you are ready to push your answers to the platform you should fill your credentials and run `submit` function in the last paragraph  of the assignment.  Task Let's start with a simple task.   Print the shape of the loaded dataframes and use [`df.head`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.head.html) function to print several rows. Examine the features you are given.<jupyter_code>items.head()
# YOUR CODE GOES HERE
item_categories.head()
transactions.head()<jupyter_output><empty_output><jupyter_text>Now use your `pandas` skills to get answers for the following questions. The first question is:1. ** What was the maximum total revenue among all the shops in September, 2014?** * Hereinafter *revenue* refers to total sales minus value of goods returned.*Hints:** Sometimes items are returned, find such examples in the dataset. * It is handy to split `date` field into [`day`, `month`, `year`] components and use `df.year == 14` and `df.month == 9` in order to select target subset of dates.* You may work with `date` feature as with strings, or you may first convert it to `pd.datetime` type with `pd.to_datetime` function, but do not forget to set correct `format` argument.<jupyter_code>transactions['month'] = transactions.date.apply(lambda x: x.split(".")[1])
transactions['day'] = transactions.date.apply(lambda x: x.split(".")[0])
transactions['year'] = transactions.date.apply(lambda x: x.split(".")[2])
transactions['day_revenue'] = transactions.item_price * transactions.item_cnt_day
transactions.head()
sept_revenue = transactions[(transactions.month == "09") & (transactions.year == "2014")].groupby(transactions.shop_id).day_revenue.sum()
sept_revenue_df = sept_revenue.to_frame().reset_index()
max_revenue = sept_revenue_df.loc[sept_revenue_df.day_revenue.idxmax()].day_revenue<jupyter_output><empty_output><jupyter_text>Great! Let's move on and answer another question:  What item category generated the highest revenue in summer 2014?* Submit `id` of the category found.    * Here we call "summer" the period from June to August.*Hints:** Note, that for an object `x` of type `pd.Series`: `x.argmax()` returns **index** of the maximum element. `pd.Series` can have non-trivial index (not `[1, 2, 3, ... ]`).<jupyter_code>summer_trans = transactions[(transactions.month >= "06") & (transactions.month <= "08") & (transactions.year == "2014")]
summer_trans_cat = pd.merge(summer_trans, items, 'left')
summer_trans_cat.groupby(summer_trans_cat.item_category_id).day_revenue.sum().argmax()
category_id_with_max_revenue = summer_trans_cat.groupby(summer_trans_cat.item_category_id).day_revenue.sum().argmax()<jupyter_output><empty_output><jupyter_text>How many items are there, such that their price stays constant (to the best of our knowledge) during the whole period of time?* Let's assume, that the items are returned for the same price as they had been sold.<jupyter_code>price_changed = transactions.groupby(transactions.item_id).item_price.unique().apply(len).to_frame().reset_index()
len(price_changed[price_changed.item_price == 1])
num_items_constant_price = len(price_changed[price_changed.item_price == 1])<jupyter_output><empty_output><jupyter_text>Remember, the data can sometimes be noisy.   What was the variance of the number of sold items per day sequence for the shop with `shop_id = 25` in December, 2014? Do not count the items, that were sold but returned back later.* Fill `total_num_items_sold` and `days` arrays, and plot the sequence with the code below.* Then compute variance. Remember, there can be differences in how you normalize variance (biased or unbiased estimate, see [link](https://math.stackexchange.com/questions/496627/the-difference-between-unbiased-biased-estimator-variance)). Compute ***unbiased*** estimate (use the right value for `ddof` argument in `pd.var` or `np.var`). * If there were no sales at a given day, ***do not*** impute missing value with zero, just ignore that day<jupyter_code>shop_id = 25
shop_december = transactions[(transactions.month == "12") & (transactions.year == "2014") & (transactions.shop_id == shop_id)]
shop_december
december_sold = shop_december.groupby(shop_december.day).item_cnt_day.sum()
total_num_items_sold = december_sold.values
days = december_sold.keys()

# Plot it
plt.plot(days, total_num_items_sold)
plt.ylabel('Num items')
plt.xlabel('Day')
plt.title("Daily revenue for shop_id = 25")
plt.show()

total_num_items_sold_var = np.var(total_num_items_sold, ddof=1)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.114214463840399, 'fraction_numerical': 0.013466334164588529, 'mean_word_length': 5.867579908675799, 'pattern_counts': {'":': 0, '<': 26, '<?xml version=': 0, '>': 26, 'https://': 5, 'lorem ipsum': 0, 'www.': 2, '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': '281172', 'license': 'MIT', 'n_tokens_mistral': 1946, 'n_tokens_neox': 1850, 'n_words': 592, 'path': 'src/PandasBasics.ipynb', 'repo_name': 'federicoBetti/DataScienceCompetition_FutureSales', 'size': '6015'}