<jupyter_start><jupyter_text>Pandas- [Pandas](Pandas)- [Introduction to Pandas](Introduction-to-Pandas)- [Series](Series)    - [Initializing Series](Initializing-Series)    - [Selecting Elements](Selecting-Elements)        - [loc](loc)        - [iloc](iloc)    - [Combining Series](Combining-Series)    - [Exercises](Exercises)        - [Exercise 1](Exercise-1)        - [Exercise 2](Exercise-2)        - [Exercise 3](Exercise-3)- [DataFrames](DataFrames)    - [Creating DataFrames](Creating-DataFrames)        - [Series as Rows](Series-as-Rows)        - [Series as Columns](Series-as-Columns)        - [Summary](Summary)    - [Pandas Pretty Print in Jupyter](Pandas-Pretty-Print-in-Jupyter)    - [Importing and Exporting Data](Importing-and-Exporting-Data)        - [Reading CSV](Reading-CSV)        - [Writing CSV](Writing-CSV)    - [Selecting Data](Selecting-Data)    - [Exercises](Exercises)        - [Exercise 1](Exercise-1)        - [Exercise 2](Exercise-2)        - [Exercise 3](Exercise-3)        - [Exercise 4](Exercise-4)- [Data Processing](Data-Processing)    - [Aggregation](Aggregation)    - [Arithmetic](Arithmetic)    - [Grouping](Grouping)    - [Unique and Duplicate Values](Unique-and-Duplicate-Values)        - [unique](unique)        - [duplicate](duplicate)    - [Exercises](Exercises)        - [Exercise 1](Exercise-1)        - [Exercise 2](Exercise-2)        - [Exercise 3](Exercise-3)        - [Exercise 4](Exercise-4)- [Merge Data Frames](Merge-Data-Frames)    - [Exercises](Exercises)        - [Exercise 1](Exercise-1)- [Reshaping Data Frames](Reshaping-Data-Frames)- [Exploratory Data Analysis](Exploratory-Data-Analysis)    - [Exercise 1](Exercise-1)    - [Exercise 2](Exercise-2)    - [Exercise 3](Exercise-3)    - [Exercise 4](Exercise-4)        - [Stacking and Unstacking Data Frames](Stacking-and-Unstacking-Data-Frames)    - [Exercise 5](Exercise-5)    - [Exercise 6](Exercise-6)  Introduction to Pandas[Pandas](http://pandas.pydata.org/) is a software library written for the Python programming language for data manipulation and analysis. In particular, it offers data structures and operations for manipulating numerical tables and time series. Pandas is free software released under the three-clause BSD license. The name is derived from the term _panel data_, an econometrics term for multidimensional structured data sets.At it's core, Pandas consists of NumPy arrays and additional functions to perform typical data analysis tasks.**Resources**:  * [Pandas Documentation](http://pandas.pydata.org/pandas-docs/stable/index.html), especially[10 minutes to pandas](http://pandas.pydata.org/pandas-docs/stable/10min.html)* [<NAME>' learn-pandas](https://bitbucket.org/hrojas/learn-pandas)  * [Harvard CS109 lab1 content](https://github.com/cs109/2015lab1)  SeriesSeries form the basis of Pandas. It is a one-dimensional labeled array capable of holding data of any type (integer, string, float, python objects, etc.). The axis labels are collectively called index. Pandas Series is nothing but a column in an excel sheet.  Initializing SeriesSeries can be initialized from Python objects like lists or tuples. If only values are given, Pandas generates default indices.<jupyter_code>#!conda install pandas
import pandas as pd
from IPython.display import display
animals = ['Tiger', 'Bear', 'Moose']
pd.Series(animals)

# 0,1,2 is indexing, this is also like a Key in dictionary
numbers = [1, 2, 3]
pd.Series(numbers)<jupyter_output><empty_output><jupyter_text>Series can be mixed type<jupyter_code># Create a mixed series

mixed = [1, 2, "Three"]
print(pd.Series(mixed)) 
print()
print(type(mixed[0]))
print(type(mixed[0]))

# series will detect every element as object data type
# However every element has its own data type<jupyter_output>0        1
1        2
2    Three
dtype: object

<class 'int'>
<class 'int'><jupyter_text>Series also support missing values via the `None` type.<jupyter_code>#create a pandas series with None
#observe the dtype

animals = ['Tiger', 'Bear', None]
print(pd.Series(animals))
print("")

print(type(animals[0]))
print(type(animals[1]))
print(type(animals[2]))
# using the None keyword in list --> NaN in series

import numpy as np
numbers = [1, 2, None]
print(pd.Series(numbers))
print("")

print(type(numbers[0]))
print(type(numbers[1]))
print(type(numbers[2]))<jupyter_output>0    1.0
1    2.0
2    NaN
dtype: float64

<class 'int'>
<class 'int'>
<class 'NoneType'><jupyter_text>We can define custom keys during initialization.<jupyter_code># need to be careful with index. Index can be numbers.

sports = pd.Series(
    data=["Bhutan", "Scotland", "Japan", "South Korea"], 
    index=["1", "2", "3", "4"])
print(sports)

# we can set our own index!
# need to be careful with index. Index can be characters.

sports = pd.Series(
    data=["Bhutan", "Scotland", "Japan", "South Korea"], 
    index=["a", "b", "c", "d"])
print(sports)

# We can use alphabet as index too!<jupyter_output>a         Bhutan
b       Scotland
c          Japan
d    South Korea
dtype: object<jupyter_text>Alternatively, Series can also be initialized with dictionaries. Indices are then generated from the dictionary keys.<jupyter_code># We can also convert dictionary to series 

dict = {
    'Archery': 'Bhutan',
    'Golf': 'Scotland',
    'Sumo': 'Japan',
    'Taekwondo': 'South Korea'
}
#create a pandas series from dictionary

sports = pd.Series(dict)
print(sports)<jupyter_output>Archery           Bhutan
Golf            Scotland
Sumo               Japan
Taekwondo    South Korea
dtype: object<jupyter_text>We can list values and indices of series.<jupyter_code>print(sports.index, end = '\n\n')
print(sports.values)<jupyter_output>Index(['Archery', 'Golf', 'Sumo', 'Taekwondo'], dtype='object')

['Bhutan' 'Scotland' 'Japan' 'South Korea']<jupyter_text>Series type<jupyter_code>type(sports)<jupyter_output><empty_output><jupyter_text>Selecting ElementsAs a result of iterative development of the Pandas library, there are several ways to select elements of a Series. Most of them are considered "legacy", however, and the best practice is to use `*.loc[...]` and `*.iloc[...]`. Take care to use the square brackets with `loc` and `iloc`, *not* the regular brackets as you would with functions.  locSelect elements by their indices. If the index is invalid, either a `TypeError` or a `KeyError` will be thrown.<jupyter_code># indexing using series convention 
print(sports.loc['Golf'])<jupyter_output>Scotland<jupyter_text>ilocSelect elements by their numerical IDs, i.e. the n-th element.<jupyter_code># indexing using python convention 
# we can call item using the usual python numbering

print(sports.iloc[1])<jupyter_output>Scotland<jupyter_text>If the indices were autogenerated then both loc and iloc seem to be identical. This is **NOT** always the case !!!<jupyter_code>sports_noindex = pd.Series(sports.values)
print(sports_noindex)
print("")
print(sports_noindex.loc[0]) 
print(sports_noindex.iloc[0])<jupyter_output>0         Bhutan
1       Scotland
2          Japan
3    South Korea
dtype: object

Bhutan
Bhutan<jupyter_text>Take care to keep your code semantically correct, however. For example, if the series is resorted, the index of each element stays the same, but the ID changes!<jupyter_code># indexing change as we sort.

sports_noindex_sorted = sports_noindex.sort_values()
print(sports_noindex_sorted)
print("")
print(sports_noindex_sorted.loc[1])
print(sports_noindex_sorted.iloc[1])<jupyter_output>0         Bhutan
2          Japan
1       Scotland
3    South Korea
dtype: object

Scotland
Japan<jupyter_text>If you want to select by index then use `loc`, if you want to select by ID then use `iloc`. Do not use them interchangeably just because they return the same results right now. This will eventually lead to bugs in your code.  Combining SeriesSeries can be combined by appending one to another<jupyter_code># combine series

s1 = pd.Series(["A", "B", "C"])
s2 = pd.Series(["D", "E", "F"])
print(s1)
print("")
print(s2)
print("")

s3 = s1.append(s2) # s1 comes first then s2
print(s3)                 # index still the same 

s4 = s2.append(s1) # as you can see s2 comes first
print(s4)<jupyter_output>0    A
1    B
2    C
dtype: object

0    D
1    E
2    F
dtype: object

0    A
1    B
2    C
0    D
1    E
2    F
dtype: object
0    D
1    E
2    F
0    A
1    B
2    C
dtype: object<jupyter_text>Notice the duplicate indices! Pandas permits this and selecting by `loc` will return **both** entries<jupyter_code>print(s3.loc[0])<jupyter_output>0    A
0    D
dtype: object<jupyter_text>Using ``iloc`` will only return based on index<jupyter_code>print(s3.iloc[0])
print(s3.iloc[3])<jupyter_output>A
D<jupyter_text>Also notice that if your selection of a Series results in a single entry, Pandas automatically converts it to its base type, i.e. a string in this case. If the selection consists of more than 1 entry, however, a Series is returned.<jupyter_code>print(s3.loc[0])
print(type(s3.loc[0]))
print("")
print(s3.iloc[0])
print(type(s3.iloc[0]))<jupyter_output>0    A
0    D
dtype: object
<class 'pandas.core.series.Series'>

A
<class 'str'><jupyter_text>Exercises  Exercise 1Create a pandas Series object from the following movie ratings        The Avengers: 9.2    Mr. Bean: 7.4    Garfield: 2.1    Star Wars The Force Awakens: 8.8<jupyter_code>rating = {
    
    'The Avengers': 9.2,
    'Mr. Bean': 7.4,
    'Garfield': 2.1,
    'Star Wars The Force Awakens': 8.8
}
# convert to series

movie_rating = pd.Series(rating)
print(movie_rating)<jupyter_output>The Avengers                   9.2
Mr. Bean                       7.4
Garfield                       2.1
Star Wars The Force Awakens    8.8
dtype: float64<jupyter_text>Exercise 2Select the rating for the movie 'Garfield'.<jupyter_code>movie_rating.loc['Garfield']
movie_rating.iloc[2]<jupyter_output><empty_output><jupyter_text>Exercise 3Select the **index** of the 2$^{nd}$ entry<jupyter_code>movie_rating.index[1]
movie_rating.index<jupyter_output><empty_output><jupyter_text>DataFramesMultiple series with common indices can form a data frame. A data frame is like a table, with rows and columns (e.g., as in SQL or Excel).|  .   | Animal | Capital || --- | --- | --- || India | a | b || Sweden | a | b |Each row usually denotes an entry in our data and each column a feature we're interested in.  Creating DataFrames  Series as ColumnsWe can also create data frames column-wise<jupyter_code>col1 = pd.Series({"India": "Elephant", "Sweden": "Reindeer"})
col2 = pd.Series({"India": "New Delhi", "Sweden": "Stockholm"})
print(col1)
print()
print(col2)
df = pd.DataFrame(data={"Animal": col1, "Capital": col2})
df<jupyter_output><empty_output><jupyter_text>Pandas' flexibility lets us cut out the middle man and use nested dictionaries. The keys of the outer dictionary become the column names and those of the inner dictionary become the row names.<jupyter_code>df = pd.DataFrame({
    "Animal": {
        "India": "Elephant",
        "Sweden": "Reindeer"},
    "Capital": {
        "India": "New Delhi",
        "Sweden": "Stockholm"}})
