# -*- coding: utf-8 -*-
"""IYKRA Week3 -  EDA.ipynb

Automatically generated by Colaboratory.

Original file is located at
    https://colab.research.google.com/drive/1WmHvgwhHNsYtxrvJL9d46cMIIsrc0BS4
"""

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import math
sns.set()
df = pd.read_csv("Ritz_Jager_Data.csv")

"""## Quick Explore"""

df.head()

df.info()

df.isnull().sum()

"""## Missing Value Handling
Note:
- check the data type, if data type is numeric you have to check the skewness. If it's + or negative fill the missing value by median. If it's symmetry, fill the missing value by mean.
- If the data type is string or object, apply mode on that missing data
"""

# children float64
fig, ax = plt.subplots(figsize=(10,5))
sns.distplot(df['children'], bins=10)
plt.show()

df['children'].fillna(df['children'].median(), inplace=True)

# country_origin object
df['country_origin'].fillna(df['country_origin'].mode()[0], inplace=True)

# agent float
df['agent'] = df.agent.astype(str)
df['agent'].fillna(df['agent'].mode(), inplace=True)

# company float
print((df['company'].isnull().sum() / len(df) * 100), "%")
print("because the missing value more than 80% just drop the feature")

df.drop(['company'], axis=1, inplace=True)

df.isnull().sum()

"""## Answer Question"""

# 1. Where do the guests come from?
df['country_origin'].unique()

# 2. How much do guests pay for a room per night?
booked = df[(df['is_canceled'] != 1) & df['adr']>0]
pd.pivot_table(booked, index=['hotel_type','assigned_room_type'], values='adr', aggfunc='mean')

# 3. How does the price per night vary over the year?
booked = df[(df['is_canceled'] != 1) & df['adr']>0]
booked[['arrival_date_year','adr']].groupby(['arrival_date_year']).var()

# 4. Which are the busiest months?
booked = df[df['is_canceled'] == 0]
pd.pivot_table(booked, index=['arrival_date_year','arrival_date_month'], values='assigned_room_type', aggfunc='count')

# 5. How long do people stay at the hotels?
df_stay = df.copy()
df_stay ['stay_duration'] = df['stays_in_weekend_nights'] + df['stays_in_week_nights']

df_stay[df_stay['is_canceled']==0]['stay_duration'].mean()

# 6. Bookings by market segment
df[df['is_canceled'] == 0]['market_segment'].value_counts()

# 7. How many bookings were cancelled?
df['reservation_status'].value_counts()

df['reservation_status'].value_counts()[1]

# 8. Which month has the highest number of cancellations? 
booked = df[df['is_canceled'] == 1]
x = pd.pivot_table(booked, index=['arrival_date_year','arrival_date_month'], values='is_canceled', aggfunc='count')
x
# x.reindex(x['is_canceled'].sort_values(ascending=False).index)

df['hotel_type'].unique()

df['hotel_type'].value_counts()

missing_data = df.isnull().sum().reset_index()
missing_data.columns = ['variable','count_missing']
missing_data['filling (%)'] = 100 - (missing_data['count_missing']/df.shape[0]*100)
missing_data.sort_values(['filling (%)'])


32cedef9c1dab554baa1be75dd22474ee7d6b915
<mask token>

<mask token>
sns.set()
<mask token>
df.head()
df.info()
df.isnull().sum()
<mask token>
sns.distplot(df['children'], bins=10)
plt.show()
df['children'].fillna(df['children'].median(), inplace=True)
df['country_origin'].fillna(df['country_origin'].mode()[0], inplace=True)
<mask token>
df['agent'].fillna(df['agent'].mode(), inplace=True)
print(df['company'].isnull().sum() / len(df) * 100, '%')
print('because the missing value more than 80% just drop the feature')
df.drop(['company'], axis=1, inplace=True)
df.isnull().sum()
<mask token>
df['country_origin'].unique()
<mask token>
pd.pivot_table(booked, index=['hotel_type', 'assigned_room_type'], values=
    'adr', aggfunc='mean')
