<jupyter_start><jupyter_text>Data Manipulation with Pandas   Program so far ***- Python Basics- Python Programming Constructs- Data Structures- Functions- Object Oriented Programming in Python- NumPy- Pandas  What are we going to learn today?***- How to carry out basic operations on a DataFrame?- How to read data into a DataFrame?- How to carry out Data Manipulation and Transformations using inbuilt functions?- The importance and usage of Pivot Tables and Group By.- How to merge two or more DataFrame into one single DataFrame?  DataFrame Basics***We'll talk about- How to create a DataFrame, the primary data structure in pandas - How to find the shape and rank of the created or existing DataFrames- How to read DataFrames from a file- What are indexes, and how do they work in the domain of Pandas DataFrames  What are DataFrames?***DataFrames are a way to store data in rectangular grids that can easily be overviewed. Each row of these grids corresponds to measurements or values of an instance, while each column is a vector containing data for a specific variable. This means that a data frame’s rows do not need to contain, but can contain, the same type of values: they can be numeric, character, logical, etc. Data frames in Python come within the Pandas library, and they are defined as a two-dimensional labeled data structures with columns of potentially different types. In general, you could say that the Pandas data frame consists of three main components: the data, the index, and the columns.   How do I create DataFrames?***DataFrames are 2 dimensional objects that can have a number of different columns for each attribute. You can create DataFrames manually or from one of the following objects:- Dict of 1D ndarrays, lists, dicts, or Series- 2-D numpy.ndarray- A Series- Another DataFrame **Creating DataFrames manually**The function that you will use is the Pandas Dataframe() function: it requires you to pass the data that you want to put in, the indices and the columns.Remember that the data that is contained within the data frame doesn’t have to be homogenous.<jupyter_code>import pandas as pd
import numpy as np

df = pd.DataFrame([[1, 2, 3],
                   [3, 4, 5],
                   [5, 6, 7],
                   [7, 8, 9]])
df
df = pd.DataFrame([[1, 2, 3], [3, 4, 5], [5, 6, 7], [7, 8, 9]])

print("Shape:", df.shape)
print("Index:", df.index)

df<jupyter_output>Shape: (4, 3)
Index: RangeIndex(start=0, stop=4, step=1)<jupyter_text>**Understanding the Index**Before you start with adding, deleting and renaming the components of your DataFrame, you first need to know how you can select these elements.This is where Indexes come into play, just the way you can use an idex page in a book to locate your chapters, you can use the loc() or iloc() function in pandas to access data in particular columns of your DataFrame.We will learn about how these functions work and their subtle differences in the next sections.<jupyter_code>df2 = pd.DataFrame([[1, 2, 3], [3, 4, 5], [5, 6, 7], [7, 8, 9]],
                   index=['a', 'b', 'c', 'd'], columns=['x', 'y', 'z'])

print("Shape:", df.shape)
print("Index:", df.index)

df2<jupyter_output>Shape: (4, 3)
Index: RangeIndex(start=0, stop=4, step=1)<jupyter_text>The Weather Dataset: Reading DataFrames from Files***The Weather Dataset is a time-series data set with per-hour information about the weather conditions at a particular location. It records Temperature, Dew Point Temperature, Relative Humidity, Wind Speed, Visibility, Pressure, and Conditions.This data is available as a CSV file. We are going to use Pandas DataFrames and analyse this dataset.<jupyter_code># Read the data into a data frame

weather_df = pd.read_csv("../data/weather_2012.csv") # 

print("Shape:", weather_df.shape)
print("Index:", weather_df.index)
weather_df['Date/Time'].head()<jupyter_output><empty_output><jupyter_text>**Let's convert the `Date/Time` column datatype from `object` to `timestamp` so that we can access the month directly using the attribute `dt.month`**<jupyter_code>weather_df['Date/Time'] = pd.to_datetime(weather_df['Date/Time'])<jupyter_output><empty_output><jupyter_text>How to Analyze DataFrames?***The following functions help you understand and explore summaries of your data without having to view the whole DataFrame  `.info()`***Provides a summary of a DataFrame: rows, columns, data types of columns (if automatically detected) and the memory usage.For detailed summaries of the the DataFrame, you can pass optional arguments verbose=True and null_counts=True to the .info() method to output information for all of the columns<jupyter_code>weather_df.info() # Bring the cursor inside the brackets of info() and hit shift+tab & see what you get.
                  # This will work for any function in Pandas<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 8784 entries, 0 to 8783
