# This Python 3 environment comes with many helpful analytics libraries installed
# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python
# For example, here's several helpful packages to load

import numpy as np # linear algebra
import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)

# Input data files are available in the read-only "../input/" directory
# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory

import os
for dirname, _, filenames in os.walk('/kaggle/input'):
    for filename in filenames:
        print(os.path.join(dirname, filename))

# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using "Save & Run All" 
# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session
# importing libraries
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
# loading datasets
customers=pd.read_csv('/kaggle/input/brazilian-ecommerce/olist_customers_dataset.csv')
sellers=pd.read_csv('/kaggle/input/brazilian-ecommerce/olist_sellers_dataset.csv')
order_reviews=pd.read_csv('/kaggle/input/brazilian-ecommerce/olist_order_reviews_dataset.csv')
order_items=pd.read_csv('/kaggle/input/brazilian-ecommerce/olist_order_items_dataset.csv')
products=pd.read_csv('/kaggle/input/brazilian-ecommerce/olist_products_dataset.csv')
geolocation = pd.read_csv('/kaggle/input/brazilian-ecommerce/olist_geolocation_dataset.csv')
product_category = pd.read_csv('/kaggle/input/brazilian-ecommerce/product_category_name_translation.csv')
orders = pd.read_csv('/kaggle/input/brazilian-ecommerce/olist_orders_dataset.csv')
payments = pd.read_csv('/kaggle/input/brazilian-ecommerce/olist_order_payments_dataset.csv')
# storing all datasets as a list for future reference

list_of_all_datasets=['customers','sellers','order_reviews','order_items','products','geolocation','product_category','orders','payments']
"""
### Analysis of Customers Data
"""
# print top 5 values
customers.head(5)
customers.shape
customers.isnull().sum()
"""
#### We can see that there is no null value in Customers dataset
#### Now, we will try to find no of unique states and city from the Customers Dataset
"""
customers.customer_city.unique()
len(customers.customer_city.unique())
"""
#### There are total 4119 different Cities from where Customers visit
"""
customers.customer_state.unique()
customers.customer_state
len(customers.customer_state.unique())
len(customers.customer_state)
"""
There are total 27 different states
"""
"""
### Visualisation of Customer Dataset
"""
plt.figure(figsize=(13,7))
sns.histplot(x=customers['customer_state'],data=customers,color='blue')
plt.show()
"""
#### From above histogram, we can see that most numbers of customer are from SP (Sao Paul) state,followed by Rj (Rio de Janeiro) state.
"""
top_10_cities=customers['customer_city'].value_counts().nlargest(10)
top_10_cities
cities=customers['customer_city'].value_counts(ascending=True)
cities[0:20]
"""
#### We can see that there are so many cites with count 1, they can be said to be city with least customers
"""
city_df=customers.groupby('customer_city').count()['customer_id'].reset_index()
sns.barplot(data = city_df.sort_values('customer_id', ascending = False).nlargest(10,'customer_id'), x = 'customer_id', 
            y = 'customer_city',)
plt.title('Cities with the Most Customers')
plt.xlabel('City')
plt.ylabel('Number of Customers')
"""
##### Again the city with most no of customers is Sao Paulo, followed by rio de janeiro
"""
"""
### Analyis of products and items
"""
products.head()
order_items.head()
"""
#### From above two datasets, we can see that we can 'inner join' these datasets on products id, Lets see
"""
products_and_order_items_df=pd.merge(order_items,products)
products_and_order_items_df.head()
top_10_products=products_and_order_items_df['product_category_name'].value_counts().reset_index().nlargest(10, 'product_category_name')
lowest_10_products=products_and_order_items_df['product_category_name'].value_counts().reset_index().nsmallest(10, 'product_category_name')
top_10_products
lowest_10_products
"""
### Payments Analysis
"""
payments.head()
payments.payment_type.unique()
"""
#### There are bascially 5 types of payment methods used by the customers
"""
top_payment_type = payments['payment_type'].value_counts(ascending=False)
top_payment_type
"""
#### Out of 5 types of methods, credit card is used on the top, then boleto and then voucher
"""
type(top_payment_type)
"""
#### Since this is a series object we can draw a histplot using its index and values
"""
a = top_payment_type.index
b = top_payment_type.values
sns.barplot(x=a,y=b)
"""
##### From the above bar graph we can see that,uses of Credit Card is the highest aroud 75000, then boleto that is slightly less than 20000, 
"""
"""
### Products Reviews
"""
# we will first see if there is any relation between our product_and_order_items_df, and order_reviews
products_and_order_items_df.head(3)
# printing top 3 rows or order_reviews
order_reviews.head(3)
"""
#### We can join it based on order_id column which is common to both, for this we will make another dataframe named "reviews_df'
"""
reviews_df=pd.merge(products_and_order_items_df, order_reviews)
reviews_df.head(3)
reviews_df.shape
order_reviews['review_score'].value_counts()
order_reviews['review_score'].unique()
"""
#### Ratings points are discrete, starting from 1 to 5.
"""
sns.barplot(x=order_reviews['review_score'].value_counts().index,y=order_reviews['review_score'].value_counts().values)
plt.xlabel('Ratings')
plt.ylabel('Counts')
"""
Most of the products have been rated 5,then 4.
also 1 rating is higher than 2 and 3

"""
"""
### Top Ten rated products
"""
reviews_df.head(3)
product_reviews_mean = reviews_df.groupby('product_category_name').mean()['review_score'].reset_index()
product_reviews_mean.head(3)
top_10_ratings = product_reviews_mean.sort_values('review_score', ascending = False).nlargest(10,'review_score')
lowest_10_ratings = product_reviews_mean.sort_values('review_score', ascending = False).nsmallest(10,'review_score')
sns.barplot(data = top_10_ratings, x = 'review_score', y = 'product_category_name')
plt.title('Top 10 Product Ratings')
plt.xlabel('Average Rating')
plt.ylabel('Product Category Name')
"""
#### Music, dvd, and cds category have the highest average ratings. after tha infant's fashion clothes come.
"""
sns.barplot(data = lowest_10_ratings, x = 'review_score', y = 'product_category_name')
plt.title('Lowest 10 Product Ratings')
plt.xlabel('Average Rating')
plt.ylabel('Product Category Name')
"""
#### Insurance Services have the worst ratings, followed by fraldas higiene products
"""
# list print again list of all datasets
list_of_all_datasets
geolocation.head()
geolocation.isnull().sum()
len(geolocation.geolocation_city.unique())
"""
#### There are 8011 unique city from geolocation data.
"""