df<jupyter_output><empty_output><jupyter_text>Another Approach using Dictionary<jupyter_code>df = pd.DataFrame({
    'Animal':['a','a'],
    'Capital':['b','b']
}, 
    index =['India','Sweden'])

df

# return error if you have uneven list<jupyter_output><empty_output><jupyter_text>SummarySeries are pasted together to become data frames. They can be pasted as:- rows: `data=[series1, series2, ...]`- columns: `data={"Column Name 1": series1, "Column Name 2": series2, ...}` We can use the same `*.index()` and `*.values()` functions as for Series<jupyter_code>print(df.index)
print(df.columns)
print(df.values)<jupyter_output>Index(['India', 'Sweden'], dtype='object')
Index(['Animal', 'Capital'], dtype='object')
[['a' 'b']
 ['a' 'b']]<jupyter_text>Pandas Pretty Print in JupyterJupyter has a 'pretty print' option for Pandas dataframes. Using `print` will print the dataframes in Jupyter as they would appear in a standard console. But leaving it away or using IPython's `display` function will render them as HTML tables<jupyter_code>print(df)
from IPython.display import display
display(df)<jupyter_output><empty_output><jupyter_text>Jupyter allows a shortcut for the `display` function. If we execute a Python command or line of code that results in a data frame, Jupyter will assume we want to display it and do so using its built-in function. Note, however, that it will only ever do this with the last relevant line in each cell.<jupyter_code>df
df*2<jupyter_output><empty_output><jupyter_text>Importing and Exporting DataMost often we don't create data within our code but read it from external sources. Pandas has a large collection of importing (and corresponding exporting) functions available.| Data | Reader | Writer || --- | --- | --- || CSV | `read_csv` | `to_csv` || JSON | `read_json` | `to_json` || HTML | `read_html` | `to_html` || Local clipboard | `read_clipboard` | `to_clipboard` || Excel | `read_excel` | `to_excel` || HDF5 | `read_hdf` | `to_hdf` || Feather | `read_feather` | `to_feather` || Parquet | `read_parquet` | `to_parquet` || Msgpack | `read_msgpack` | `to_msgpack` || Stata | `read_stata` | `to_stata` || SAS | `read_sas` |  || Python Picke Format | `read_pickle` | `to_pickle` || SQL | `read_sql` | `to_sql` || Google Big Query | `read_gbq` | `to_gbq` |http://pandas.pydata.org/pandas-docs/stable/io.html  Reading CSVWe will read a tabular CSV file as an example.<jupyter_code>cars = pd.read_csv("data/cars.csv") # relative directory
cars.head(3)<jupyter_output><empty_output><jupyter_text>We can also define one of the columns as an index column using either the column header (if it exists) or the column ID (remember, Python starts counting at 0)<jupyter_code># make new index using other column
cars = pd.read_csv("data/cars.csv", index_col="model")

