Data Handling using Pandas - II

The statement to install the MySQL connector pymysql is:

```bash
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:

```python
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.

```python
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:
```python
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:
```python
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:

```python
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)
```