Data Handling Using Pandas – II

pip install pymysql

pip install sqlalchemy

engine = create_engine('mysql+pymysql://username:password@host:port/database_name')

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

df['Hindi'].mode()

g1 = df.GROUP BY('Name')
print(g1.first())

pip install pymysql
pip install sqlalchemy
                

from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://username:password@host:port/database_name')

import pandas as pd
df = pd.read_sql_query('SELECT * FROM INVENTORY', engine)

import pandas as pd
data = {
    'Store': ['S1', 'S4', 'S3', 'S1', 'S2', 'S3', 'S1', 'S2', 'S3'],
    'Year': [2016, 2016, 2016, 2017, 2017, 2017, 2018, 2018, 2018],
    'Total_sales(₹)': [12000, 330000, 420000, 20000, 10000, 450000, 30000, 11000, 89000],
    'Total_profit(₹)': [1100, 5500, 21000, 32000, 9000, 45000, 3000, 1900, 23000]
}
df = pd.DataFrame(data)

pivot_df = df.pivot(index='Store', columns='Year', values=['Total_sales(₹)', 'Total_profit(₹)'])
print(pivot_df)

import pandas as pd
from sqlalchemy import create_engine
    
# MySQL connection setup (replace with your actual credentials)
username = 'your_username'
password = 'your_password'
host = 'localhost'
database_name = 'your_database_name'
table_name = 'Product'

# Creating a connection to your MySQL database
engine = create_engine(f'mysql+pymysql://{username}:{password}@{host}/{database_name}')

# a) Creating the DataFrame by reading from the MySQL table
df = pd.read_sql_table(table_name, engine)

# b) Adding new rows to the DataFrame
# Example new rows
new_rows = pd.DataFrame({
    'Item': ['Fridge', 'Washing Machine'],
    'Company': ['Samsung', 'IFB'],
    'Rupees': [18000, 20000],
    'USD': [850, 900]
})
df = pd.concat([df, new_rows], ignore_index=True)

# c) Displaying the maximum price of LG TV
lg_tv_max_price = df[(df['Item'] == 'TV') & (df['Company'] == 'LG')]['Rupees'].max()
# Displaying the calculated value
print("Maximum price of LG TV:", lg_tv_max_price)

# d) Displaying the Sum of all products
total_sum = df['Rupees'].sum()
# Displaying the calculated value
print("Sum of all products:", total_sum)

# e) Displaying the median of the USD of Sony products
sony_usd_median = df[df['Company'] == 'SONY']['USD'].median()
# Displaying the calculated value
print("Median of the USD of Sony products:", sony_usd_median)

# f) Sorting the data according to the Rupees
df_sorted = df.sort_values(by='Rupees')

# g) Transferring the sorted DataFrame into MySQL with new values
# Remember that we've declared a variable table_name to hold the table name 'Product'
df_sorted.to_sql(table_name, engine, if_exists='replace', index=False)

data = {
    'Name': ['Aparna', 'Pankaj', 'Ram', 'Ramesh', 'Naveen', 'Krrishnav', 'Bhawna'],
    'Degree': ['MBA', 'BCA', 'M.Tech', 'MBA', 'NaN', 'BCA', 'MBA'],
    'Score': [90.0, float('NaN'), 80.0, 98.00, 97.0, 78.0, 89.0]
}
df = pd.DataFrame(data)

max_marks_per_degree = df.groupby('Degree')['Score'].max()

df_filled = df.fillna(76)

df_indexed = df.set_index('Name')

avg_marks_per_student = df.groupby(['Name', 'Degree'])['Score'].mean()

mba_student_count = df[df['Degree'] == 'MBA']['Name'].count()

bca_mode_marks = df[df['Degree'] == 'BCA']['Score'].mode()