# Use head() to print only the first few lines
cars.head()
# We can read how many rows that we want

cars = pd.read_csv("data/cars.csv", index_col=0)
cars.head(3)
cars.tail(3)<jupyter_output><empty_output><jupyter_text>Writing CSVWriting CSV files is as straightforward as it gets. Notice that these functions are now methods of the specific objects, not of base Pandas<jupyter_code># !ls
# For windows:
!dir
cars.to_csv("cars2.csv")
!dir<jupyter_output>Volume in drive C has no label.
 Volume Serial Number is 2C31-3910

 Directory of C:\Users\fazie\Desktop\Barang Faez\CADS\Python Advance

10/07/2020  05:30 PM    <DIR>          .
10/07/2020  05:30 PM    <DIR>          ..
10/07/2020  08:46 AM    <DIR>          .ipynb_checkpoints
10/07/2020  10:01 PM             1,811 cars2.csv
10/07/2020  12:23 PM    <DIR>          data
10/06/2020  06:51 AM    <DIR>          example
10/07/2020  03:17 PM    <DIR>          images
10/06/2020  06:51 AM    <DIR>          Pictures
10/06/2020  05:13 PM           109,594 Python_Day4_SC.ipynb
10/07/2020  05:30 PM           299,938 Python_Day5_SC.ipynb
10/06/2020  06:51 AM            52,575 Python_Day6_SC.ipynb
10/06/2020  06:51 AM             2,814 Untitled.ipynb
               5 File(s)        466,732 bytes
               7 Dir(s)  98,939,170,816 bytes free<jupyter_text>Selecting DataSelecting data from Pandas arrays works just as it did for NumPy arrays, except that `loc` and `iloc` are necessary.<jupyter_code># row with index no. 9
