#!/usr/bin/env python
# coding: utf-8

# In[1]:


import pandas as pd


# In[ ]:


flights=pd.read_csv("flights.csv") 


# In[ ]:


flights.shape


# In[ ]:


flights.head() #Prints first five rows of the dataset


# # Reading selected columns or rows from the dataframe

# In[ ]:


flights=pd.read_csv("flights.csv", usecols=[0,4]) #Reads only the 0th and 4th column


# In[ ]:


flights=pd.read_csv("flights.csv", nrows=3) #Reads only the first 3 rows of the dataframe
flights


# In[ ]:


flights.columns #Displays all the columns heading of the dataframe


# In[ ]:


flights.ORIGIN_AIRPORT  #Display a particular series(column) from the dataframe


# In[ ]:


flights.describe() #Gives descriptive statistics of numerical columns


# # Filter rows with filter(), query()

# In[ ]:


flights.query("MONTH==1 & DAY==1")


# In[ ]:


flights[(flights.MONTH==1)&(flights.DAY==1)]


# In[ ]:


flights.iloc[:9] #Displays first 9 rows of the dataset


# # Arrange rows with arrange(), sort()

# In[ ]:


flights.sort_values(['MONTH','DAY','DAY_OF_WEEK'])


# In[ ]:


#Sorts the particular column
flights.ARRIVAL_DELAY.sort_values()


# In[ ]:


#Sorts the whole dataframe according to the column
flights.sort_values('ARRIVAL_DELAY',ascending=False) #Sorts data in descending order of "ARRIVAL_DELAY"


# # Select columns with select (),[]

# In[ ]:


flights[['MONTH','DAY']]


# In[ ]:


flights.loc[:,'MONTH':'DAY_OF_WEEK'] #Display all rows from MONTH to DAY_OF_WEEK


# # Renaming columns

# In[ ]:


flights.rename(columns={'TAIL_NUMBER':'TAIL_NUM'}) #changes column name from "TAIL_NUMBER" to "TAIL_NUM"


# In[ ]:


flights.columns=flights.columns.str.replace('_',' ') #It replaces all the underscores in the columns names with spaces


# # Extract distinct (unique) rows

# In[ ]:


flights.TAIL_NUMBER.unique()


# In[ ]:


flights[['ORIGIN_AIRPORT','DESTINATION_AIRPORT']].drop_duplicates()


# # Add new columns

# In[ ]:


flights.assign(Travel_Time=flights.ARRIVAL_TIME - flights.DEPARTURE_TIME)


# In[ ]:


flights['Travel_Time']= flights.ARRIVAL_TIME - flights.DEPARTURE_TIME
flights.head()


# # Remove columns from Dataframe

# In[ ]:


flights.drop('Travel_Time',axis=1,inplace=True) #axis=1 to remove a column, axis=0 to remove a row


# In[ ]:


flights.head()


# # Use string methods in pandas

# In[ ]:


flights.AIRLINE.str.lower() #Converts the Airline column to lowercase


# In[ ]:


flights.DESTINATION_AIRPORT.str.contains('PBI')


# # Changing data type of column values

# In[6]:


orders = pd.read_table('http://bit.ly/chiporders')


# In[ ]:


orders.head()


# In[ ]:


orders.dtypes


# In[ ]:


orders.quantity.astype(float) #Changes data type from int to float


# In[ ]:


orders.item_price.str.replace('$','').astype(float).mean() 


# # Summarise values

# In[ ]:


flights.mean() #Calculates the mean of each columns


# # Randomly sample rows

# In[ ]:


flights.sample(n=10) #Randomly selects 10 rows from the dataset as a sample


# In[ ]:


flights.sample(frac=0.01) #Randomly selects a samplesize of 1% of the total dataset


# # Using groupby() in pandas

# In[15]:


drinks=pd.read_csv('http://bit.ly/drinksbycountry')


# In[ ]:


drinks.head()


# In[ ]:


drinks.groupby('continent').mean() #Calculates mean values for each continent


# In[ ]:


drinks.groupby('continent').beer_servings.agg(['count','max','min','mean']) #agg() uses various parameters at once


# In[ ]:


get_ipython().run_line_magic('matplotlib', 'inline')


# In[ ]:


drinks.groupby('continent').mean().plot(kind='bar') #Plots a bar chart


# # Different methods in pandas series

# In[ ]:


movies=pd.read_csv('http://bit.ly/imdbratings')


# In[ ]:


movies.head()


# In[ ]:


movies.genre.describe()


# In[ ]:


movies.genre.value_counts()


# In[ ]:


movies.genre.value_counts(normalize=True) #Displays as a % values


# In[ ]:


movies.genre.unique()


# In[ ]:


movies.genre.nunique()


# In[ ]:


pd.crosstab(movies.genre,movies.content_rating)


# In[ ]:


get_ipython().run_line_magic('matplotlib', 'inline')


# In[ ]:


movies.duration.plot(kind='hist') #plots a histogram


# In[ ]:


movies.genre.value_counts().plot(kind='bar') #plots a bar chart


# # Handling missing values in pandas

# In[ ]:


ufo=pd.read_csv('http://bit.ly/uforeports')


# In[ ]:


ufo.tail()


# In[ ]:


ufo.isnull().tail() #Shows True for NaN values


# In[ ]:


ufo.notnull().tail() #Does the opposite of isnull()


# In[ ]:


ufo.isnull().sum() #Calculates the total number of missing values in each column


# In[ ]:


ufo.dropna(how='any').shape #Drops rows if any of its entries is missing


