<jupyter_start><jupyter_text>[Basic Data Exploration](basic-explor)[Bivariate analysis](bivar)<jupyter_code>pwd<jupyter_output><empty_output><jupyter_text>MODULE -1  -- Intro to pandas and basic concepts of data frames  DATA INPUT AND OUTPUT USING PANDASCSVEXCELHTMLSQLWhen you install Jupyter via anaconda distribution, all the below packages come in by defaulyWhen installing Jupyter outside anaconda distribution, you will need to use the following install commandsLibraries that need to be installed with pip or condaconda install sqlalchemyconda install lxmlconda install html5libconda install BeautifulSoup4to read any file, type read_ and click tab Data frames are 2d data structures .Data is aligned in a tabular format consists of rows and columnsDataFrames can hold multiple data typesDataFrames Constructor in pandas ispd.DataFrames(data=,index=,dtype=,copy)data holds the data sourceindex holds the index to access the data sourcedtype is type of the datacopy is self to false default. take a copy<jupyter_code>import numpy as np
import pandas as pd
import random 
from numpy.random import randn

random.randint(25,50)
 #### we get the same set of random numbers
### initializes the random number generator. If you want to generate the same number 
### every time you need to pass the same seed value every time
## https://pynative.com/python-random-seed/

print('random number with seed 101')
random.seed(400) 

print("first--",random.randint(25,50))     ### will generate a random number

### to generate same random number as previous
random.seed(101)
print("second--",random.randint(25,50))     ### will generate a random number


### to generate same random number as previous
random.seed(101)
print("third--",random.randint(25,50))     ### will generate a random number

x= randn(5,4)
#### creating a data frame using random number generator randn,use shift + tab to understand more about data frame parameters

df0 = pd.DataFrame(data=randn(5,4),index=['A','B','C','D','E'],columns= ['W','X','Y','Z'])
df0

#### each of the column W,X,Y,Z is each a pandas series sharing a common index
#### creating a data frame from a list
df_list = [['a1','b1','c1'],['a2','b2','c2'],['a3','b3','c3']]
df_list

#### to create user defined index, let us create a list of index
df_index = ['indx1','indx2','indx3']


### create dataframe from list
#data_Frame_list = pd.DataFrame(data=df_list)

data_Frame_list = pd.DataFrame(data=df_list,index=df_index,columns=('ColA','ColB','Colc'))
data_Frame_list
#### creating a dataframe from dictionary
#### let us create a new diction
df_dict = {'k1':(1,2,3),'k2':(20,30,40),'k3':(30,40,50),'k4':[100,200,300],'k5':[100,200,300]}


### to make INDEXES FOR DATAFRAME
#data_Frame_dict = pd.DataFrame(data=df_dict,index=('I1','I2','I3','I4'),columns=('ColA','ColB','ColC','ColD'))
#data_Frame_dict


### to make INDEXES FOR DATAFRAME
#data_Frame_dict = pd.DataFrame(data=df_dict,index=('k1','k2','k3','k4'),columns=('ColA','ColB','ColC','ColD'))
#data_Frame_dict
### default creation
data_Frame_dict = pd.DataFrame(data=df_dict)
print(data_Frame_dict)
df00= pd.DataFrame({'col1':[1,2,3,4,5],'col2':[444,555,333,120,111],'col3':['abc','cde','def','efg','ghi']})
print(type(df00))
df00.head()                          ### returns the first n rows
df00.tail()
df00
df00['col2'].unique()
### get information on unique values
df00['col2'].unique()                   ### gives me unique values in col2

### to get just the number of unique values in a column
df00['col2'].nunique()

### other way
### len(df['col2'].unique())
df00['col2'].value_counts()        ### gives the number of counts each value was occuring = frequency table in r<jupyter_output><empty_output><jupyter_text>Every column in a data frame is a series by itself series is formed out of numpy arraysdataframe --> within dataframe there is series ==> within series you have numpy arrays  Changing data types<jupyter_code>temp = pd.DataFrame({"A":["1","2","3"],"B":[11,12,13],"C": ["12-06-2012","13-06-2015","15-06-2017"]})
temp         ### not the keys in dictionary becomes columns and valuees become rows
temp.dtypes
temp["C"] = pd.to_datetime(temp["C"])