cars.iloc[9]
# column alone is a series since dataframe is a combination of series
type(cars.iloc[9])
cars.iloc[9].unique()
# row with index 4 to 6

cars.iloc[4:7]
# skip 2

cars.iloc[1:9:2]<jupyter_output><empty_output><jupyter_text>As with Series, we can also select items by their index names.<jupyter_code># select row with index = "Datsun 710"

cars = pd.read_csv("data/cars.csv", index_col="model")

cars.loc[["Datsun 710"]]

#view in dataframe
# this will become a series instead of dataframe
cars.loc["Datsun 710"]
# select 2 rows

cars.loc[["Datsun 710", "<NAME>"]]<jupyter_output><empty_output><jupyter_text>Notice how a single entry is shown as a series but multiple entries as a data frame. This is analogous to how a single entry of a series is shown as a base type and multiple entries as a smaller seriesBase Type --> Series --> Data Frame Selecting columns can be done just as with dictionaries except that we can select multiple Pandas columns simultaneously. As with row selection, selecting a single column results in a Series object but selecting multiple columns results in a new DataFrame object.<jupyter_code># view all columns
cars.columns
# select columns  

cars[['mpg','cyl']].head(3)
# select 2 columns with only 5 rows

cars[["disp", "wt"]].head()<jupyter_output><empty_output><jupyter_text>Alternatively, we can also use the `*.loc`/`.*iloc` syntax. In this case, we have to include both the row and column indices to select. As with base Python, the color `:` instructs Pandas to select all rows or columns<jupyter_code># select column = 'disp'

cars[["disp"]].head(3)
# select a particular cell only...

cars.loc[   "Mazda RX4", "disp"] # row and column 
# call 2 rows as dataframe
cars.loc[['Datsun 710','Mazda RX4']]
cars.loc[['Datsun 710','Mazda RX4'],:]<jupyter_output><empty_output><jupyter_text>Take note that if we want to mix ID and index selection, we need to chain together `loc` and `iloc` calls. There is no way to combine this into a single index tuple.<jupyter_code>display(cars.iloc[[4]])
print()
print(cars.loc[:, 'mpg'][4])
print(cars.iloc[4]['mpg'])


cars.loc[:, ["mpg"]].head(3)<jupyter_output><empty_output><jupyter_text>We can see the names of all columns with the `columns` property (notice that this is also an index object, just as the row names is).<jupyter_code>print(cars.columns)
print(cars.index)<jupyter_output>Index(['mpg', 'cyl', 'disp', 'hp', 'drat', 'wt', 'qsec', 'vs', 'am', 'gear',
       'carb'],
      dtype='object')
Index(['Mazda RX4', 'Mazda RX4 Wag', 'Datsun 710', 'Hornet 4 Drive',
       'Hornet Sportabout', 'Valiant', 'Duster 360', 'Merc 240D', 'Merc 230',
       'Merc 280', 'Merc 280C', 'Merc 450SE', 'Merc 450SL', 'Merc 450SLC',
       'Cadillac Fleetwood', 'Lincoln Continental', 'Chrysler Imperial',
       'Fiat 128', 'Honda Civic', 'Toyota Corolla', 'Toyota Corona',
       'Dodge Challenger', 'AMC Javelin', 'Camaro Z28', 'Pontiac Firebird',
       'Fiat X1-9', 'Porsche 914-2', 'Lotus Europa', 'Ford Pantera L',
       'F<NAME>', 'Maserati Bora', 'Volvo 142E'],
      dtype='object', name='model')<jupyter_text>We can also use boolean masks to select rows or columns, i.e.```pythoncars.loc[True, True, False, True, False, ...]```However, as we're dealing with large datasets, typing them out by hand is suboptimal. So let's use some simple boolean conditions instead.<jupyter_code># Pandas applies the operation to each individual entry

cars["mpg"] > 25
# Use loc, not iloc, to select based on boolean masks

cars.loc[cars["mpg"] > 25]<jupyter_output><empty_output><jupyter_text>We can also select specific rows of certain columns with boolean masks.<jupyter_code># select specific columns with masking

cars.loc[cars["mpg"] > 25,
         ["hp", "disp"]]
# select specific cell...

# cars.loc[0,'model']
# select specific cell...