# In[ ]:


ufo.dropna(how='all').shape #Drops rows only if all its entries are missing


# In[ ]:


ufo.dropna(subset=['City','Shape Reported'],how='any').shape #Checks missing values only in specific columns


# # Using index in the dataframe

# In[ ]:


drinks.head()


# In[ ]:


drinks.set_index('country',inplace=True) #Sets column 'country' as the index


# In[ ]:


drinks.head()


# In[ ]:


drinks.index.name=None #Removes name of the index heading


# In[ ]:


drinks.head()


# In[ ]:


drinks.index.name='country'


# In[ ]:


drinks.loc['Brazil','beer_servings']


# In[ ]:


drinks.reset_index(inplace=True) 


# In[ ]:


drinks.head()


# # Memory usage in pandas

# In[ ]:


drinks.info() #Shows memory usage of object references


# In[ ]:


drinks.info(memory_usage='deep') #Shows memory usage of the objects


# In[ ]:


drinks.memory_usage()


# In[ ]:


drinks.memory_usage(deep=True)


# In[ ]:


drinks['continent']=drinks.continent.astype('category') #Assigns integers to unique strings (Reducing memory usage)


# In[ ]:


drinks.continent.cat.codes.head()


# In[ ]:


drinks.memory_usage(deep=True)


# In[ ]:


df=pd.DataFrame({'ID':[100,101,102,103], 'quality':['Good', 'Very Good', 'Good', 'Excellent']})


# In[ ]:


df


# In[ ]:


df['quality']=df.quality.astype('category', categories=['Good', 'Very Good', 'Excellent'], ordered=True)
#Assigns integers in the order defined by 'categories' parameter


# In[ ]:


df.quality


# In[ ]:


df.sort_values('quality')


# In[ ]:


df[df.quality>'Good']


# # Using pandas with scikit-learn

# In[ ]:


train=pd.read_csv('http://bit.ly/kaggletrain') #Training data


# In[ ]:


train.head()


# In[ ]:


feature_cols=['Pclass','Parch'] #Selects two columns as features for training


# In[ ]:


x=train.loc[:,feature_cols] 
x.shape


# In[ ]:


y=train.Survived #Target values
y.shape


# In[ ]:


from sklearn.linear_model import LogisticRegression
logreg=LogisticRegression()
logreg.fit(x,y)


# In[ ]:


test=pd.read_csv('http://bit.ly/kaggletest') #Testing data
test.head()


# In[ ]:


x_new=test.loc[:,feature_cols]
x_new.shape


# In[ ]:


new_pred_class=logreg.predict(x_new)


# In[ ]:


new_pred_class


# In[ ]:


pd.DataFrame({'PassengerID':test.PassengerId,'Survived':new_pred_class})


# # Find and remove duplicate rows in pandas

# In[7]:


orders.head()


# In[10]:


orders.shape


# In[11]:


orders.duplicated().sum() #Counts the total number of duplicate rows in the dataframe


# In[17]:


orders.loc[orders.duplicated(),:] #Shows all the duplicate rows in the dataframe


# In[21]:


orders.loc[orders.duplicated(keep=False),:] #Shows duplicate and its original entry in the dataframe


# In[24]:


orders.drop_duplicates().shape #Removes duplicate rows


# In[25]:


orders.drop_duplicates(subset=['order_id','item_name']).shape #Removes duplicates entries in the these 2 columns only


# # Customising a Dataframe

# In[37]:


df=pd.DataFrame({'id':[100,101,102],'colour':['Red','Blue','Green']},columns=['colour','id'],index=[1,2,3]) #Create a dataframe using a dictionary
df


# In[41]:


df2=pd.DataFrame([[101,'Red'],[102,'Blue'],[103,'Green']],columns=['id','colour']).set_index('id')
df2 #Create a DataFrame using lists


# In[46]:


s=pd.Series(['square','circle','triangle'],name='shapes',index=[2,1,3])
s


# In[48]:


pd.concat([df,s],axis=1) #Joins the series as a column to the dataframe


# # Applying functions to pandas series and dataframe

# In[2]:


train=pd.read_csv('http://bit.ly/kaggletrain')


# In[3]:


train.head()


# In[6]:


train['sex_num']=train.Sex.map({'female':0,'male':1}) #Using 'map' method


# In[5]:


train.loc[0:4,['Sex','sex_num']]


# In[10]:


train['name_length']=train.Name.apply(len) #Calculates the length of string


# In[9]:


train.loc[0:5,['Name','name_length']]


# In[11]:


import numpy as np
train['fare_ceil']=train.Fare.apply(np.ceil)


# In[12]:


train.loc[0:4,['Fare','fare_ceil']]


# In[14]:


train.Name.str.split(',').apply(lambda x: x[0]).head() #Gets the first word of the string


# In[16]:


drinks.head()


# In[20]:


drinks.loc[:,'beer_servings':'wine_servings'].apply(max,axis=0) #Shows the max value under each column


# In[23]:


drinks.loc[:,'beer_servings':'wine_servings'].apply(np.argmax,axis=1) #argmax shows the column with the max value


# # Merging different dataframes

# In[2]:


left=pd.DataFrame({'subject':['history','science'],'marks':[10,20]})
left


# In[3]:


right=pd.DataFrame({'subject':['history','science','literature'],'books':[4,5,9]})
right


# In[5]:


pd.merge(left,right,on='subject',how='outer') #'outer' takes the biggest collective set of left and right. 'inner' takes the intersection of left and right.






