<jupyter_start><jupyter_text>Importing pandas and reading different types of files<jupyter_code>import pandas as pd
df = pd.read_csv('pokemon_data.csv')
df_xl = pd.read_excel('pokemon_data.xlsx')
df_txt= pd.read_csv('pokemon_data.txt', sep = '\t')
df_txt.head(5)  == df.head(5)<jupyter_output><empty_output><jupyter_text>Reading Data<jupyter_code># Read Headers
df.columns
# Read single columns
df['Name']
#Read Multiple Columns 
df [[ 'Name', 'HP', 'Speed']]
df.index
#Reading an entire row - by index position
df.iloc[0]
#You can access any specific row of any column
df.iloc[0,1]<jupyter_output><empty_output><jupyter_text>* Format == [row number : column number] * So for Slicing , [0:end, 0:end] == [:,:] (single colon means all items)* Ex : [0:2, 0:2] == 0th row to 2nd row and 0th column to 2nd column.* Double colon means slicing steps, meaning the value after double colon will be the step of slicing.<jupyter_code>#You can also access multiple number of rows and columns by slicing ; 
df.iloc[0:2,0:2]
df.iloc[0:2, ::2]<jupyter_output><empty_output><jupyter_text>Resting and setting index Here, the index and the name are same.So, to understand better, we will change the dataframe a bit. We will copy the dataframe to another dataframe, reset the index and set it to the column 'Name' .* reset_index(drop = true)       =>drops the index,* reset_index(inplace = true)  =>data modifies inplace<jupyter_code>df_cpy = df.copy()
df_cpy = df_cpy.reset_index(drop = True)
df_cpy = df_cpy.set_index(['Name'])
df_cpy.head(3)
#Reading Row wise - by name of the index
df_cpy.loc['Bulbasaur']
##Reading each Row wise - by index of name
df_cpy.iloc[0]
#iterating through each row

#for index,row in df.iterrows():
#    print(index,row)<jupyter_output><empty_output><jupyter_text>Sorting & Describing<jupyter_code>df.describe()
df.sort_index()
#sorting any column values ; [True == 1, False == 0]

df_sorted = df.sort_values('Name', ascending = 1)
df_sorted['Name']
#Sorting Multiple Columns
df_sorted = df.sort_values(['Name', 'HP'], ascending = [True, False])
df_sorted[['Name', 'HP']]<jupyter_output><empty_output><jupyter_text>Adding & Deleting a new column   Method 1 - Using column names<jupyter_code>df.head(1)
df['Total'] = df['HP'] + df['Attack'] + df['Defense'] + df['Sp. Atk'] + df['Sp. Def'] + df['Speed']
df.head(1)
#droping table
df.drop(columns = ['Total'])
df.head(1)<jupyter_output><empty_output><jupyter_text>Method 2 - Using iloc() function<jupyter_code>df['Total'] = df.iloc[:,4:10].sum(axis = 1)
df.head(1)<jupyter_output><empty_output><jupyter_text>Re-arranging Columns<jupyter_code>columns = list(df.columns)  # gives a list of all columns
df = df[columns[0:4] + [columns[-1]] + columns[-3:-1]]
df.head(1)<jupyter_output><empty_output><jupyter_text>Writing / Saving Files to different formats<jupyter_code>df.to_csv('Updated_pokemon_data.csv', index = False) 
df.to_excel('Updated_pokemon_data.xlsx', index = False)
df.to_csv('Updated_pokemon_data.txt', index = False, sep = '\t')