cars.iloc[0]['mpg']<jupyter_output><empty_output><jupyter_text>ExercisesFamiliarize yourselves with data frame creation and handling.  Exercise 1Manually create a dataframe from the following data. EmployeeID should be the index of the dataframe. Try using different methods (e.g. nested dictionaries, list of lists, series objects as rows or columns)EmployeeID | EmployeeName | Salary | Department---------- | ------------ | ------ | ----------2044       | James        |  2500  | Finance1082       | Hannah       | 4000   | Sales7386       | Victoria     | 3700   | IT<jupyter_code>details = {
    
    'EmployeeID' : [2044, 1082, 7386],
    'EmployeeName' : ['James', 'Hannah', 'Victoria'],
    'Salary': [2500, 4000, 3700],
    'Department': ['Finance', 'Sales', 'IT'],
    
}

pd.DataFrame(details).set_index(keys = 'EmployeeID')<jupyter_output><empty_output><jupyter_text>Exercise 2Read in the chocolate.csv data set and display the first 8 lines<jupyter_code>choc = pd.read_csv('data/chocolate.csv')
choc.head(8)<jupyter_output><empty_output><jupyter_text>Exercise 3Select only the chocolates with "Congo" as the country of origin and show only the rating, the cocoa percent, and the country of origin (to make sure we've selected the right products)<jupyter_code>choc.loc[choc['Country of Origin'] == 'Congo', ['Rating','Cocoa Percent','Country of Origin']]<jupyter_output><empty_output><jupyter_text>Exercise 4Oh no! There was a mistake in the data entry. One of the products has a missing country of origin. Please find it, replace it with "Venezuela", and save the fixed data frame as "chocolate_fixed.csv"  - You can use `*.isna()` to identify which entry of a series is either `NaN` or `None`, e.g. `mySeries.isna()`  - You can assign values to data frames just like you would to lists, e.g. `df.iloc[0, 5] = 15`<jupyter_code>choc.loc[choc["Country of Origin"].isna(),
        'Country of Origin'] = 'Venezuela'
choc.iloc[1072]
choc.to_csv('data/chocolate_fixed.csv')<jupyter_output><empty_output><jupyter_text>Data ProcessingPandas contains many functions to process and transform data. These can be called either on data frames or individual series. Describing every function in detail is far too time-consuming and application-dependent. A thorough list and description of *all* Pandas functionality can be found here: https://pandas.pydata.org/pandas-docs/stable/api.htmlMany of the functions are more or less self-explanatory and/or well-documented  Aggregation<jupyter_code>numbers = pd.Series([1, 2, 3, 4, 5, 5, 6, 6, 6])
print(numbers.sum())
print(numbers.mean())
print(numbers.max())
print(numbers.min())
print(numbers.idxmax())# return index yang value dia max
print(numbers.idxmin())# return index yang value dia min<jupyter_output>38
4.222222222222222
6
1
6
0<jupyter_text>Functions can be applied to series or data.frames. In the case of data frames, they are applied to each row or column individually<jupyter_code>df = pd.DataFrame([[1,1,1], [2,2,2], [3,3,3]])
df
# Sum by column

df.sum() # by default akan add by column 
# check for each column, which index has the max value

df.idxmax()<jupyter_output><empty_output><jupyter_text>We can decide whether the aggregation should occur along columns or rows. Note however, that the syntax is confusing. `axis=X` indicates along which dimension the function will "travel". For example, `axis=columns` indicates that all columns will be collapsed into the function, and the function will be applied to individual rows. Likewise, `axis=rows` means that the function will travel along rows and compute the aggregate value for each column individually.<jupyter_code># sum the value for each column.
display(df)
print(df.sum(axis = 'columns')) # from left to right
print(df.sum(axis = 'rows')) # from up to down
# sum the value for each row.

display(df)
print(df.sum(axis = 'rows')) # from up to down<jupyter_output><empty_output><jupyter_text>The most important aggregation function is `*.apply()`, which applies an arbitrary function to each row/column.<jupyter_code>display(df)
print(df.apply( lambda x: sum(x**2)))
print(df.apply( lambda x: sum(x**2) , axis = 'columns'))<jupyter_output><empty_output><jupyter_text>`*.apply()` is slower than the built-in functions, so should not be used for simple operations that can also be solved with direct operations on data frames.<jupyter_code>df = pd.DataFrame([[1,1,1], [2,2,2], [3,3,3]])

%timeit df.apply(lambda x: sum(x**2))
%timeit (df**2).sum() # faster!<jupyter_output>2.09 ms ± 47.4 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
642 µs ± 72.7 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)<jupyter_text>Also take care that the function will be applied to all columns, regardless of type. The built-in functions are clever enough to skip columns for which they are not defined.<jupyter_code>df = pd.DataFrame({
    "Age": [10, 12, 12], 
    "Name": ["Liz", "John", "Sam"]})
df.sum()

# Uncomment for exception
#df.apply(lambda x: sum(x**2), axis="rows")
# focus on Age 

df[['Age']].apply(lambda x: sum(x**2), axis="rows")<jupyter_output><empty_output><jupyter_text>ArithmeticWe can also perform element-wise operations on dataframe columns or rows, e.g.<jupyter_code>df = pd.DataFrame(
    data=[[1,2,3], [4,5,6], [7,8,9]], 
    columns=["ColA", "ColB", "ColC"], 
    index=["RowA", "RowB", "RowC"])
