CHAPTER -3
1. Write the statement to install the python connector to connect MySQL i.e.
pymysql.
The statement required to install the pymysql database driver is:
pip install pymysql
2. Explain the difference between pivot() and pivot_table() function.
The main difference lies in how they handle duplicate data:
pivot(): This function is used to reshape and create a new DataFrame. It requires
that the index/columns specified have unique entries. If there are duplicate values for
the specified columns, the pivot() function will result in a ValueError.
pivot_table(): This function works similarly to pivot(), but it handles rows with
duplicate entries for the specified columns by aggregating the values. It uses an
aggregate function (aggfunc), which defaults to mean, to combine the multiple entries
into a single value.
3. What is sqlalchemy?
sqlalchemy is a library that is used to interact with the MySQL database by providing the
necessary credentials. This library provides the function create_engine() which is essential
for establishing the connection between Python and the MySQL database.
4. Can you sort a DataFrame with respect to multiple columns?
Yes, a DataFrame can be sorted based on multiple columns. When multiple columns are
specified, the DataFrame is sorted first by the initial column, and then subsequent columns
are used as tie-breakers for rows where values in the primary sort column are the same.
5. What are missing values? What are the strategies to handle them?
Missing Values: A missing value occurs when a value corresponding to a column is
not present in a DataFrame. They are denoted by NaN.
Strategies to Handle Them: The two most common strategies are:
1. Dropping the object: Removing the entire row (object) or column having the
missing value(s) using the dropna() function.
2. Filling/Estimating the missing value: Replacing the missing value with an
appropriate estimation or approximation. This can be done using the fillna()
function, replacing NaN with values such as zero, one, the mean, or the value
just before (method='pad') or after (method='bfill') the missing value.
6. Define the following terms: Median, Standard Deviation and Variance.
Median: The middle value of the data. The function [Link]() displays
the median of the values of each column of a DataFrame.
Variance: The average of squared differences from the mean. The function
[Link]() is used to display the variance.
Standard Deviation (std): The standard deviation is calculated as the square root of
the variance. The function [Link]() returns this value.
7. What do you understand by the term MODE? Name the function which is
used to calculate it. (Page 103)
MODE: The mode is defined as the value that appears the most number of times in
a data set.
Function: The function used to calculate the mode is [Link]().
8. Write the purpose of Data aggregation. (Page 103)
The purpose of data aggregation is to transform the dataset and produce a single numeric
value from an array. Aggregate functions like max(), min(), sum(), count(), std(), and
var() are used for this purpose.
9. Explain the concept of GROUP BY with help on an example. (Page 103)
The [Link] BY() function is used to split the data into groups based on
specified criteria. It operates using a split-apply-combine strategy.
Explanation:
1. Split: The original DataFrame is divided into separate groups based on the unique
values in the column specified (e.g., grouping the marks data by 'Name').
2. Apply: A function (like sum, mean, or max) is applied to each of these individual
groups.
3. Combine: The results from the groups are combined to form a new aggregated
DataFrame.
Example: To find the mean marks of Mathematics scored by each student (Name):
>>> [Link] BY(by='Name')['Maths'].agg(['mean'])
This statement first splits the data by Name, then applies the mean
function to the Maths
column within each name group, and finally combines the results.
10. Write the steps required to read data from a MySQL database to a
DataFrame. (Page 103)
Reading data from MySQL to a Pandas DataFrame is known as importing data. The steps are:
1. Install Drivers/Libraries: Install the necessary libraries, particularly pymysql (the
database driver) and sqlalchemy (used to interact with MySQL).
2. Establish Connection: Use sqlalchemy.create_engine() with the appropriate
connection string (including driver, username, password, host, port, and database
name) to establish a connection and obtain an engine object.
3. Fetch Data: Use one of the Pandas SQL functions, such as
pandas.read_sql_query(query, sql_conn) or
pandas.read_sql_table(table_name, sql_conn), passing the query/table name
and the engine object (sql_conn) to load the data directly into a DataFrame.
11. Explain the importance of reshaping of data with an example. (Page 103)
Importance: Reshaping is the process of changing the arrangement (shape) of the dataset
into rows and columns to make it suitable for analysis problems. It transforms the structure
to make data more readable and easy to analyse.
Example Utility (using pivot()): If you have sales data where each year/store entry is a
separate row (long format), reshaping the data using pivot() allows you to set Store as the
index and Year as the columns. This rearrangement makes complex queries, such as finding
the maximum sale value by a store over all years, simpler and more intuitive to perform.
12. Why estimation is an important concept in data analysis? (Page 103)
Estimation (specifically of missing values) is important because missing values are a
common occurrence in real-world datasets and create problems during data analysis.
If rows with missing values are simply removed using dropna(), the size of the
dataset is reduced, leading to a loss of information.
By replacing missing values with an estimation (like the mean, previous value, or
zero), the analysis can proceed. While the results may not be the actual results, they
serve as a good approximation of actual results.
13. Assuming the given table: Product. Write the python code for the
following:
Item Company Rupees USD
TV LG 12000 700
TV VIDEOCON 10000 650
TV LG 15000 800
AC SONY 14000 750
a) To create the data frame for the above table.
>>> import pandas as pd
>>> data = {'Item':['TV','TV','TV','AC'],
'Company':['LG','VIDEOCON','LG','SONY'],
'Rupees':,
'USD':
}
>>> df = [Link](data)
>>> print(df)
b) To add the new rows in the data frame. (Assuming one new product: Pen, ABC
Company, 50 Rupees, 1 USD)
>>> [Link][len(df)] = ['Pen', 'ABC', 50, 1]
c) To display the maximum price of LG TV (in Rupees).
>>> df_LG_TV = df[([Link] == 'TV') & ([Link] == 'LG')]
>>> df_LG_TV['Rupees'].max()
# Output: 15000
(Uses slicing and max())
d) To display the Sum of all products (in Rupees and USD).
>>> df[['Rupees', 'USD']].sum()
(Uses slicing and sum())
e) To display the median of the USD of Sony products.
>>> df[[Link] == 'SONY']['USD'].median()
# Output: 750 (based on original table)
(Uses slicing and median())
f) To sort the data according to the Rupees and transfer the data to MySQL.
>>> from sqlalchemy import create_engine
>>> df_sorted = df.sort_values(by=['Rupees']) # Sorting data
# Establishing connection (assuming engine is set up)
>>> engine =
create_engine('mysql+pymysql://username:password@localhost:3306/DB_NAME')
>>> df_sorted.to_sql('product_sorted', engine, if_exists="replace",
index=False) # Exporting data
g) To transfer the new dataframe into the MySQL with new values. (Assuming the new
rows are contained in df and we are adding them to an existing table named 'product')
>>> engine =
create_engine('mysql+pymysql://username:password@localhost:3306/DB_NAME')
>>> df.to_sql('product', engine, if_exists="append", index=False)
(Uses to_sql() with if_exists="append")
14. Write the python statement for the following question on the basis of given
dataset:
(Note: Since the dataset for Q14 is not provided in the source material, the code below
assumes a DataFrame df exists with columns like 'Name', 'Degree', 'Stream', and 'Marks',
potentially containing NaN values).
a) To create the above DataFrame.
# Assuming required imports and data structure (using [Link] for missing
values)
>>> import pandas as pd
>>> import numpy as np
# Example data structure
# data = {'Name':['A','B','C'], 'Degree':['BCA','MBA','BCA'], 'Stream':
['CS','Mgt','CS'], 'Marks':[80, 75, [Link]]}
# df = [Link](data)
b) To print the Degree and maximum marks in each stream.
>>> [Link] BY(['Degree', 'Stream'])['Marks'].max()
(Uses GROUP BY and max() aggregation)
c) To fill the NaN with 76.
>>> [Link](76, inplace=True)
(Uses fillna() to estimate missing values)
d) To set the index to Name.
>>> df.set_index('Name', inplace=True)
(Uses set_index() to alter the index)
e) To display the name and degree wise average marks of each student.
>>> [Link] BY(['Name', 'Degree'])['Marks'].mean()
(Uses GROUP BY on multiple attributes and mean aggregation)
f) To count the number of students in MBA.
>>> len(df[[Link] == 'MBA'])
# Alternatively, if 'Name' is the row index:
# >>> df[[Link] == 'MBA']['Degree'].count()
(Uses slicing and len() or count())
g) To print the mode marks BCA.
>>> df[[Link] == 'BCA']['Marks'].mode()
(Uses slicing to filter the data and the mode() function)