Data Handling using Pandas - II — NCERT Solutions

The statement to install the MySQL connector pymysql is:

pip install pymysql

pivot() is used to reshape a DataFrame when the entries for the specified index and columns are unique. It rearranges data from rows into a new table.

pivot_table() works like pivot(), but it can handle duplicate entries by applying an aggregate function such as mean, sum, max, or min. The default aggregate function is mean.

So, the main difference is:

• pivot() does not work with duplicate values for the chosen index/columns.
• pivot_table() can reshape data even when duplicates are present, by aggregating them.

sqlalchemy is a library used to interact with the MySQL database by providing the required credentials. It also provides the create_engine() function to establish the connection.

Yes, a DataFrame can be sorted with respect to multiple columns. In sort_values(), we pass a list of column names in the by parameter.

For example, if marks in Science are the same, then sorting can be done using Hindi as the next column:

dfUT3 = df[df.UT == 3]
print(dfUT3.sort_values(by=['Science', 'Hindi']))

Here, the data is first sorted by Science and then by Hindi.

A missing value is a value that is not present in a DataFrame. It is denoted by NaN.

The two main strategies to handle missing values are:

1. Drop the object (row) having missing values.
2. Fill/estimate the missing value using an appropriate value, such as 0, 1, previous value, next value, average, minimum, or maximum.

• Median: the middle value of the data after arranging it in order. If there are an even number of values, it is the average of the two middle values.
• Standard Deviation: the measure of how much the values vary from the mean; it is the square root of variance.
• Variance: the average of squared differences from the mean.

Mode is the value that appears the most number of times in a dataset. The function used to calculate it in Pandas is mode().

The purpose of data aggregation is to transform a dataset and produce a single numeric value from an array. It helps summarize data using functions like max(), min(), sum(), count(), std(), and var().

GROUP BY is used to split data into groups based on some criteria, then apply a function to each group, and combine the results.

Example: if we group the marks DataFrame by Name, we can find the first entry, size, or sum for each student.

g1 = df.groupby('Name')
print(g1.first())
print(g1.size())

This creates separate groups for Raman, Zuhaire, Ashravy and Mishti, and then operations can be performed group-wise.

The steps to read data from a MySQL database to a DataFrame are:

1. Install and import the required libraries, such as pymysql and sqlalchemy.
2. Create a connection engine using create_engine().
3. Use one of the Pandas functions to read the table/query, such as:

• pd.read_sql_query(query, engine)
• pd.read_sql_table(table_name, engine)
• pd.read_sql(sql, engine)

4. Store the result in a DataFrame.

Example:

engine = create_engine('mysql+pymysql://root:password@localhost:3306/database_name')
df = pd.read_sql_query('SELECT * FROM INVENTORY', engine)

Reshaping data means changing the structure of a DataFrame so that it becomes suitable for analysis. Pandas provides pivot() and pivot_table() for this purpose.

Example: the sales data of stores can be reshaped so that Store becomes the index and Year becomes the columns. Then sales values are easier to compare across years.

This is important because reshaping can make data more readable, organized, and easier to analyze.

Estimation is important in data analysis because missing values represent a loss of information. If we replace them using a suitable estimate such as 0, previous value, next value, or average, we can still perform analysis and get a good approximation of the actual results. Without estimation, some data may have to be dropped, which reduces the size of the dataset.

The required task is to write Python code for the given Product table and related operations. The table can be stored as a DataFrame using a dictionary and pd.DataFrame().

For example:

import pandas as pd

data = {
    'Item': ['TV', 'TV', 'TV', 'AC'],
    'Company': ['LG', 'VIDEOCON', 'LG', 'SONY'],
    'Rupees': [12000, 10000, 15000, 14000],
    'USD': [700, 650, 800, 750]
}

df = pd.DataFrame(data)

This creates the DataFrame for the given table.

To create the DataFrame for the given Product table:

import pandas as pd

data = {
    'Item': ['TV', 'TV', 'TV', 'AC'],
    'Company': ['LG', 'VIDEOCON', 'LG', 'SONY'],
    'Rupees': [12000, 10000, 15000, 14000],
    'USD': [700, 650, 800, 750]
}

df = pd.DataFrame(data)
print(df)