Data columns (total 8 columns):
 #   Column              Non-Null Count  Dtype         
---  ------              --------------  -----         
 0   Date/Time           8784 non-null   datetime64[ns]
 1   Temp (C)            8784 non-null   float64       
 2   Dew Point Temp (C)  8784 non-null   float64       
 3   Rel Hum (%)         8784 non-null   int64         
 4   Wind Spd (km/h)     8784 non-null   int64         
 5   Visibility (km)     8784 non-null   float64       
 6   Stn Press (kPa)     8784 non-null   float64       
 7   Weather             8784 non-null   object        
dtypes: datetime64[ns](1), float64(4), int64(2), object(1)
memory usage: 549.1+ KB<jupyter_text>`.head()`***It is used to preview a part of a large DataFrame, similar to the linux `head` command. This reduces time and resources required if  whole DataFrame was to be fetched instead. Shows the first N rows in the data (by default, N=5).<jupyter_code>weather_df.head(5)<jupyter_output><empty_output><jupyter_text>`.index`***This attribute provides the `index` of the dataframe.Indexing identifies data using known indicators that allows intuitive getting and setting of subsets of the data set.A major advantage of Pandas over NumPy is that each of the columns and rows has a label. Working with column positions is possible, but it can be hard to keep track of which number corresponds to which column.We can work with labels using the **pandas.DataFrame.loc** method, which allows us to index using labels instead of positions.<jupyter_code>weather_df.index<jupyter_output><empty_output><jupyter_text>`.unique()`***This method, which belongs to the `Series` object, can be useful when trying to identify unique values in a column.- Uniques are returned in order of appearance. - It is significantly faster than numpy.unique and includes N/A values<jupyter_code>weather_df['Weather'].unique()<jupyter_output><empty_output><jupyter_text><img src="../images/icon/ppt-icons.png" alt="mini project" style="width: 100px;float:left; margin-right:15px"/>  Mini - Challenge - 1***Find all the unique Wind Speed values recorded in the dataset<jupyter_code># Code your solution here
weather_df['Wind Spd (km/h)'].unique()<jupyter_output><empty_output><jupyter_text>`.nunique()`***This method belongs to the `Series` object and can be useful when trying to identify the number of unique values in a column. - Excludes NA values by default- Always returns an integer value<jupyter_code>weather_df['Weather'].nunique()<jupyter_output><empty_output><jupyter_text>`.value_counts()`***This method, which belongs to the `Series` object, can be useful when trying to identify unique values and their counts in a column- The resulting object will be in descending order so that the first element is the most frequently-occurring element. - Excludes NA values by default.<jupyter_code>weather_df['Weather'].value_counts()<jupyter_output><empty_output><jupyter_text><img src="../images/icon/ppt-icons.png" alt="mini project" style="width: 100px;float:left; margin-right:15px"/>  Mini-Challenge - 2***Find the number of times when the weather was exactly 'Clear'<jupyter_code># code your solution
weather_df['Weather'].value_counts()[3]<jupyter_output><empty_output><jupyter_text>How can we get the count for Clear ?***In order to get only the number of times when the weather was exactly 'Clear' , we need to used Square brackets after `.value_counts` eg. `.value_counts()[3]` <img src="../images/icon/ppt-icons.png" alt="mini project" style="width: 100px;float:left; margin-right:15px"/>  Mini-Challenge - 3*****Try one more time..!!**Find the number of times when the wind speed was exactly 4 km/h<jupyter_code># Your solution here
weather_df['Wind Spd (km/h)'].value_counts()[9]<jupyter_output><empty_output><jupyter_text>Data Manipulation : Gets you desired results***The true power of the Pandas DataFrame is the ease and flexibility of manipulating data to get your desired results.Pandas is best at handling tabular data sets comprising different variable types (integer, float, double, etc.). In addition, the pandas library can also be used to perform even the most naive of tasks such as loading data or doing feature engineering on time series data.  Selection (Part 1)***How do you select particular rows/columns from the DataFrame ?The DataFrame object supports indexing operations just like the Python `list` class and the Pandas Series object, but is much faster and more powerful.Note that when you extract a single row or column, you get a one-dimensional object as output. That is called a pandas Series. The values on the left are just labels taken from the dataframe index. On the other hand, when we extract portions of a pandas dataframe, we get a two-dimensional DataFrame type of object. Something to keep in mind for later.  How to get the Weather column from the "weather_df" dataframe<jupyter_code>col = weather_df['Weather']