#by using 'index = False' attribute, it doesn't write the index column to the saved file<jupyter_output><empty_output><jupyter_text>Filtering data for a speicific condition, using 'loc()'  Filtering any specific Row - by the name of the column<jupyter_code>df.loc[df['Name'] == 'Bulbasaur']
df.loc[(df['Type 1'] == 'Grass') & (df['Type 2'] == 'Poison') & (df['Generation'] == 1)]<jupyter_output><empty_output><jupyter_text>Regex Filtering (filter based on textual patterns)<jupyter_code>df.loc[df['Name'].str.contains('Mega')] #contains
df.loc[~df['Name'].str.contains('Mega')] #not contains<jupyter_output><empty_output><jupyter_text>Using regex library * flags = re.I => ignores all capitalization<jupyter_code>import re
df.loc[df['Name'].str.contains('^pi[a-z]*', flags = re.I, regex = True)]<jupyter_output><empty_output><jupyter_text>Conditional Changes<jupyter_code>df_cpy = df.copy()
df_cpy.head()
df_cpy.loc[df['Type 1'] == 'Fire', 'Legendary'] = True
df_cpy.head()
df_cpy = df.copy()
df_cpy.head()
df_cpy.loc[df['Total'] > 400, 'Legendary'] = True
df_cpy.head()<jupyter_output><empty_output><jupyter_text>Using Groupby()<jupyter_code>df.head()
df['Count'] = 1
df.head()
df.groupby('Type 1').count()
df.groupby('Type 1').count()['Count']
df.groupby(['Type 1', 'Type 2']).count()['Count']<jupyter_output><empty_output><jupyter_text>Working with big file  If the file size is big(say 20 gb) , we can't load the whole file at a time. So we need to load chunks of data at a time<jupyter_code>for df in pd.read_csv('Updated_pokemon_data.csv', chunksize = 20):
    print('===================================================================================')
    print(df)<jupyter_output>===================================================================================
     #                       Name Type 1  Type 2  Total  Generation  Legendary
0    1                  Bulbasaur  Grass  Poison    318           1      False
1    2                    Ivysaur  Grass  Poison    405           1      False
2    3                   Venusaur  Grass  Poison    525           1      False
3    3      VenusaurMega Venusaur  Grass  Poison    625           1      False
4    4                 Charmander   Fire     NaN    309           1      False
5    5                 Charmeleon   Fire     NaN    405           1      False
6    6                  Charizard   Fire  Flying    534           1      False
7    6  CharizardMega Charizard X   Fire  Dragon    634           1      False
8    6  CharizardMega Charizard Y   Fire  Flying    634           1      False
9    7                   Squirtle  Water     NaN    314           1      False
10   8                  Wartortle  Water     Na[...]<jupyter_text>Merging Dataframes<jupyter_code>import pandas as pd
products = pd.DataFrame([{'Product ID': 4109, 'Price' : 5.0, 'Product': 'Sushi Roll'},
                     {'Product ID': 1412, 'Price' : 0.5, 'Product': 'Egg'},
                     {'Product ID': 8931, 'Price' : 1.5, 'Product': 'Bagel'}])
invoices = pd.DataFrame([{'Customer':'Ali' , 'Product ID': 4109, 'Quantity':1 },
                        {'Customer':'Eric' , 'Product ID': 1412, 'Quantity': 12},
                        {'Customer':'Ande' , 'Product ID': 8931, 'Quantity': 6},
                        {'Customer':'Sam' , 'Product ID': 4109, 'Quantity': 2},])
answer = pd.merge(products, invoices, left_on = 'Product ID', right_on = 'Product ID')
answer = answer.sort_values('Price', ascending = False)
answer
staff_df = pd.DataFrame([{'First Name': 'Kelly', 'Last Name': 'Desjardins', 'Role': 'Director of HR'},
                         {'First Name': 'Sally', 'Last Name': 'Brooks', 'Role': 'Course liasion'},
                         {'First Name': 'James', 'Last Name': 'Wilde', 'Role': 'Grader'}])
student_df = pd.DataFrame([{'First Name': 'James', 'Last Name': 'Hammond', 'School': 'Business'},
                           {'First Name': 'Mike', 'Last Name': 'Smith', 'School': 'Law'},
                           {'First Name': 'Sally', 'Last Name': 'Brooks', 'School': 'Engineering'}])