df
df["ColA"] + df["ColB"]
# Pandas is smart enough to convert our list into a series and then add the two columns element-wise

df["ColA"] + [10, 11, 12]  # notice this call column 
# Remember, both rows AND columns can be represented as Pandas series

df.loc["RowA"] * df.loc["RowB"]  # notice that this calls row<jupyter_output><empty_output><jupyter_text>Pandas adheres to the same broadcasting rules as NumPy<jupyter_code>df = pd.DataFrame(
    data=[[1,2], [3,4], [5,6]], 
    columns=["ColA", "ColB"], 
    index=["RowA", "RowB", "RowC"])
df
df * 2
df * [1, -1]
#Transpose shortcut

df.T
# the row with index = 'RowA'

df.loc["RowA"] / 5
df["ColB"] ** 3<jupyter_output><empty_output><jupyter_text>GroupingA core functionality of Pandas is the ability to group data frames and apply functions to each individual group. The function `*.groupby(...)` defines groups based on common labels. Aggregators applied to this grouped data frame are then applied to each group individually.<jupyter_code>df = pd.DataFrame({
    "Height": [178, 182, 158, 167, 177, 174, 175, 185], 
    "Age": [24, 33, 32, 18, 21, 28, 22, 29],
    "Gender": ["M", "M", "F", "F", "M", "F", "M", "F"]})
display(df)<jupyter_output><empty_output><jupyter_text>We can also select columns without disturbing the grouping<jupyter_code># grouping...
print(df.groupby('Gender').mean())

# group and filter 
df.groupby('Gender')[['Height']].mean()

# plot
df.groupby('Gender')[['Height']].mean().plot(kind='bar')<jupyter_output>Height    Age
Gender               
F        171.0  26.75
M        178.0  25.00<jupyter_text>A useful function is `size()`, which counts how large each of the groups is.<jupyter_code># size is actually calculating the number of M and F

df.groupby("Gender").size()

# size() return number of row
# count() return number of each element<jupyter_output><empty_output><jupyter_text>Unique and Duplicated ValuesTwo functions can help us identify unique and duplicated values within Series objects. They are aptly names `unique()` and `duplicated()`, respectively.   unique`*.unique()` returns only unique values of a Series object.<jupyter_code>s = pd.Series([1,2,3,2,3,4,3,5])
s.unique()

# df['Height'].unique() - series can use unique
# df[['Height']].unique() - dataframe can't use unique<jupyter_output><empty_output><jupyter_text>duplicated`*.duplicated()` identifies duplicated values in Series objects and returns a boolean Series. Entries that have already been seen are marked as `True` while new values are marked as `False`.<jupyter_code>s = pd.Series([1,2,3,2,3,4,3,5])
s.duplicated()

# duplicated has parameters first last and false<jupyter_output><empty_output><jupyter_text>When applied to Dataframes, `duplicated()` compares **ENTIRE ROWS** for duplicates.<jupyter_code>df = pd.DataFrame([
    ["Dog", 5], 
    ["Cat", 4], 
    ["Dog", 5], 
    ["Fish", 2], 
    ["Cat", 8]], 
    columns=["Animal", "Age"])
display(df)
display(df.duplicated())
# kalau nak focus on one column je
display(df.duplicated(subset = 'Age'))<jupyter_output><empty_output><jupyter_text>To remove duplicated rows from a data frame we could therefore do the following (just like in NumPy, booleans are negated with `~`)<jupyter_code># Just like mask, duplicate can be convert

df.loc[~df.duplicated()]
# get the unique values in a column

%timeit df.Animal.unique().tolist()
%timeit list(df.Animal.unique())<jupyter_output>77.6 µs ± 6.4 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)
79 µs ± 5.68 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)<jupyter_text>Exercises  Exercise 1Load the "cars.csv" dataframe and calculate the average miles per gallon (column "mpg")<jupyter_code>car = pd.read_csv('data/cars.csv')
car.head()
car['mpg'].mean()<jupyter_output><empty_output><jupyter_text>Exercise 2Cars can have 4, 6, or 8 cylinders (column "cyl"). Find the mean miles per gallon (column "mpg") for each of these classes **without** using the `groupby(...)` function.*BONUS: Write a function that takes the number of cylinders and returns the mean miles per gallon.*<jupyter_code>def mean_miles(cyl):
    return cars.loc[cars['cyl']==cyl]['mpg'].mean()

mean_miles(6)<jupyter_output><empty_output><jupyter_text>Exercise 3Repeat the above exercise but this time make use of the `groupby(...)` function.<jupyter_code>car.groupby('cyl')['mpg'].mean().sort_index()<jupyter_output><empty_output><jupyter_text>Exercise 4Your client has a proprietary metric for car engine quality that is calculated as $Q = \frac{hp}{wt^2}$. Calculate this metric for all cars and then find the average for cars with a manual (column "am" == 1) or automatic (column "am" == 0) transmission.**HINT** You can add the new metric as a column to your data frame via `cars["q_metric'] = ...`. Assignments to unknown column (or row) index names will result in new columns (or rows) to be appended to the data frame.<jupyter_code>car.head(2)
car['q_metric'] = car['hp']/(car['wt']**2)
car.head(2)
car.groupby('am')['q_metric'].mean()<jupyter_output><empty_output><jupyter_text>Merge Data FramesPandas data frames can be treated like SQL tables and joined.<jupyter_code>sales = pd.DataFrame({
    "Date": pd.date_range(start="2018-10-01", end="2018-10-07"), 
    "ItemID": ["A401", "C776", "A401", "FY554", "Y98R", "Y98R", "FY554"]})