temp["A"] = pd.to_numeric(temp["A"])
temp.dtypes

temp["A"] = temp["A"].astype(str)
temp.dtypes
temp["D"] = pd.to_numeric(temp["A"])
temp<jupyter_output><empty_output><jupyter_text>Creating Data Frames by reading files<jupyter_code>## WE WILL USE THE FILE Uber Drives
## Pandas Libraries to be installed 
import pandas as pd
import numpy as np
# read the data in pandas

df = pd.read_csv("Uber Drives 2016.csv")<jupyter_output><empty_output><jupyter_text>Key Modules1. Basic Dataframe operations2. Common data manipulation tasks3. Loops and Functions  Basic dataframe operations<jupyter_code>df.head()
type(df)
### to return a random sample
#df2= df.sample(n=100000,replace=True)
df.info()
### Shape of data frame
df.shape 

## 1156 observations and 7 columns
### size of dataframe # a multiple of rows and columns
df.size
### datatypes of columns in a dataframe
df.dtypes
### tells whether object has attribute with given name
hasattr(df,"head")
callable(getattr(df,"head"))
hasattr(df,"tail")
callable(getattr(df,"tail"))
df.tail()<jupyter_output><empty_output><jupyter_text>convert data to datetime format  check this out pd.to_datetime(df["START_DATE*"],format='%m/%d/%Y %H:%M') Available time formats: https://docs.python.org/3/library/datetime.htmlstrftime-and-strptime-behavior  Dataset summarization methods  The Describe Method -  is used to view some basic statistical details like percentile, mean, std etc. of a data frame or a series of numeric values. When this method is applied to a series of string, it returns a different output- mean is the average and is the "expected" value of the distribution. On average, you'd expect to get this number.- std tells you how big of swings the data takes relative to the mean. Specifically, its the mean squared deviation from the mean.- 50% is also the median and marks the mid point of data in a column. This means half the scores are greater than or equal  to this value of median and half are less- 25% - lower quartile - 25% of scores fall below lower quartile-  75% - upper quartile - 75% of scores fall below the upper quartile value  - min - minimum value- max - maximum value- count the larger this number, the more credibility all the stats have.<jupyter_code>df.info()
df.describe()  ### will give summary statistics for numeric /quantitative variables only 
print(df['CATEGORY*'].value_counts()) ### gives the count of each value in CATEGORY

df['MILES*'].value_counts().head()
df.describe(include="all") ### will give summary statistics for all variables - 5 Point Summary

### NaN is missing values
### for quantitative all summary stats are shown
### for categorical only top,freq unique values are shown
### missing values are reported too<jupyter_output><empty_output><jupyter_text>interpretation of describetop - gives the top occuring value in a given categorical column  (mode)freq  gives the frequency of times that a particular value occurs (example here "cary" occurs 201 times as start pointunique = gives the unique occurrence of top occuring value "cary" 177 timesmean - mean value of the columnmin - min value of columnstd - standard deviation of column25% - 25th percentile value...meaning 75<jupyter_code>### describe for individual columns
df["START_DATE*"].describe()
### describe for select group of columns
df[["START_DATE*","END_DATE*"]].describe()
# count of unique start locations
df["START*"].value_counts().head()
### gives  unique values in START*
df["START*"].unique()
#NUMBER OF  unique value in START*
df["START*"].nunique()
### count of each value in START*

df["START*"].value_counts().head()
#### median of a dataframe
#### gives the median of the numeric columns MILES
import statistics
df.median()         
### column   median -  adds up all the values in a given column and finds the mid point. same as df.median
df.median(axis=0)
### median of a specific column
#df['MILES*'].median()
df.loc[:,"MILES*"].median()
### mode  
df.mode()<jupyter_output><empty_output><jupyter_text>---  Common data manipulation tasks  1. Renaming Columns<jupyter_code>dfnew = pd.read_csv("Uber Drives 2016.csv")
### renaming column names in dataframe
dfnew.rename(columns={'START_DATE*' : 'start_date',
                    'END_DATE*'  : 'end_date',
                     'CATEGORY*' : 'category',
                     'START*'    : 'start',
                     'STOP*'     : 'stop',
                     'MILES*'    :  'miles',
                     'PURPOSE*'   :  'purpose' }, 
                 inplace=False).head()