new_df = pd.merge(staff_df, student_df, how = 'outer', left_on = ['First Name','Last Name'], right_on = ['First Name','Last Name'])
new_df<jupyter_output><empty_output><jupyter_text>Droping a specific row of entries<jupyter_code>new_df = new_df.drop(new_df[new_df['School'] == 'Business'].index)
new_df<jupyter_output><empty_output><jupyter_text>Renaming a Column<jupyter_code>new_df.rename(columns = {'School' : 'University'})<jupyter_output><empty_output><jupyter_text>Pandas Idioms - making code beautiful<jupyter_code>import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>Example 1️⃣: open the census.csv file, exclude SUMLEV == 50, rename ESTIMATESBASE2010 column to Estimates Base 2010,<jupyter_code>df = pd.read_csv('census.csv')<jupyter_output><empty_output><jupyter_text>Method one : The beautiful way<jupyter_code>(df.where(df['SUMLEV'] == 50)
 .dropna()
 .set_index(['STNAME','CTYNAME'])
 .rename(columns = {'ESTIMATESBASE2010' : 'Estimates Base 2010'})
 .head(1)
)<jupyter_output><empty_output><jupyter_text>Method 2 : The typical way<jupyter_code>df = df[df['SUMLEV'] == 50]
df = df.set_index(['STNAME','CTYNAME'])
df = df.rename(columns = {'ESTIMATESBASE2010' : 'Estimates Base 2010'})
df.head(1)<jupyter_output><empty_output><jupyter_text>Example 2️⃣ : Calculate min and max population for : [POPESTIMATE2010','POPESTIMATE2011','POPESTIMATE2012','POPESTIMATE2013','POPESTIMATE2014','POPESTIMATE2015'] columns in df dataframe from census.csv .     Note: Syntex for apply function => dataframe.apply(function, axis, .. )<jupyter_code>#using pre-defined function
import numpy as np
def min_max(row):
    data = row[['POPESTIMATE2010','POPESTIMATE2011','POPESTIMATE2012','POPESTIMATE2013','POPESTIMATE2014','POPESTIMATE2015']]
    return pd.Series({'min': np.min(data) , 'max' : np.max(data)})
df.apply(min_max, axis = 1)
#using lamdba 
columns = [['POPESTIMATE2010','POPESTIMATE2011','POPESTIMATE2012','POPESTIMATE2013','POPESTIMATE2014','POPESTIMATE2015']]

#df.apply(lambda x: np.max(x[columns]) )<jupyter_output><empty_output><jupyter_text>Group By     Example : Calculate avg population state wise using group by      Method 1 : Using groupby with user-defined function<jupyter_code>import pandas as pd
import numpy as np
df = df[df['SUMLEV'] == 50]
df_grouped = df.groupby('STNAME')
df_grouped.head(1)
for index, values in df_grouped:
    Avg = np.mean(values['CENSUS2010POP'])
    print( index , 'has : ', str(Avg) , 'population')<jupyter_output>Alabama has :  71339.34328358209 population
Alaska has :  24490.724137931036 population
Arizona has :  426134.4666666667 population
Arkansas has :  38878.90666666667 population
California has :  642309.5862068966 population
Colorado has :  78581.1875 population
Connecticut has :  446762.125 population
Delaware has :  299311.3333333333 population
District of Columbia has :  601723.0 population
Florida has :  280616.5671641791 population
Georgia has :  60928.63522012578 population
Hawaii has :  272060.2 population
Idaho has :  35626.86363636364 population
Illinois has :  125790.50980392157 population
Indiana has :  70476.10869565218 population
Iowa has :  30771.262626262625 population
Kansas has :  27172.55238095238 population
Kentucky has :  36161.39166666667 population
Louisiana has :  70833.9375 population
Maine has :  83022.5625 population
Maryland has :  240564.66666666666 population
Massachusetts has :  467687.78571428574 population
Michigan has :  119080.0 population
Minnesota has[...]<jupyter_text>Method 2 : using agg() function        Note : Syntex for agg() is => agg({'Column name' : Method}) (for old version)                               => agg([method1, method2, ...])  (for new version)<jupyter_code>df = pd.read_csv('census.csv')
df.groupby('STNAME').agg({'CENSUS2010POP' : np.average})<jupyter_output><empty_output><jupyter_text>Looking at our backpacking equipment DataFrame, suppose we are interested in finding our total weight for each category. Use groupby to group the dataframe, and apply a function to calculate the total weight (Weight x Quantity) by category.<jupyter_code>import pandas as pd
df_backpack = pd.read_csv('backpack.csv')
df_backpack.head(1)
df_backpack.head(1)
def total_weight(df_backpack):
    return sum(df_backpack['Quantity'] * df_backpack['Weight (oz.)'])
df_new = df_backpack.groupby('Category').apply(total_weight)
print(df_new)
df_backpack.head(1)<jupyter_output><empty_output><jupyter_text>Groupby using multiple Columns - Single Index(Series groupby)<jupyter_code>df_backpack.set_index('Item').groupby(level = 0)['Category','Quantity', 'Weight (oz.)'].head(1)<jupyter_output><ipython-input-121-ea7879626502>:1: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.
  df_backpack.set_index('Item').groupby(level = 0)['Category','Quantity', 'Weight (oz.)'].head(1)<jupyter_text>Groupby (multiple Columns) - Multiple Index (Dataframe groupby)<jupyter_code>(df_backpack.set_index(['Item','Category'])
 .groupby(level = 0)['Quantity', 'Weight (oz.)']
 .agg([np.sum, np.mean])
).head(1)<jupyter_output><ipython-input-122-9269cccbf877>:1: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.
  (df_backpack.set_index(['Item','Category'])<jupyter_text>More Example : using census dataframe<jupyter_code>df = pd.read_csv('census.csv')
df[df['SUMLEV'] == 50].head(1)<jupyter_output><empty_output><jupyter_text>Task 1: find avg for CENSUS2010POP<jupyter_code>#way 1
df.groupby('STNAME')['CENSUS2010POP'].agg([np.average]).head(1)
#way 2
(df.set_index('STNAME')
.groupby(level = 0)['CENSUS2010POP']
 .agg([np.average])
).head(1)<jupyter_output><empty_output><jupyter_text>task 2: Find both average and sum for CENSUS2010POP<jupyter_code>(df.set_index('STNAME')
 .groupby(level = 0)['CENSUS2010POP']
 .agg([np.sum, np.average])
).head(1)<jupyter_output><empty_output><jupyter_text>Task 3 : Find both average and sum for POPESTIMATE2010,POPESTIMATE2011 & POPESTIMATE2012<jupyter_code>(df.set_index('STNAME')
.groupby(level = 0)['POPESTIMATE2010','POPESTIMATE2011', 'POPESTIMATE2012']
.agg([np.sum, np.average])
).head(1)<jupyter_output><ipython-input-127-7ea86d9f9e7f>:1: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.
  (df.set_index('STNAME')<jupyter_text>Scaling     Example 1 : Using astype to categorize data<jupyter_code>import pandas as pd
df = pd.DataFrame(['A+', 'A', 'A-', 'B+', 'B', 'B-', 'C+', 'C', 'C-', 'D+', 'D'],
                  index=['excellent', 'excellent', 'excellent', 'good', 'good', 'good', 'ok', 'ok', 'ok', 'poor', 'poor'])
df.rename(columns={0: 'Grades'}, inplace=True)
df
df['Grades'] > 'C'
df['Grades'] = pd.Categorical(df['Grades'], ordered = True)
df
df['Grades'] > 'C'<jupyter_output><empty_output><jupyter_text>Example 2 : Using cut() function to categorize data<jupyter_code>s = pd.Series([168, 180, 174, 190, 170, 185, 179, 181, 175, 169, 182, 177, 180, 171])
s
pd.cut(s, 3, labels = ['low', 'medium', 'high'])<jupyter_output><empty_output><jupyter_text>Example 3 : Reading data from 'census.csv' file, excluding 'SUMLEV'==50, set index to 'STNAME' and Groupby using 'CENSUS2010POP' and calculate their avrage. and then Categorize the data in 10 Categories.<jupyter_code>import numpy as np
df = pd.read_csv('census.csv')
df = df[df['SUMLEV'] == 50]
new_df = (df.set_index('STNAME')
 .groupby(level = 0)['CENSUS2010POP']
 .agg([np.mean])
)
pd.cut(new_df['mean'], 10 , labels = ['Category 1','Category 2','Category 3','Category 4','Category 5'
                                      ,'Category 6','Category 7','Category 8','Category 9','Category 10'])<jupyter_output><empty_output><jupyter_text>Pivot table<jupyter_code>df = pd.read_csv('cars.csv')
df.head(1)<jupyter_output><empty_output><jupyter_text>Example : find the avg km & time, total km & time for cars in the dataframe, showing the model, manufacturer and year.<jupyter_code>df.pivot_table(values = ['(km)','TIME (h)'], index = ['Make', 'Model', 'YEAR'], aggfunc = [np.average, np.sum] , margins = True )<jupyter_output><empty_output><jupyter_text>Date Functionality in Pandas<jupyter_code>import numpy as np
import pandas as pd<jupyter_output><empty_output><jupyter_text>Timestamp<jupyter_code>pd.Timestamp('6/09/2020 8:41PM')<jupyter_output><empty_output><jupyter_text>Period<jupyter_code>pd.Period('9/2020')
pd.Period('6/09/2020 ')
pd.Period('6/09/2020 8:42 PM')<jupyter_output><empty_output><jupyter_text>DatetimeIndex<jupyter_code>t1 = pd.Series(['a','b','c'],
              [pd.Timestamp('2016-09-01'), pd.Timestamp('2016-09-02'), pd.Timestamp('2016-09-03')])
print(t1)
print(type(t1.index))<jupyter_output>2016-09-01    a
2016-09-02    b
2016-09-03    c
dtype: object
<class 'pandas.core.indexes.datetimes.DatetimeIndex'><jupyter_text>PeriodIndex<jupyter_code>t2 = pd.Series(['d','e','f'], 
              [pd.Period('2016-09'), pd.Period('2016-10'), pd.Period('2016-11')])
print(t2)
print(type(t2.index))<jupyter_output>2016-09    d
2016-10    e
2016-11    f
Freq: M, dtype: object
<class 'pandas.core.indexes.period.PeriodIndex'><jupyter_text>Converting to Datetime<jupyter_code>pd.to_datetime('4.7.12', dayfirst=True)
d1 = ['2 June 2013', 'Aug 29, 2014', '2015-06-26', '7/12/16']
ts3 = pd.DataFrame(np.random.randint(10, 100, (4,2)), index=d1, columns=list('ab'))
ts3
ts3.index = pd.to_datetime(ts3.index)
ts3<jupyter_output><empty_output><jupyter_text>Timedeltas     Calculating difference between 2 dates<jupyter_code>pd.Timestamp('9/3/2016')-pd.Timestamp('9/1/2016')<jupyter_output><empty_output><jupyter_text>Adding time difference to a date, to get the final date<jupyter_code>pd.Timestamp('9/3/2016') + pd.Timedelta('12D 3H')<jupyter_output><empty_output><jupyter_text>Working with Dates in a Dataframe<jupyter_code>dates = pd.date_range('10-01-2016', periods=9, freq='2W-SUN')
dates
df = pd.DataFrame({'Count 1': 100 + np.random.randint(-5, 10, 9).cumsum(),
                  'Count 2': 120 + np.random.randint(-5, 10, 9)}, index=dates)
df
df.index.weekday_name
df.diff()
df.resample('M').mean()
df['2016']
df.asfreq('W', method='ffill')
import matplotlib.pyplot as plt
%matplotlib inline

df.plot()<jupyter_output><empty_output><jupyter_text>Distributions in Pandas     np.random.binomial(number of loops, probability) gives a result either 0 or 1 , based on the parameters<jupyter_code>import numpy as np
import pandas as pd
np.random.binomial(1,0.5)
np.random.binomial(1000, 0.5)/ 1000
chances_of_tornado = 0.01/100
np.random.binomial(100000, chances_of_tornado)<jupyter_output><empty_output><jupyter_text>Example:    Suppose we want to simulate the probability of flipping a fair coin 20 times, and getting a number greater than or equal to 15. Use np.random.binomial(n, p, size) to do 10000 simulations of flipping a fair coin 20 times, then see what proportion of the simulations are 15 or greater.     Syntex:    numpy.random.binomial(n, p, size=None)    n trials and p probability of success where n an integer >= 0 and p is in the interval [0,1]<jupyter_code>import numpy as np
x = np.random.binomial(20,0.5, 10000)
(x>=15).mean()<jupyter_output><empty_output><jupyter_text>chances of back to back tornado happening in two days in 1000000 years<jupyter_code>probability  = 0.01
tornado_event = np.random.binomial(1, probability, 1000000)
tornado_twoday= 0

for i in range(1,len(tornado_event)-1):
    if tornado_event[i] == 1 and tornado_event[i-1] == 1:
        tornado_twoday += 1
        
print('{} numbers of tornado will happen two days in a row in about {} years'.format(tornado_twoday,1000000/365))<jupyter_output>104 numbers of tornado will happen two days in a row in about 2739.72602739726 years
{'fraction_non_alphanumeric': 0.15319329514109908, 'fraction_numerical': 0.05866751538298324, 'mean_word_length': 5.004140127388535, 'pattern_counts': {'":': 0, '<': 194, '<?xml version=': 0, '>': 205, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 5, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '49', 'dir': 'jupyter-structured-clean-dedup', 'id': '350579', 'license': 'Apache-2.0', 'n_tokens_mistral': 7487, 'n_tokens_neox': 6604, 'n_words': 1566, 'path': 'basic_of_pandas.ipynb', 'repo_name': 'tauhid61/pandas_basic', 'size': '18852'}