Question

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

1. To create the data frame for the above table.
2. To add the new rows in the data frame.
3. To display the maximum price of LG TV.
4. To display the Sum of all products.
5. To display the median of the USD of Sony products.
6. To sort the data according to the Rupees and transfer the data to MySQL.
7. To transfer the new dataframe into the MySQL with new values.

Answer 1

a)

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)

b)

new_data = pd.DataFrame({
    'Item': ['AC', 'TV'],
    'Company': ['LG', 'SONY'],
    'Rupees': [16000, 18000],
    'USD': [850, 900]
})
df = pd.concat([df, new_data], ignore_index=True)
print(df)

c) 

max_price = df[(df['Item'] == 'TV') & (df['Company'] == 'LG')]['Rupees'].max()
print("Maximum Price of LG TV =", max_price)

d) 

total = df['Rupees'].sum()
print("Sum of All Products =", total)

e)

median_usd = df[df['Company'] == 'SONY']['USD'].median()
print("Median USD of SONY Products =", median_usd)

f)

from sqlalchemy import create_engine
sorted_df = df.sort_values(by='Rupees')
engine = create_engine("mysql+pymysql://root:password@localhost:3306/productdb")
sorted_df.to_sql('Product', con=engine, if_exists='replace', index=False)
print("Sorted data transferred to MySQL successfully.")

g) 

df.to_sql('New_Product', con=engine, if_exists='replace', index=False)
print("New DataFrame transferred to MySQL successfully.")

Output:

• DataFrame is created.
• New rows are added.
• Maximum price of LG TV is displayed.
• Sum of all product prices is calculated.
• Median USD of SONY products is displayed.
• Sorted data is transferred to MySQL.
• Updated DataFrame is stored in a new MySQL table.