sales
item_info = pd.DataFrame({
    "ID": ["A401", "C776", "FY554", "Y98R"],
    "Name": ["Toaster", "Vacuum Cleaner", "Washing Machine", "Clothes Iron"], 
    "Price": [25, 220, 540, 85]})
item_info
sales.merge(
    right=item_info, 
    how="inner", 
    left_on="ItemID", 
    right_on="ID")<jupyter_output><empty_output><jupyter_text>Merge types:- **Inner**: keep only rows with corresponding IDs found in *both* data frames- **Left**: use only rows with IDs found in the left data frame- **Right**: use only rows with IDs found in the right data frame- **Outer**: use all keys that are in at least one of the data frames. This is essentially the combination of left and right joinsMissing data will be replaced by `NaN` values ![Join Type]("images/sql-joins.png")<jupyter_code>sales = pd.DataFrame({
    "Date": pd.date_range(start="2018-10-01", end="2018-10-07"), 
    "ItemID": ["A401", "ZZZC776", "A401", "ZZZFY554", "Y98R", "Y98R", "FY554"]})
display(sales)
item_info = pd.DataFrame({
    "ID": ["A401", "C776", "FY554", "Y98R", "U1776"],
    "Name": ["Toaster", "Vacuum Cleaner", "Washing Machine", "Clothes Iron", "Computer"], 
    "Price": [25, 220, 540, 85, 899]})
display(item_info)
sales.merge(
    right=item_info, 
    how="inner", 
    left_on="ItemID", 
    right_on="ID")<jupyter_output><empty_output><jupyter_text>We can also merge on indices, either of one or both of the data frames<jupyter_code># merge using index

df1 = pd.DataFrame({'employee': ['Bob', 'Jake', 'Lisa', 'Sue'],
                    'group': ['Accounting', 'Engineering', 'Engineering', 'HR']})
df2 = pd.DataFrame({'employee': ['Lisa', 'Bob', 'Jake', 'Sue'],
                    'hire_date': [2004, 2008, 2012, 2014]})
display(df1, df2)
df1 = df1.set_index("employee")
df2 = df2.set_index("employee")
display(df1, df2)
df1.merge(df2, left_index=True, right_index=True)
df1 = pd.DataFrame({'employee': ['Bob', 'Jake', 'Lisa', 'Sue'],
                    'group': ['Accounting', 'Engineering', 'Engineering', 'HR']})
df2 = pd.DataFrame({'employee': ['Lisa', 'Bob', 'Jake', 'Sue'],
                    'hire_date': [2004, 2008, 2012, 2014]})
df2 = df2.set_index("employee")
display(df1, df2)
df1.merge(df2, left_on="employee", right_index=True)<jupyter_output><empty_output><jupyter_text>Exercises  Exercise 1Merge the three data frames so that we have all information available for Bob, Alice, Kevin, and Joshua in a single data frame<jupyter_code>salaries = pd.DataFrame(
    data=[["Bob", 5000], ["Alice", 4000], ["Kevin", 8000]], 
    columns=["Name", "Salary"])
departments = pd.DataFrame(
    data=[["Kevin", "IT"], ["Joshua", "Data Science"], ["Bob", "Data Science"]], 
    columns=["Name", "Department"])
supervisors = pd.DataFrame(
    data=[["IT", "Jeremy"], ["Data Science", "Darren"], ["Sales", "Yvonne"]], 
    columns=["Department", "Supervisor"])
display(salaries, departments, supervisors)
salaries.merge(
    right=departments, 
    how="outer", 
    
).merge(
    right=supervisors, 
    how="left", 
)<jupyter_output><empty_output><jupyter_text>Exploratory Data AnalysisA large part of our task as data scientists and analysts is to find patterns and interesting phenomena within data. We can make use of Pandas' vast assortment of functions to help us with this. The following exercises are designed to help you get an idea of the kind of questions you can answer with Pandas.This dataset describes all olympic athletes, the year they participated, the event they participated in, and whether they received a medal. The data is split into two files, `olympics_events.csv` and `olympics_games.csv`, describing the events and metadata of the games, respectively. The data has been adjusted from https://www.kaggle.com/heesoo37/120-years-of-olympic-history-athletes-and-results  Exercise 11. Load the two files, `olympics_events.csv` and `olympics_games.csv`, and display the first 10 lines of each data frame.<jupyter_code>event = pd.read_csv('data\olympics_events.csv')
event.head(3)
game = pd.read_csv('data\olympics_games.csv')
game.head(3)<jupyter_output><empty_output><jupyter_text>2. Merge the two data frames on the `GamesID` and `ID` columns. Join = **outer**. Drop the now-unnecessary id-columns afterwards.<jupyter_code>fullTab = event.merge(
    
    right=game, 
    how="outer", 
    left_on="GamesID", 
    right_on="ID"
)

