Scribd
Upload
2 views

prac1

The document outlines various practical exercises in Business Analytics, including importing and filtering CSV files, calculating total revenue from Excel data, converting JSON data to CSV, and managing SQLite databases. It demonstrates the use of pandas for data manipulation and exporting results to new files. The exercises cover data handling from different formats and merging datasets for analysis.

Uploaded by

asharathod1999
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2 views

prac1

The document outlines various practical exercises in Business Analytics, including importing and filtering CSV files, calculating total revenue from Excel data, converting JSON data to CSV, and managing SQLite databases. It demonstrates the use of pandas for data manipulation and exporting results to new files. The exercises cover data handling from different formats and merging datasets for analysis.

Uploaded by

asharathod1999
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 5

Rathod Dharm Nitin - 22SE02ML063 Business Analytics

Practical – 1
Import a student.csv file and filter the file with scores more than 70 and save
it to a new csv file name high_score.csv
import pandas as pd

# Load the CSV file into a pandas DataFrame


df = pd.read_csv("student_scores.csv")

# Filter rows where the score is greater than 70


filtered_df = df[df["score"] > 70]

# Display the filtered DataFrame


print(filtered_df)

# Save the filtered data to a new CSV file


filtered_df.to_csv("high_scores.csv", index=False)
Rathod Dharm Nitin - 22SE02ML063 Business Analytics

Open the Excel file named sales_data.xlsx, which contains the columns: Date,
Product, Quantity, and Revenue. Calculate the total revenue for each product.
Save the results to a new Excel sheet named product_summary.xlsx.
import pandas as pd

# Load the sales_data.xlsx file


df = pd.read_excel("sales_data.xlsx")

# Calculate the total revenue for each product


product_summary = df.groupby("Product", as_index=False)["Revenue"].sum()

# Save the results to a new Excel file


summary_file_path = "product_summary.xlsx"
product_summary.to_excel(summary_file_path, index=False)

print("Saved file data:\n", product_summary)


Rathod Dharm Nitin - 22SE02ML063 Business Analytics

Objective: Handle JSON data and convert it to other formats. 1. Open the
JSON file named movies.json. Convert the JSON data into a pandas
DataFrame. Save the DataFrame into a CSV file named movies_table.csv.

import pandas as pd
import json

# Load the JSON file


with open("movies.json", "r") as file:
movies_data = json.load(file)

# Convert the JSON data into a pandas DataFrame


df = pd.DataFrame(movies_data)

# Save the DataFrame to a CSV file


df.to_csv("movies_table.csv", index=False)

print("CSV file data:\n", df)


Rathod Dharm Nitin - 22SE02ML063 Business Analytics

Import data from the CSV file employee_data.csv, which contains columns:
EmployeeID, Name, Department, and Salary. Create a new SQLite database
named company.db and a table employees. Export the data from the CSV file
into the employees table. Query the database to verify the data has been
stored correctly.
import sqlite3
import pandas as pd

# Load the CSV data into a pandas DataFrame


df = pd.read_csv("employee_data.csv")

# Connect to SQLite database (it will create the database if it doesn't exist)
conn = sqlite3.connect("company.db")
cursor = conn.cursor()

# Create the 'employees' table


cursor.execute("""
CREATE TABLE IF NOT EXISTS employees (
EmployeeID INTEGER PRIMARY KEY,
Name TEXT,
Department TEXT,
Salary INTEGER
)
""")

# Insert data into the 'employees' table from the DataFrame


df.to_sql("employees", conn, if_exists="replace", index=False)

# Commit and close the connection


conn.commit()

# Verify the data


cursor.execute("SELECT * FROM employees LIMIT 3")
data = cursor.fetchall()
print(data)

conn.close()
Rathod Dharm Nitin - 22SE02ML063 Business Analytics

Import the following files: inventory.csv: Contains columns ProductID,


Product Name, and Stock. sales.json: Contains sales data for each ProductID
with columns ProductID, Quantity Sold, and Revenue. Merge the data from
both files on ProductID into a pandas DataFrame. Save the consolidated data
to an Excel file named inventory_sales_summary.xlsx.
import pandas as pd

# Step 1: Load the data from the CSV and JSON files
inventory_df = pd.read_csv("inventory.csv") # Replace with your file path
sales_df = pd.read_json("sales.json") # Replace with your file path

# Step 2: Merge the two dataframes on 'ProductID'


consolidated_df = pd.merge(inventory_df, sales_df, on="ProductID")

# Step 3: Save the merged DataFrame to an Excel file


consolidated_df.to_excel("inventory_sales_summary.xlsx", index=False)

print("Data from Excel file:\n", consolidated_df)

You might also like