print(type(col))
col.head()<jupyter_output><class 'pandas.core.series.Series'><jupyter_text>How to get the Weather and Temperature columns from the "weather_df" dataframe<jupyter_code>two_cols = weather_df[['Weather', 'Temp (C)']] # Take a good look at those brackets. There are two sets of them
                                               # to access more than one columns. 
print(type(two_cols))
two_cols.head()<jupyter_output><class 'pandas.core.frame.DataFrame'><jupyter_text>**Keep in mind Whenever you need to take more than two columns need to put double sqaure  like above example**  **Get the first 25 rows from the "weather_df" dataframe*******Important**: This slicing would work even if the row index had non-numeric labels, because slicing works here the same way as a list<jupyter_code>weather_df[:25]<jupyter_output><empty_output><jupyter_text>**How to get the first 3 alternating rows from the "weather_df" dataframe, but only the Visibility and Relative Humidity columns**<jupyter_code># solution 1
result1 = weather_df[:6:2][['Rel Hum (%)', 'Visibility (km)']]

# solution 2
result2 = weather_df[['Rel Hum (%)', 'Visibility (km)']][:6:2]

# are they the same?
result1 == result2<jupyter_output><empty_output><jupyter_text>So which of the two solutions should you use?*****Answer**: Neither. Because we're indexing more than once (Chained Indexing).When you use chained indexing, the order and type of the indexing operation partially determine whether the result is a slice into the original object, or a copy of the slice.Let's analyse (break down) one of the above solutions.<jupyter_code># first indexing
df1 = weather_df[:6:2]

# second indexing
df2 = df1[['Rel Hum (%)', 'Visibility (km)']]<jupyter_output><empty_output><jupyter_text>While both results are correct in this **read-only** case, chained indexing may give unpredictable behaviours when **writing** to a dataframe.This is because indexing could either return a "view" (of slices of the dataframe), or a copy of the dataframe.  Selection (Part 2)***Pandas provides a powerful way to work with both rows and columns together, optionally using their label indices or numeric indices.- **`.loc :`**Purely label-location based indexer for selection by label (but may also be used with a boolean array).**Important: If you use slicing in loc, it will return the end index as well**- **`.iloc:`**Purely integer-location based indexing for selection by position (but may also be used with a boolean array).Allowed inputs are:- A single label, e.g. 5 or 'a'- A list or array of labels, e.g. ['a', 'b', 'c']- A slice object with labels, e.g. 'a':'f' <img src="../images/icon/ppt-icons.png" alt="mini project" style="width: 100px;float:left; margin-right:15px"/>  Mini-Challenge - 4***Get the first 3 alternating rows from the `weather_df` dataframe, but only the Visibility and Relative Humidity columns<jupyter_code># Using loc or iloc
# NOTE: slice is `:5:2`, not 6 !
weather_df[['Visibility (km)','Rel Hum (%)']].iloc[:3]<jupyter_output><empty_output><jupyter_text>Filtering***![Filter](../images/filters1.jpg)Image Source:https://pixabay.com/en/yashica-filter-camera-vintage-711794/Anything that takes in data, processes it, and provides an outputInput Data ⟶ Filter ⟶ Output DataFiltering rows of a DataFrame is an almost mandatory task for Data Analysis with Python. Given a Data Frame, we may not be interested in the entire dataset but only in specific rows.  Find all instances when snow was recorded***Whether or not it snowed can be found out using the Weather column.Note that this routine does not filter a dataframe on its contents. The filter is applied to the labels of the index.<jupyter_code>weather_df['Weather'].unique()
# Basically, we want a way to "filter out" records that have the word "snow" (case insensitive) in the last column

