Pandas와 SQL 1~4번 문제

문제1

문제 : 최고 가격의 제품 조회

각 카테고리별로 가장 비싼 제품의 이름과 가격을 조회하시오.

PANDAS

import pandas as pd

# 예시 데이터를 DataFrame으로 생성
data = {
    'ProductID': [1, 2, 3, 4],
    'ProductName': ['Apple iPhone 13', 'Samsung Galaxy S22', 'Logitech Mouse', 'Nike Running Shoes'],
    'Price': [999.99, 899.99, 29.99, 120.00],
    'Category': ['Electronics', 'Electronics', 'Accessories', 'Apparel']
}

products_df = pd.DataFrame(data)

def find_highest_price_prodect(group):
    return group.loc[group['Price'] == group['Price'].max()]
#find_highest_price_prodec = 주어진 그룹 데이터프레임에서 가장 높은 가격을 가진 제품(행)을 찾는 함수
#'Price'가 가장 높은 값만을 필터링(loc)한 행을 조회

find_highest_price_prodect_df = products_df.groupby('Category').apply(find_highest_price_prodect).reset_index(drop = True)
find_highest_price_prodect_df
#reset_index(drop = True)을 하지 않으면 그룹화된 category가 인덱스가 된다

 

• 사용자 함수만들기
• apply 메소드 : 각 그룹에 대해 사용자 겅의 함수를 정의
• reset_index 메소드 : 인덱스를 초기화

 

SQL(유사문제) : 식품분류별 가장 비싼 식품의 정보 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/131116?language=oracle

-- 식품분류별 제일 비싼 식품
-- 분류, 가격, 이름
-- 조건 : 과자, 국, 김치, 식용류만 출력
-- 정렬 : 식품가격 내림차순

select CATEGORY, PRICE ,PRODUCT_NAME
from FOOD_PRODUCT a
where CATEGORY in( '과자', '국', '김치', '식용유')
and (CATEGORY,PRICE) in (select b.CATEGORY, max(b.PRICE)
              from FOOD_PRODUCT b
            group by CATEGORY)
order by a.price desc

작동순서

💡SQL 실행 순서는 From -> Where -> Group by -> Having -> Select -> Order by ->결과반환

1. FROM 절: FOOD_PRODUCT 테이블에서 
2. WHERE 절: CATEGORY 가 ( '과자', '국', '김치', '식용유')에 포함되고, (CATEGORY,PRICE)가
   1. 서브쿼리 절:  FOOD_PRODUCT테이블에서 CATEGORY별로 Price가 가장 큰 값에 포함되는 경우를 필터링 한다
   2. *(CATEGORY,PRICE)가 한개의 단위처럼 계산
3. SELECT 절: CATEGORY, PRICE ,PRODUCT_NAME를 조회
4. ORDER BY 절: price를 기준으로 내림차순

---

문제2

문제 : 재고가 부족한 아이템 조회

재고 수량이 10개 이하인 모든 아이템의 이름과 재고 수량을 조회하세요.

 

PANDAS

import pandas as pd

# 예시 데이터를 DataFrame으로 생성
data = {
    'ItemID': [1, 2, 3, 4],
    'ItemName': ['Wooden Chair', 'Desk Lamp', 'Office Desk', 'Monitor Stand'],
    'StockQuantity': [15, 22, 10, 5],
    'Price': [45.00, 23.99, 150.00, 29.99],
    'Supplier': ['WoodCrafters', 'LightBright', 'OfficeFurnish', 'TechGear']
}

inventory_df = pd.DataFrame(data)

# 재고가 10개 미만인 데이터 조회
low_stock_items_df = inventory_df[inventory_df['StockQuantity']<=10]
# 출력, 재고를 기준으로 오름차순 정렬
print(low_stock_items_df[['ItemName', 'StockQuantity']].sort_values(by = 'StockQuantity'))

SQL

select ItemName, StockQuantity
from Inventory
where StocjQuantity <= 10
order by StockQuantity ASC;

 

---

문제3

문제 : 특정 장르의 책 조회

'Fiction' 장르의 모든 책의 제목과 저자를 조회하세요

import pandas as pd

# 예시 데이터를 DataFrame으로 생성
data = {
    'BookID': [1, 2, 3, 4],
    'Title': ['The Great Gatsby', 'Brave New World', 'A Brief History of Time', 'The Art of War'],
    'Author': ['F. Scott Fitzgerald', 'Aldous Huxley', 'Stephen Hawking', 'Sun Tzu'],
    'Genre': ['Fiction', 'Fiction', 'Science', 'Philosophy'],
    'PublishedYear': [1925, 1932, 1988, -500],
    'InStock': [True, False, True, True]
}