<mask token>
booked[['arrival_date_year', 'adr']].groupby(['arrival_date_year']).var()
<mask token>
pd.pivot_table(booked, index=['arrival_date_year', 'arrival_date_month'],
    values='assigned_room_type', aggfunc='count')
<mask token>
df_stay[df_stay['is_canceled'] == 0]['stay_duration'].mean()
df[df['is_canceled'] == 0]['market_segment'].value_counts()
df['reservation_status'].value_counts()
df['reservation_status'].value_counts()[1]
<mask token>
x
df['hotel_type'].unique()
df['hotel_type'].value_counts()
<mask token>
missing_data.sort_values(['filling (%)'])

<mask token>
sns.set()
df = pd.read_csv('Ritz_Jager_Data.csv')
<mask token>
df.head()
df.info()
df.isnull().sum()
<mask token>
fig, ax = plt.subplots(figsize=(10, 5))
sns.distplot(df['children'], bins=10)
plt.show()
df['children'].fillna(df['children'].median(), inplace=True)
df['country_origin'].fillna(df['country_origin'].mode()[0], inplace=True)
df['agent'] = df.agent.astype(str)
df['agent'].fillna(df['agent'].mode(), inplace=True)
print(df['company'].isnull().sum() / len(df) * 100, '%')
print('because the missing value more than 80% just drop the feature')
df.drop(['company'], axis=1, inplace=True)
df.isnull().sum()
<mask token>
df['country_origin'].unique()
booked = df[(df['is_canceled'] != 1) & df['adr'] > 0]
pd.pivot_table(booked, index=['hotel_type', 'assigned_room_type'], values=
    'adr', aggfunc='mean')
booked = df[(df['is_canceled'] != 1) & df['adr'] > 0]
booked[['arrival_date_year', 'adr']].groupby(['arrival_date_year']).var()
booked = df[df['is_canceled'] == 0]
pd.pivot_table(booked, index=['arrival_date_year', 'arrival_date_month'],
    values='assigned_room_type', aggfunc='count')
df_stay = df.copy()
df_stay['stay_duration'] = df['stays_in_weekend_nights'] + df[
    'stays_in_week_nights']
df_stay[df_stay['is_canceled'] == 0]['stay_duration'].mean()
df[df['is_canceled'] == 0]['market_segment'].value_counts()
df['reservation_status'].value_counts()
df['reservation_status'].value_counts()[1]
booked = df[df['is_canceled'] == 1]
x = pd.pivot_table(booked, index=['arrival_date_year', 'arrival_date_month'
    ], values='is_canceled', aggfunc='count')
x
df['hotel_type'].unique()
df['hotel_type'].value_counts()
missing_data = df.isnull().sum().reset_index()
missing_data.columns = ['variable', 'count_missing']
missing_data['filling (%)'] = 100 - missing_data['count_missing'] / df.shape[0
    ] * 100
missing_data.sort_values(['filling (%)'])

<mask token>
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import math
sns.set()
df = pd.read_csv('Ritz_Jager_Data.csv')
<mask token>
df.head()
df.info()
df.isnull().sum()
<mask token>
fig, ax = plt.subplots(figsize=(10, 5))
sns.distplot(df['children'], bins=10)
plt.show()
df['children'].fillna(df['children'].median(), inplace=True)
df['country_origin'].fillna(df['country_origin'].mode()[0], inplace=True)
df['agent'] = df.agent.astype(str)
df['agent'].fillna(df['agent'].mode(), inplace=True)
print(df['company'].isnull().sum() / len(df) * 100, '%')
print('because the missing value more than 80% just drop the feature')
df.drop(['company'], axis=1, inplace=True)
df.isnull().sum()
<mask token>
df['country_origin'].unique()
booked = df[(df['is_canceled'] != 1) & df['adr'] > 0]
pd.pivot_table(booked, index=['hotel_type', 'assigned_room_type'], values=
    'adr', aggfunc='mean')
booked = df[(df['is_canceled'] != 1) & df['adr'] > 0]
booked[['arrival_date_year', 'adr']].groupby(['arrival_date_year']).var()
booked = df[df['is_canceled'] == 0]
pd.pivot_table(booked, index=['arrival_date_year', 'arrival_date_month'],
    values='assigned_room_type', aggfunc='count')