### renames columns with the first 3 chars of each column
dfnew.rename(columns=lambda x: x[0:3], inplace=False).head()

##How To Change and Row Names/Indexes in Pandas?
dfnew.rename(index={0:'zero',1:'one'}, inplace=False).head()

##How To Change Column Names and Row Indexes Simultaneously in Pandas?
dfnew.rename(columns={'START_DATE*' : 'start_date',
                    'END_DATE*'  : 'end_date',
                     'CATEGORY*' : 'category',
                     'START*'    : 'start',
                     'STOP*'     : 'stop',
                     'MILES*'    :  'miles',
                     'PURPOSE*'   :  'purpose'}, 
                 index={0:'zero',1:'one'}, 
                 inplace=False).head(3)<jupyter_output><empty_output><jupyter_text>Common data manipulation tasks 2. Select /Indexing 5 verbs of data manipulation1. Selecting/Indexing2. Filtering3. Sorting4. Mutating/ Conditionally adding columns5. Groupby/Summarize<jupyter_code>### retreiving /accessing/grabbing/extracting data from a dataframe
### Permanently renaming columns
dfnew.rename(columns={'START_DATE*' : 'start_date',
                    'END_DATE*'  : 'end_date',
                     'CATEGORY*' : 'category',
                     'START*'    : 'start',
                     'STOP*'     : 'stop',
                     'MILES*'    :  'miles',
                     'PURPOSE*'   :  'purpose'}, 
                 index={0:'zero',1:'one'}, 
                 inplace=True)
dfnew.head()<jupyter_output><empty_output><jupyter_text>Selecting Columns from a data frame<jupyter_code>#### To retrieve a column  
dfnew['start_date'].head()
#### To retrieve multiple columns ..note the twin square brackets
dfnew[['start_date','end_date','miles']].head()
#### to retrieve information from data frame

###
print(type(dfnew))

### extracting top and bottom 5 rows of a column START_DATE* from dataframe df 
print(dfnew['start_date'].head())
print('/n')
print(dfnew['start_date'].tail())


#### note that the output is a series with user defined indexes and also its uni dimensional

### to verify whether every column in a data frame is a series or not
type(dfnew['start_date'])
### alternate way to retrieve data from df. THIS IS NOT RECOMMENDED 
### because using '.' can get confused with 
#### using methods

dfnew.head()
### 2 or more columns twin sq brackets are enough
dfnew[['start_date','end_date','category']].head()

#### Double bracket notation is enough to extract more than 1 column<jupyter_output><empty_output><jupyter_text>conditional selection<jupyter_code>## retreive all rows where miles is greater than 4
dfnew_cond = dfnew['miles'] > 4
dfnew_cond.head(10)


### smart coding - #### using bracket notation, we can get only the rows where this cond is satisfied
### grab only rows of col miles where its > 4
dfnew[dfnew['miles']>4].head()<jupyter_output><empty_output><jupyter_text>Conditionally adding columnsFeature Engineering<jupyter_code>### if we want to add a column called miles category with 2 categories as Long Trip and Short Trip
import numpy as np
dfnew["miles_cat"] = np.where(dfnew["miles"] > 5,"Long Trip","Short Trip")
dfnew.head()
dfnew["miles_cat"].value_counts()
dfnew.loc[dfnew["miles"] > 5].shape<jupyter_output><empty_output><jupyter_text>Filtering<jupyter_code>### all rows with miles greater than 10
df2 = dfnew.loc[dfnew["miles"] > 10,["miles"]]
df2
df3 = df2.iloc[0:3]
df3
# find out all rides greater than 10 miles
dfnew.loc[dfnew["miles"] > 10].head()
# Find out all rides to Cary
#dfnew[dfnew['start']=="Cary",dfnew["miles"]].head()

dfnew[(dfnew['start']=="Cary") & (dfnew['miles'] > 4)][['start','category','start_date']] 
dfnew.loc[dfnew['start']=='Cary','miles'].head()
#dfnew.loc[['zero','one',2],['start','stop','miles']]
# Find out all rides to Cary & Morrisville          
dfnew[dfnew["start"].isin(["Cary","Morrisville"])]
 # Find out all rides to Cary & Morrisville          