books_df = pd.DataFrame(data)

books_df_Fiction = books_df[books_df['Genre'] == 'Fiction']
print(books_df_Fiction[['Title', 'Author','PublishedYear']].sort_values(by='PublishedYear'))
# 정렬하고 싶은 열이 컬럼에 없으면 오류
# 예) print(books_df_Fiction[['Title','Author']].sort_values(by = 'PublishedYear'))->PublishedYear가 컬럼에 없기 때문에 오류남

# 사용자 함수를 사용하기
def find_fiction(df):
   return df.loc[df['Genre']=='Fiction']

find_fiction(books_df)

SQL(유사문제)

https://school.programmers.co.kr/learn/courses/30/lessons/144854#qna

-- 조건: 경제 카테고리
-- 조회 : 아이디, 저자명, 출한일
-- 정렬 : 출판일, 오름차순
SELECT BOOK_ID, AUTHOR_NAME, TO_CHAR(PUBLISHED_DATE, 'YYYY-MM-DD') as PUBLISHED_DATE
from BOOK inner join AUTHOR 
on BOOK.AUTHOR_ID = AUTHOR.AUTHOR_ID
where CATEGORY = '경제'
order by PUBLISHED_DATE ;

1. FROM 절: BOOK 테이블과 AUTHOR테이블을 AUTHOR_ID기준으로 이너조인
2. WHERE 절: CATEGORY 가 경제인 경우 필터링 하여
3. SELECT 절:  BOOK_ID, AUTHOR_NAME, PUBLISHED_DATE을 조회하는데, PUBLISHED_DATE은  'YYYY-MM-DD'형식으로 문자화하여 PUBLISHED_DATE으로 별칭
4. ORDER BY 절: PUBLISHED_DATE기준으로 오름차순

---

문제4

문제 : 최고 판매 제품 조회

전체 기간 동안 가장 많이 판매된 제품의 이름과 총 판매량을 조회하세요.

 

PANDAS

import pandas as pd

# 예시 데이터를 DataFrame으로 생성
data = {
    'SaleID': [1, 2, 3, 4, 5],
    'ProductName': ['Gaming Laptop', 'Wireless Mouse', 'Mechanical Keyboard', 'Gaming Laptop', 'Wireless Mouse'],
    'SaleDate': ['2024-01-01', '2024-01-03', '2024-01-05', '2024-01-07', '2024-01-09'],
    'Quantity': [2, 5, 3, 1, 2],
    'SalePrice': [1200.00, 25.99, 75.50, 1200.00, 25.99],
    'CustomerID': [101, 102, 103, 104, 101]
}

sales_data_df = pd.DataFrame(data)

 

# 방법1
# 각 제품별 총 판매량 계산
total_quantity_by_product = sales_data_df.groupby('ProductName')['Quantity'].sum()

# 가장 많이 판매된 제품 조회
top_selling_product = total_quantity_by_product.idxmax()
top_selling_quantity = total_quantity_by_product.max()

# 결과 출력
print(f"Top Selling Product: {top_selling_product}, Total Quantity: {top_selling_quantity}")

방법2
sales_data_df_sum = sales_data_df.groupby(['ProductName']).agg({'Quantity':'sum'})
sales_data_df_sum[sales_data_df_sum['Quantity'] == sales_data_df_sum['Quantity'].max()]

 

 

sales_data_df에서 ProductName별 수량의 합계 구하기 

1. sales_data_df.groupby(['ProductName'])['Quantity'].sum()

-> 방법: ProductName열을 기준으로 그룹화하여 Quantity열에 대한 합계

-> 출력: Series

2. sales_data_df.groupby(['ProductName']).agg({'Quantity':'sum'}) 

-> 방법 : 그룹화할 열과 집계대상을 딕셔너리 형태로 지정하여 그룹화

-> 출력: 그룹화된 열을 인덱스로 갖는 DataFrame

방법1	방법2

 

최대값을 갖는 인덱스 구하기

1. np.argmax(data)

NumPy라이브러리에서 제동하는 함수

2.dafa.idxmax()

Pandas라이브러리에서 제공하는 함수

 

SQL

select ProductName, sum(Quantity)
from SalesData
group by ProductName
order by sum(Quantity)
limit 1