df_stay = df.copy()
df_stay['stay_duration'] = df['stays_in_weekend_nights'] + df[
    'stays_in_week_nights']
df_stay[df_stay['is_canceled'] == 0]['stay_duration'].mean()
df[df['is_canceled'] == 0]['market_segment'].value_counts()
df['reservation_status'].value_counts()
df['reservation_status'].value_counts()[1]
booked = df[df['is_canceled'] == 1]
x = pd.pivot_table(booked, index=['arrival_date_year', 'arrival_date_month'
    ], values='is_canceled', aggfunc='count')
x
df['hotel_type'].unique()
df['hotel_type'].value_counts()
missing_data = df.isnull().sum().reset_index()
missing_data.columns = ['variable', 'count_missing']
missing_data['filling (%)'] = 100 - missing_data['count_missing'] / df.shape[0
    ] * 100
missing_data.sort_values(['filling (%)'])

# -*- coding: utf-8 -*-
"""IYKRA Week3 -  EDA.ipynb

Automatically generated by Colaboratory.

Original file is located at
    https://colab.research.google.com/drive/1WmHvgwhHNsYtxrvJL9d46cMIIsrc0BS4
"""

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import math
sns.set()
df = pd.read_csv("Ritz_Jager_Data.csv")

"""## Quick Explore"""

df.head()

df.info()

df.isnull().sum()

"""## Missing Value Handling
Note:
- check the data type, if data type is numeric you have to check the skewness. If it's + or negative fill the missing value by median. If it's symmetry, fill the missing value by mean.
- If the data type is string or object, apply mode on that missing data
"""

# children float64
fig, ax = plt.subplots(figsize=(10,5))
sns.distplot(df['children'], bins=10)
plt.show()

df['children'].fillna(df['children'].median(), inplace=True)

# country_origin object
df['country_origin'].fillna(df['country_origin'].mode()[0], inplace=True)

# agent float
df['agent'] = df.agent.astype(str)
df['agent'].fillna(df['agent'].mode(), inplace=True)

# company float
print((df['company'].isnull().sum() / len(df) * 100), "%")
print("because the missing value more than 80% just drop the feature")

df.drop(['company'], axis=1, inplace=True)

df.isnull().sum()

"""## Answer Question"""

# 1. Where do the guests come from?
df['country_origin'].unique()

# 2. How much do guests pay for a room per night?
booked = df[(df['is_canceled'] != 1) & df['adr']>0]
pd.pivot_table(booked, index=['hotel_type','assigned_room_type'], values='adr', aggfunc='mean')

# 3. How does the price per night vary over the year?
booked = df[(df['is_canceled'] != 1) & df['adr']>0]
booked[['arrival_date_year','adr']].groupby(['arrival_date_year']).var()

# 4. Which are the busiest months?
booked = df[df['is_canceled'] == 0]
pd.pivot_table(booked, index=['arrival_date_year','arrival_date_month'], values='assigned_room_type', aggfunc='count')

# 5. How long do people stay at the hotels?
df_stay = df.copy()
df_stay ['stay_duration'] = df['stays_in_weekend_nights'] + df['stays_in_week_nights']

df_stay[df_stay['is_canceled']==0]['stay_duration'].mean()

# 6. Bookings by market segment
df[df['is_canceled'] == 0]['market_segment'].value_counts()

# 7. How many bookings were cancelled?
df['reservation_status'].value_counts()

df['reservation_status'].value_counts()[1]

# 8. Which month has the highest number of cancellations? 
booked = df[df['is_canceled'] == 1]
x = pd.pivot_table(booked, index=['arrival_date_year','arrival_date_month'], values='is_canceled', aggfunc='count')
x
# x.reindex(x['is_canceled'].sort_values(ascending=False).index)

df['hotel_type'].unique()

df['hotel_type'].value_counts()

missing_data = df.isnull().sum().reset_index()
missing_data.columns = ['variable','count_missing']
missing_data['filling (%)'] = 100 - (missing_data['count_missing']/df.shape[0]*100)
missing_data.sort_values(['filling (%)'])