dfnew[dfnew["start"].isin(["Cary","Morrisville"])].shape
# Find out all rides to Cary & Morrisville          
dfnew.loc[dfnew["start"].isin(["Cary","Morrisville"])].head()
dfnew.loc[~dfnew["start"].isin(["Cary","Morrisville"])].head()
dfnew.describe()
dfnew[dfnew_cond].head(10)

### multiple conditions using and 
#### using bracket notation, we can get only the rows where this cond is satisfied
### grab only rows of col miles where its >2 and < 4
dfnew_cond1 = (dfnew['miles']>2) & (dfnew['miles']<4)
print(dfnew[dfnew_cond1].head())

### Alternate way of smart coding
print(dfnew[(dfnew['miles']>2) & (dfnew['miles']<4)].head(5))

dfnew_cond2 = (df['miles']>2) | (df['miles']<4)
dfnew_cond2.head()
dfnew[dfnew_cond2].head()<jupyter_output><empty_output><jupyter_text>Extracting data from a resultset of<jupyter_code>dfnew_cond2 = dfnew[(dfnew['miles']>2) & (dfnew['miles']<4)]
print(dfnew_cond2.head())
##### extracting data from the above resultset - eg extract column X from result set
### watch the double square brackets after condition
dfnew[(dfnew['miles']>2) & (dfnew['miles']<4)][['start','category','start_date']].head()<jupyter_output><empty_output><jupyter_text>Column addition and deletion - Feature Engineering<jupyter_code>#### creating a new column  - helps in feature creation
### df['new']   returns error that new column is not found

#### correct way of creating a new column or feature

dfnew['new_col1']  = dfnew['end_date'] + dfnew['start_date']
dfnew['new_col2'] = dfnew['miles'] * 2
dfnew.head()

##mylistdf = [10000,20000,3000,49999,590000]

##mylistdf

### you see new_col and new_Col1
### try printing it again and you  see the new columns
dfnew.head()
## TO DROP A COLUMN
dfnew.drop('new_col1')
print(dfnew)

### this will give an error
### default axis for columns is axis 1, rows is axis 0
dfnew.drop('new_col1',axis=1,inplace=False)
### this will drop the column but not permanently
### inplace = True needs to be given to permanently drop the column. default inplace is False. we can still see the column new_col1
dfnew.columns

### to permanently drop
dfnew.drop('new_col1',axis=1,inplace=True)

### check validate
dfnew.columns<jupyter_output><empty_output><jupyter_text>ROW SELECTION<jupyter_code>dfnew.head()
#### selecting rows, by using .loc method. .loc is followed by [] and not ()
### prints the entire row with index 2
print(dfnew.loc['zero'])  ### first row
print('/n)')
print('/n)')
print(dfnew.loc[2])     #### third row
 
### ALTERNATE WAY OF EXTRACTING ROWS FROM A DATA FRAME
#### index location based look up
 
print(dfnew.iloc[0])
print('\n')
print(dfnew.iloc[2])
dfnew.head()
#### to extract subset of rows and columns - example row zero and Column start - '<NAME>'
dfnew.loc['zero','start']   
#### SUBSETTING -Example 2 - extract values in row zero and one, columns start and stop
### df1.loc[[row indexes], [column indexes]]

dfnew.loc[['zero','one'],['start','stop']]
dfnew.loc[['zero','one',2],['start','stop','miles']]
### extracting rows using iloc and slice notation - slice notations apply..go up to a row, but not include that row index
dfnew.iloc[0:2]
### extracting rows using iloc and slice notation - slice notations apply..go up to a row, but not include that row index
dfnew.iloc[0:2][['start','stop','miles']]
## extracts rows with index 0 and 1 and columns 0 and 3
dfnew.iloc[0:2,[0,3]]<jupyter_output><empty_output><jupyter_text>Sorting<jupyter_code>dfnew.sort_values(by=['miles'],ascending=False).head(5)
dfnew.sort_values(by=['start', 'miles'],ascending=[True,False])<jupyter_output><empty_output><jupyter_text>INDEX MANIPULATION IN DATA FRAMES<jupyter_code>### Permanently renaming columns
dfnew1 = pd.read_csv("Uber Drives 2016.csv")
dfnew1.rename(columns={'START_DATE*' : 'start_date',
                    'END_DATE*'  : 'end_date',
                     'CATEGORY*' : 'category',
                     'START*'    : 'start',
                     'STOP*'     : 'stop',
                     'MILES*'    :  'miles',
                     'PURPOSE*'   :  'purpose'}, 
                 index={0:'zero',1:'one'}, 
                 inplace=True)