snowed_filter = weather_df['Weather'].str.lower().str.contains('snow')
weather_df[snowed_filter]<jupyter_output><empty_output><jupyter_text>**Find all instances when wind speed was above 24 and visibility was 25**<jupyter_code>df = weather_df[(weather_df['Wind Spd (km/h)'] > 24) & (weather_df['Visibility (km)']== 25)]
df.head()<jupyter_output><empty_output><jupyter_text>Summary / Cheatsheet: Selection/Indexing/Filtering***This is a handy reminder for what syntax will get what result.Syntax | Function | Remarks:--- | :--- | :---**`df['some_label']`** |  Get the (single) Column referenced by name `some_label` | A **str** is provided**`df[['label1', 'label2']]`** | Get multiple columns referenced by given names | A **list** is provided **`df[start:end:step]`** | Get corresponding rows (same as list slicing) | A **slicing operator** is provided**`df[boolean array/df]`** | Get corresponding rows (same as list slicing) | A **filter object** is provided**`df.loc [row_sel, col_sel]`** | Select specified rows and columns (by labels) | **`df.iloc[row_sel, col_sel]`** | Select specified rows and columns (by index) |  ![](../images/Week_4_R_vs_Python.jpeg)Both R and Python are in parity to each other regardless of your problem. But, if you want something which is multi purpose, growing yet flexible for data analysis then Python is the winner of the game!. There are a few more reasons that give Python an edge over R.Read here to find out more: https://greyatom.com/blog/2017/06/python-or-r-what-to-choose-in-data-science/   Working with Columns***- We will learn how to carry out Series operations on DataFrame Columns- How to add or update columns within a DataFrame- How to rename specific columns- How to delete or drop a column that is no longer required for analysis  Series Operations***A series is a one-dimensional ndarray with axis labels (including time series).Labels need not be unique but must be a hashable type. The object supports both integer- and label-based indexing and provides a host of methods for performing operations involving the index. Statistical methods from ndarray have been overridden to automatically exclude missing data (currently represented as NaN).Operations between Series (+, -, /, , *) align values based on their associated index values– they need not be the same length. The result index after operation will be the sorted union of the two indexes. Add 10 to the values in the column "Wind Spd (km/h)" using the "+" operator<jupyter_code>add_10 = weather_df["Wind Spd (km/h)"] + 10
add_10.head()<jupyter_output><empty_output><jupyter_text>Multiply the values in the 'Visibility (km)' column by 2 using the asterisk (*) operator<jupyter_code>mult_2 = weather_df['Visibility (km)'] * 2
mult_2.head()<jupyter_output><empty_output><jupyter_text>Add the "Temp (C)" and "Dew Point Temp (C)" columns as series "temperature"THis can be done by simply passing the column names and using the "+" operator<jupyter_code>temperature = weather_df["Temp (C)"] + weather_df["Dew Point Temp (C)"]
temperature.head()<jupyter_output><empty_output><jupyter_text>Apply / Call Functions  `.apply()`***You can pass any number of arguments to the function that apply is calling through either unnamed arguments, passed as a tuple to the args parameter, or through other keyword arguments internally captured as a dictionary by the kwds parameter.Invoke function on values of Series. Can be a NumPy function that applies to the entire Series or a Python function that only works on single values<jupyter_code># Applying custom functions

def times2(value):
    return value * 2