fullTab.head(2)
df = fullTab.drop(columns = ['GamesID','ID'])
df.head(3)
fullTab.duplicated().sum()<jupyter_output><empty_output><jupyter_text>Exercise 2History lesson! Malaysia's olympic nationality code is `MAS`. Prior to this, the Federation of Malaya competed under the code `MAL`. Likewise, Sarawak and Sabah competed as North Borneo (`NBO`).1. In which years did the Federation of Malaya compete in the Olympics?<jupyter_code>df.loc[df['Nationality']=='MAL', 'Year'].unique()<jupyter_output><empty_output><jupyter_text>2. How many athletes did they send?<jupyter_code>df.loc[df['Nationality']=='MAL', 'Name'].nunique()<jupyter_output><empty_output><jupyter_text>3. Who were the first countries to participate in the Olympic games (as per this data set)?<jupyter_code>early_year = df.Year.min()
early_year
first_event = df.loc[df.Year == early_year, 'Nationality'
                    ].unique()
first_event<jupyter_output><empty_output><jupyter_text>4. How many men and women has Malaysia (`MAS`) sent to the Olympics in total? Keep in mind that athletes can participate in multiple events and multiple years. Each person should only ever be counted once.*HINT*: As we're only interested in athlete names and their genders, it's easiest to drop other columns and not have to worry about them. Create a new data frame but don't overwrite `events` as we'll need it for later exercises as well, though.<jupyter_code>gender = fullTab.loc[fullTab['Nationality'] == 'MAS', ['Name', 'Sex','Nationality']]
genderfix = gender.drop_duplicates()
genderfix.groupby('Sex')['Sex'].count()
df.loc[df.Nationality == 'MAS'].groupby('Sex')[['Name']].nunique()<jupyter_output><empty_output><jupyter_text>Exercise 31. How many men and women has Malaysia (`MAS`) sent to the Olympics each year?    Hint: This is a lot like the previous question except that athletes only count as duplicate now if they compete in multiple events in the same year. An athlete competing in multiple years is no longer duplicate.<jupyter_code>participation = fullTab.loc[fullTab['Nationality'] == 'MAS', ['Name', 'Sex','Nationality','Year']]
participation.head(3)
participation.groupby(['Year','Sex']).count().T
df.loc[df.Nationality == 'MAS'].groupby(['Year','Sex'])[['Name']].nunique().sort_index(ascending = False)<jupyter_output><empty_output><jupyter_text>Exercise 4Let's start looking at some of the numerical data!1. How many gold medals has each country won? How about Malaysia (`MAS`)?<jupyter_code>medals = df.loc[df['Medal'].notna()]
pingat = medals.groupby(['Nationality','Medal']).size()
pingat.loc[('MAS')]<jupyter_output><empty_output><jupyter_text>Exercise 51. What is the median age of gold medalists?<jupyter_code>df.loc[ df.Medal == 'Gold']['Age'].median()<jupyter_output><empty_output><jupyter_text>2. Look at only swimmers. How has the mean weight of all competitors changed throughout the years? Use `*.plot()` to get a visual sense of the trend.<jupyter_code>df.loc[df['Sport']=='Swimming'].groupby('Year')['Weight'].mean().plot()<jupyter_output><empty_output><jupyter_text>3. What is the mean and standard deviation of the BMI of athletes in each sports discipline? The BMI can be computed as     $$BMI = Weight~/~\left(\frac{Height}{100}\right)^2$$    with the values in this dataset. To solve this question, break it down into individual steps:    - Calculate the BMI for all athletes    - Group by 'Sport'    - Calculate the mean and standard deviation of the BMI of the grouped data frame        *Hint*: Use `*.agg([..., ...])` to apply "mean" and "std" (standard deviation) simultaneously.<jupyter_code>df['BMI'] = df.Weight/(df.Height/100)**2
df.head(3)
df.groupby('Sport').BMI.agg(['mean','std'])<jupyter_output><empty_output><jupyter_text>Exercise 61. What country has the most gold medals in wrestling?<jupyter_code>df.loc[
    (df.Sport == 'Wrestling') & 
    (df.Medal == 'Gold')
].groupby('Nationality').Medal.size().idxmax()<jupyter_output><empty_output><jupyter_text>2. How many different types of events have ever been held for fencing?<jupyter_code>df.loc[
    df.Sport == 'Fencing',
    'Event'
].nunique()<jupyter_output><empty_output><jupyter_text>Summary Notes  Series<jupyter_code># CONVERT LIST AND DICTIONARY TO SERIES
# pd.Series()
# SELECT ELEMENT IN SERIES
# variable.iloc[] follow python convention 
# variable.loc[] follow automatic/ assigned index 
# COMBINE SERIES
# s1.append(s2) - s1 goes first in the series and vice versa<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1321486769612788, 'fraction_numerical': 0.02609865238871214, 'mean_word_length': 4.678907168037603, 'pattern_counts': {'":': 29, '<': 301, '<?xml version=': 0, '>': 307, 'https://': 4, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '76', 'dir': 'jupyter-structured-clean-dedup', 'id': '350678', 'license': 'MIT', 'n_tokens_mistral': 13635, 'n_tokens_neox': 12610, 'n_words': 4112, 'path': 'Python_Day5_SC.ipynb', 'repo_name': 'faeznizam/python_for_data_analysis', 'size': '38678'}