dfnew1.head(10)
### INDEX MANIPULATION
## resetting indexes
#### resets index, but not inplace reset
dfnew1.reset_index(drop=True,inplace=False).head() ### will reset the index permanently


### NOTE - User defined index is showing as another column "index"
### drop will drop the user defined column as index
### inplace=True will permanently reset
dfnew1.head()
dfnew1["my_indx"] = dfnew['miles']
dfnew1.head()
### set the index of dataframe to column States
dfnew1.set_index('my_indx',inplace=False).head()

#### adding a new column to data frame 
### set that column as index

newstring = 'CA NY NJ OH CO'
newx = newstring.split()
newx
df1['States'] = newx
df1
df1.set_index('States')

### column states has now become index
df1
### note that States is not index because inplace=False
#### difference between reset and set index is
#### reset index resets to new index and makes the old index as a column in the data frame
#### set index completely overrides the old index
### note both reset and set will become permanent ONLY when using INPLACE argument<jupyter_output><empty_output><jupyter_text>GROUP BY IN PANDAS ![image.png](attachment:image.png)  Groupby Summarize<jupyter_code>dfnew.head(10)
# for each start loc find the mean distance travelled
dfnew.groupby("start").agg("mean").head()
# for each start loc find the mean & the total dist travelled
dfx=dfnew.groupby("start").agg(["mean","sum"]).head()
dfx.columns
import numpy as np
import pandas as pd
sales_data = {'Company':['GooG','GooG','MSFT','MSFT','FB','FB'],
              'Person' :['<NAME>','<NAME>','Amy','Andy','Charlie','Karla'],
              'Sales'  :[200,400,600,1000,1200,10000]}
### create a data frame using dictionary sales_data
sales_df = pd.DataFrame(data=sales_data)
sales_df
#### using Group By Method. group by "company" column

grpbycomp = sales_df.groupby('Company')
grpbycomp
print(type(grpbycomp))

### note that the output is a group by object
### mean of sales by company or average sales for each company
grpbycomp.mean()

### for eg mean sales of facebook is (1200+10000)/2) = 5600 
### outputs a data frame with index called company and sales
### this allows us to further process the data

### to find total sales per company
grpbycomp.sum()
#### to get the overall sales of facebook
grpbycomp.sum().loc['FB']
### smart coding 
sales_df.groupby('Company').sum().loc['FB']

### smart coding 
res= sales_df.groupby('Company').sum().loc[['FB','GooG']]
print(res)
#### to get the overall sales of Google
grpbycomp.sum().loc['GooG']

### smart coding 
df.groupby('Company').sum().loc['GooG']
### other ways of writing expression with groupby
#### groupby (column). aggregatefunction
df.groupby('Company').sum().loc['FB']
df.groupby('Company').sum().loc['GooG']
df
### to count the number of persons in each company

df.groupby('Company').count()
### count of rows in Sales column for every company
df.groupby('Company').count()['Sales']
### Max Sales in every company

df.groupby('Company').max()
df.groupby('Company').min()
#### group by with describe method, gives min,max, mean , count, std deviation, \
### 1st quartile, 2nd quartile, 3rd quartile.
### All your summary statistics will be displayed by describe

sales_df.groupby('Company').describe()
### describe on the whole data frame
df.describe()
#### use transpose method on describe method output to transpose rows and columns
sales_df.groupby('Company').describe().transpose()
### describe for every column
df.describe()['Sales']
### for loop to iterate through a data frame columns
for key, value in df100.iteritems():
    value +=1                        ## value = value+1
    print(key,value)
df200 = pd.DataFrame(np.random.rand(5,4),columns=['col1','col2','col3','col4'])
df200
### for loop to iterate through a data frame rows

for key, value in df200.iterrows():
    value= value+100
    print(key,value)

df200[['col1','col2']]<jupyter_output><empty_output><jupyter_text>Merging, Concatenating, Joining<jupyter_code>import pandas as pd
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],
                        'B': ['B0', 'B1', 'B2', 'B3'],
                        'C': ['C0', 'C1', 'C2', 'C3'],
                        'D': ['D0', 'D1', 'D2', 'D3']},
                        index=[0, 1, 2, 3])