t2 = weather_df['Visibility (km)'].apply(times2)
t2.head()<jupyter_output><empty_output><jupyter_text>`.describe()`***The above function is used to summarize the  central tendency, dispersion and shape of a dataset’s distribution, excluding NaN values.<jupyter_code>weather_df['Visibility (km)'].describe()<jupyter_output><empty_output><jupyter_text>Adding/Updating Columns<jupyter_code>visibility_in_meter = weather_df["Visibility (km)"] * 1000  
weather_df["Visibility (m)"] = visibility_in_meter

weather_df.head()<jupyter_output><empty_output><jupyter_text>Renaming Columns  `.rename()`***Alter Series index labels or name. It will replace the existing names with the names you provide, in the order you provide.You can also assign the names by index.The rename() method allows you to relabel an axis based on some mapping (a dict or Series) or an arbitrary function.<jupyter_code># Notice the "inplace=True" parameter. This means the renaming has been assigned in the old DataFrame itself

weather_df.rename(columns={'Visibility (m)': 'Visibility (meters)'}, inplace=True)
weather_df.head()<jupyter_output><empty_output><jupyter_text>Deleting Columns  `.drop()`***Return new object with labels in requested axis removed.Note that Pandas uses zero based numbering, so 0 is the first row, 1 is the second row, etc.You can select ranges relative to the top or drop relative to the bottom of the DataFrame as well.- Note: Specifying both labels and index or columns will raise a ValueError.<jupyter_code># Since we have not mentioned inplace=True, it returns a new dataframe.
weather_df.drop(labels=['Visibility (meters)'], axis=1).head(3)
# Alternatively, you can also write
# del weather_df2['Visibility (meters)']<jupyter_output><empty_output><jupyter_text>Sorting***  `.sort_values()`***Sort by the values along either axis, in a user specified order. The order can be specified by selecting true or false for the "ascending" parameter.<jupyter_code>sorted_by_temp = weather_df.sort_values('Temp (C)', ascending=False)  # can be inplace as well
sorted_by_temp.head()<jupyter_output><empty_output><jupyter_text>Which were the top 10 hottest values and their counts?<jupyter_code>sorted_value_counts = weather_df['Temp (C)'].value_counts().sort_values(ascending=False)
sorted_value_counts.iloc[:10]<jupyter_output><empty_output><jupyter_text>What is the mean temperature recorded by month?  `.mean()`***- It is an aggregation function used to combine multiple values- Return the mean of the values for the requested axis- Returns scalar or Series (if level specified)<jupyter_code># One Solution
mean_temperatures = {}

for month in range(1, 13):
    mean_temperatures[month] = weather_df.loc[weather_df['Date/Time'].dt.month == month, 'Temp (C)'].mean()

pd.Series(mean_temperatures)<jupyter_output><empty_output><jupyter_text>Pivot Tables : Excellent way to Summarize your Data!***- A pivot table is a tool that allows you to reorganize and summarize selected columns and rows of data in a dataframe - Pivot tables provide an easy way to subset by one column and then apply a calculation like a sum or a mean - Pivot tables first groups and only then applies a calculation<jupyter_code>data = {
    'A': ['foo','foo','foo','bar','bar','bar'],
    'B': ['one','one','two','two','one','one'],
    'C': ['x','y','x','y','x','y'],
    'D': [1, 3, 2, 5, 4, 1]
}

df = pd.DataFrame(data)
df
pivot_df = df.pivot_table(
                values='D',      # We want to aggregate the values of which column?
                index='A',       # We want to use which column as the new index?
                columns=['C'],   # We want to use the values of which column as the new columns? (optional)
                aggfunc=np.sum)  # What aggregation function to use ?


pivot_df
# convert it back to a simple index