df2 = pd.DataFrame({'A': ['A4', 'A5', 'A6', 'A7'],
                        'B': ['B4', 'B5', 'B6', 'B7'],
                        'C': ['C4', 'C5', 'C6', 'C7'],
                        'D': ['D4', 'D5', 'D6', 'D7']},
                         index=[4, 5, 6, 7]) 
df3 = pd.DataFrame({'A': ['A8', 'A9', 'A10', 'A11'],
                        'B': ['B8', 'B9', 'B10', 'B11'],
                        'C': ['C8', 'C9', 'C10', 'C11'],
                        'D': ['D8', 'D9', 'D10', 'D11']},
                        index=[8, 9, 10, 11])
df1
df2
df3<jupyter_output><empty_output><jupyter_text>ConcatenationConcatenation basically glues together DataFrames. Keep in mind that dimensions should match along the axis you are concatenating on. You can use **pd.concat** and pass in a list of DataFrames to concatenate together:<jupyter_code>### the axis of concatenation is rows - because by default axis=0 represents rows. so concatenation happens on rows
pd.concat([df1,df2,df3])
pd.concat([df1,df2,df3],axis=1)<jupyter_output><empty_output><jupyter_text>_____     Example DataFrames - dictionary<jupyter_code>left = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                     'A': ['A0', 'A1', 'A2', 'A3'],
                     'B': ['B0', 'B1', 'B2', 'B3']})
   
right = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                      'C': ['C0', 'C1', 'C2', 'C3'],
                      'D': ['D0', 'D1', 'D2', 'D3']})    
left
right
pd.concat([left,right])
pd.concat([left,right],axis=1)
left
right<jupyter_output><empty_output><jupyter_text>___  MergingThe **merge** function allows you to merge DataFrames together using a similar logic as merging SQL Tables together. For example:<jupyter_code>print(left)
print(right)
pd.merge(left,right,how='inner',on='key')<jupyter_output><empty_output><jupyter_text>Or to show a more complicated example:<jupyter_code>left1 = pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],
                     'key2': ['K0', 'K1', 'K0', 'K1'],
                        'A': ['A0', 'A1', 'A2', 'A3'],
                        'B': ['B0', 'B1', 'B2', 'B3']})
    
right1 = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],
                      'key2': ['K0', 'K0', 'K0', 'K0'],
                         'C': ['C0', 'C1', 'C2', 'C3'],
                         'D': ['D0', 'D1', 'D2', 'D3']})
print(left1)
print(right1)

pd.merge(left1, right1, on=['key1', 'key2'])
print(left)
print(right)
print(left1)
print(right1)
pd.merge(left1, right1, how='outer', on=['key1', 'key2'])   ### similar to sql full outer join
print(left1)
print(right1)
pd.merge(left1, right1, how='right', on=['key1', 'key2'])        ### similar to sql right outer join
pd.merge(left1, right1, how='left', on=['key1', 'key2'])<jupyter_output><empty_output><jupyter_text>JoiningJoining is a convenient method for combining the columns of two potentially differently-indexed DataFrames into a single result DataFrame.<jupyter_code>df1 =pd.DataFrame({'A': ['A0', 'A1', 'A2'],
                     'B': ['B0', 'B1', 'B2']},
                      index=['K0', 'K1', 'K2']) 

df2= pd.DataFrame({'C': ['C0', 'C2', 'C3'],
                    'D': ['D0', 'D2', 'D3']},
                      index=['K0', 'K2', 'K3'])