pivot_df.reset_index()<jupyter_output><empty_output><jupyter_text>What is the mean temperature recorded by month?<jupyter_code>mean_temperature_df = weather_df.pivot_table(values='Temp (C)', index=weather_df['Date/Time'].dt.month, aggfunc=np.mean)
mean_temperature_df # the numbers 1 to 12 denote the respective months from January to December.<jupyter_output><empty_output><jupyter_text>Group By***The groupby method allows you to group rows of data together and call aggregate functions that applies to the whole group.Any groupby operation involves one of the following operations on the original object. They are −- Splitting the Object- Applying a function- Combining the resultsIn many situations, we split the data into sets and we apply some functionality on each subset. In the apply functionality, we can perform the following operations −- Aggregation − computing a summary statistic- Transformation − perform some group-specific operation- Filtration − discarding the data with some condition<jupyter_code>mean_temperature_df2 = weather_df.groupby(weather_df['Date/Time'].dt.month).agg(np.mean).reset_index()
mean_temperature_df2<jupyter_output><empty_output><jupyter_text>Concat, Merge and Join ***There are 3 key ways of combining DataFrames together:- **Concatenation**: Concatenation glues together DataFrames. Keep in mind that dimensions should match along the axis you are concatenating on - **Merging**: The merge function allows you to merge DataFrames together using a similar logic as merging SQL Tables together- **Join**: Join is a convenient method for combining the columns of two potentially differently-indexed DataFrames into a single result DataFrame  ConcatMany a time, we are required to combine different arrays. So, instead of typing each of their elements manually, you can use array concatenation to handle such tasks easily.<jupyter_code>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'],
    'E': ['D8', 'D9', 'D10', 'D11']
}, index=[8, 9, 10, 11])
df1
# if you don't specify an axis, it defaults to axis=0, which means it appends to rows
pd.concat([df1, df2, df3])
# axis=1 means concat along columns

pd.concat([df1, df2, df3], axis=1)<jupyter_output><empty_output><jupyter_text>Join***Simply join two DFs having potentially different row indicesYou can do both inner as well as outer joins using the join function in pandas- Parameters {‘inner’, ‘outer’}, default ‘outer’. Outer for union and inner for intersection.<jupyter_code># Join
left_df = pd.DataFrame({
    'A': ['A0', 'A1', 'A2'],
    'B': ['B0', 'B1', 'B2']
}, index=['K0', 'K1', 'K2']) 

right_df = pd.DataFrame({
    'C': ['C0', 'C2', 'C3'],
    'D': ['D0', 'D2', 'D3']
}, index=['K0', 'K2', 'K3'])

left_df.join(right_df, how='outer')<jupyter_output><empty_output><jupyter_text>MergeMany a times you will be working with multiple dataframes all at once.The merge function allows them to be combined into a single data frame<jupyter_code># Merging on multiple keys
left = pd.DataFrame({
    'key1': ['K0', 'K0', 'K1', 'K2'],
    'key2': ['K0', 'K1', 'K0', 'K1'],
    'A': ['A0', 'A1', 'A2', 'A3'],
    'B': ['B0', 'B1', 'B2', 'B3']
})
    
right = pd.DataFrame({
    'key1': ['K0', 'K1', 'K1', 'K2'],
    'key2': ['K0', 'K0', 'K0', 'K0'],
    'C': ['C0', 'C1', 'C2', 'C3'],
    'D': ['D0', 'D1', 'D2', 'D3']
})

pd.merge(left, right, how='outer', on=['key1', 'key2'])
# other options are 'inner', 'left', 'right'

pd.merge(left, right, how='left', on=['key1', 'key2'])<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11065393680341949, 'fraction_numerical': 0.016018855111253148, 'mean_word_length': 4.895902025435705, 'pattern_counts': {'":': 0, '<': 155, '<?xml version=': 0, '>': 156, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '38', 'dir': 'jupyter-structured-clean-dedup', 'id': '548598', 'license': 'MIT', 'n_tokens_mistral': 7707, 'n_tokens_neox': 7277, 'n_words': 3071, 'path': 'Week-04-Pandas-Advanced/notebooks/Week-4 notebook.ipynb', 'repo_name': 'bibekuchiha/26-Weeks-Of-Data-Science-master', 'size': '25033'}