print(df1)
print(df2)
df1.join(df2)
df2.join(df1)
left.join(right, how='outer')<jupyter_output><empty_output><jupyter_text>Treating missing values 1) using dropna() , remove all the elements with missing values. elements here can be elements of an array, row of a file etc..2) Impute missing values with meaningful valid value. Imputation means replacement of missing values with valid values   - what meaningful values     - in case of a numeric variable or column, missing values are replaced by average value of all the elements in the column                  A                  1                  2                  3                  Nan            imputation value = mean(A)                   5                       - in case of a numeric variable or column, missing values are replaced by median value of all the elements in the column                  A                  1                  2                  3                  Nan            imputation value = median(A)                   5                       - - in case of a numeric variable or column, missing values are replaced by mode value of all the elements in the column                  A                  1                  2                  3                  Nan            imputation value = mode(A)                   5                           - in case of a categorical variable or column, missing values are replaced by mode - frequent occuring value in the column                  A                  cat1                  cat1                  cat1                  Nan            imputation value = cat1                  cat5<jupyter_code>dfnew.head()<jupyter_output><empty_output><jupyter_text><jupyter_code>## Count missing values
dfnew.isnull().sum()<jupyter_output><empty_output><jupyter_text>fill missing values with most common/frequently occurring<jupyter_code># Fill NA with most common values
dfnew["purpose"].value_counts()
dfnew = dfnew.fillna({"purpose":"Meeting"})
dfnew["purpose"].value_counts()
### removing missing values in rows
dfnew.dropna(axis=0,inplace=False).head()

### removing missing values in columns
dfnew.dropna(axis=1,inplace=False).head()
#### row 1 has 3 Non NaN values (no missing values)
#### row 2 has 2 Non NaN values 
### row 3 has 1 Non NaN value 
#### we can set the threshold to retain Row1 and drop only row 2

dfnew.dropna(axis=0,thresh=2,inplace=False)

### observe second row is retained becausee it had 2 NON NaN values<jupyter_output><empty_output><jupyter_text>Imputing Missing Values<jupyter_code>##### replace missing values using value parameter
### one way to fill missing values is by finding the mean of that column and replacing missing valuees
### with that mean.
### note below that df['A'].mean() helps in getting the mean of column A
#df3['A'].fillna(value=dfnew['miles'].mean())


#### DATA INPUT OUTPUT USING EXCEL,HTML,SQL<jupyter_output><empty_output><jupyter_text>DATA INPUT AND OUTPUT USING PANDASCSVEXCELHTMLSQLWhen you install Jupyter via anaconda distribution, all the below packages come in by defaulyWhen installing Jupyter outside anaconda distribution, you will need to use the following install commandsLibraries that need to be installed with pip or condaconda install sqlalchemyconda install lxmlconda install html5libconda install BeautifulSoup4to read any file, type read_ and click tab<jupyter_code>import pandas as pd
import numpy as np
### reading excel file with .xls or .xlsx extension
df_weightloss = pd.read_excel('DietWeightLoss.xls')
### use tab to understand diffrent files that can be read using read
## pd.read_
df_weightloss<jupyter_output><empty_output><jupyter_text>WRITE INTO A FILE FROM A DATAFRAME<jupyter_code>### writing to an excel file 
## weightloss.to_excel('new_diet_data.xls')
df_weightloss.to_excel('feb2_weightloss.xls') 

### to check whether the new file got created - read the file
pd.read_excel('feb2_weightloss.xls')
df_weightloss.to_csv('feb2_weightloss.csv')

newdietdf = pd.read_excel('new_diet_weightloss.xls')
print(type(newdietdf))

## to get the top 5 records of your data frame 
newdietdf.head()
### to get a specific number of records from data frame
newdietdf.head(20)
### to get the bottom 5 records from the dataframe
newdietdf.tail()
### to get the specific number of records from the bottom of the file
newdietdf.tail(20)
df.head()
#### writing into a file from dataframe
##### click tab key after df.to to get all options
### below creates a My_Ouput.csv file
#### reason for index = False is index also gets saved as a column 

df_housing = pd.read_csv("housing_prices.csv")
df_housing.head()
df_housing.info()
df_housing.to_csv('df_housing_out',sep=',')
dfhousingout=pd.read_csv('df_housing_out')  #### note the column Unnamed which also stores index
dfhousingout.head()  #### note the column Unnamed which also stores index
df_housing.to_csv('df_housing_out_new',index=False)

pd.read_csv('df_housing_out_new').head()
pd.read_csv('My_Output1')  
#pd.read_csv('My_Output1')  #### note the column Unnamed which also stores index
df.to_csv('My_Output2',index=False)
pd.read_csv('My_Output2').head()<jupyter_output><empty_output><jupyter_text>READING AND WRITING FROM EXCEL FILES      PANDAS CAN ONLY IMPORT DATA.      DOES NOT IMPORT FORMULAS OR MACROS      TRYING TO READ EXCEL FILE WITH FORMULAS MAY CAUSE PANDAS TO CRASH      WE MAY BE ASKED TO INSTALL XLRD LIBRARY(IDEALLY INSTALLED AUTOMATICALLY WITH ANACONDA       Distribution)      IF ERROR         conda install xlrd<jupyter_code>#### to read an excel file, pass file name and sheet name. if no sheet name given, sheet1 is assumed
### pandas reads as a dataframe
df=pd.read_excel('myexcel.xlsx',sheet_name='newsheet')
df
df1=pd.read_excel('myexcel.xlsx',sheet_name='newsheet1')
df1
df2=pd.read_excel('myexcel.xlsx',sheet_name='newsheet2')
df2
df5=pd.read_excel('myexcel.xlsx')
print(df5)

#### write to excel
df5.to_excel('myoutputxl.xlsx',sheet_name='NewSheetOut2')
## over writes the existing sheet 
### example for writing into a new shete and append to existing
dfout1=pd.read_excel('myoutputxl.xlsx',sheet_name='NewSheetOut2')
dfout1<jupyter_output><empty_output><jupyter_text>READING FROM HTML INPUT<jupyter_code>datahtml = pd.read_html('https://www.fdic.gov/bank/individual/failed/banklist.html')

#### notee that depending on the size of html,
#### jupyter will take time to read and process the data.
#### output is a list
#### this command, picks up every table element that is present in html file
#### table references markings (can be found in the html source code "view source")
### this command, creates a list of all table elements and converts each item to data frame)

type(datahtml)
len(datahtml)
type(datahtml)
#### to check the first item in list --
### there is only onee table in the website...index 0 of list datahtml
datahtml[0]
### look at top 5 rows
datahtml[0].head() 
### bottom 5 rows
datahtml[0].tail()
### bottom 10 rows
datahtml[0].head(10)
datahtml[0].tail(10)<jupyter_output><empty_output><jupyter_text>Groupby Summarize<jupyter_code># for each start loc find the mean distance travelled
dfnew.groupby("start")["miles"].agg("mean").head()
# for each start loc find the mean & the total dist travelled
dfnew.groupby("start").agg(["mean","sum"]).head()<jupyter_output><empty_output><jupyter_text>Assignments1) Find mean quarterly sales by store2) Find the month with highest sales for each store  Other/Miscellaneous topics  Missing value treatment<jupyter_code>df.isnull().sum()
## Count missing values
df.isnull().sum()
# Fill NA with most common values
df["PURPOSE*"].value_counts()
df = df.fillna({"PURPOSE*":"Meeting"})
df["PURPOSE*"].value_counts()<jupyter_output><empty_output><jupyter_text>Matrix operations use case  Example 1 - compute month on month growth in sales<jupyter_code>store_sales.head()
sales_mat = np.array(store_sales.iloc[:,2:])
sales_mat
sales_mat_lag = sales_mat[:,1:]
sales_mat_lag
sales_mat_lag = sales_mat[:,1:]
sales_mat_lag = np.c_[sales_mat_lag,np.zeros(100)]
sales_diff = sales_mat_lag - sales_mat
perct_change = pd.DataFrame(sales_diff/sales_mat)
perct_change.head()<jupyter_output><empty_output><jupyter_text>Example 1 - compute net revenue<jupyter_code>profit_mat = np.random.uniform(low=0.05, high=0.25, size=(100,12))
## Find total annual profit for each store
profit_mat = profit_mat.transpose()
profit_mat.shape
prof_matrix = np.dot(sales_mat,profit_mat)
prof_matrix.shape<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13901496565260058, 'fraction_numerical': 0.022417811579980372, 'mean_word_length': 4.180965999364474, 'pattern_counts': {'":': 5, '<': 158, '<?xml version=': 0, '>': 168, 'https://': 3, 'lorem ipsum': 0, 'www.': 1, 'xml': 2}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 20, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '37', 'dir': 'jupyter-structured-clean-dedup', 'id': '16187', 'license': 'MIT', 'n_tokens_mistral': 10879, 'n_tokens_neox': 10322, 'n_words': 3219, 'path': 'foundation/applied-statistics/class_material_day_3/GL-APR-19-20-PYTHON-PANDAS-OVERVIEW-MAIN.ipynb', 'repo_name': 'tejeshreddy/greatlearning-aiml', 'size': '32619'}