12월 2024 - 4 중 2 번째 페이지

[PYTHON/OPENPYXL] Worksheet 클래스 : merge_cells 메소드를 사용해 셀 병합하기

■ Worksheet 클래스의 merge_cells 메소드를 사용해 셀을 병합하는 방법을 보여준다. ▶ main.py


from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

worksheet.merge_cells("B2:C2")

from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

worksheet.merge_cells("B2:C2")

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl 명령을 실행했다.

[PYTHON/OPENPYXL] Cell 클래스 : font 속성을 사용해 폰트 설정하기

■ Cell 클래스의 font 속성을 사용해 폰트를 설정하는 방법을 보여준다. ▶ main.py


from openpyxl        import Workbook
from openpyxl.styles import Font

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

font = Font(name = "나눔고딕코딩", size = 14, bold = True, italic = False)

cell.font = font

from openpyxl import Workbook

from openpyxl.styles import Font

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

font = Font(name = "나눔고딕코딩", size = 14, bold = True, italic = False)

cell.font = font

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl 명령을 실행했다.

[PYTHON/OPENPYXL] Cell 클래스 : coordinate 속성을 사용해 셀 위치 구하기

■ Cell 클래스의 coordinate 속성을 사용해 셀 위치를 구하는 방법을 보여준다. ▶ main.py


from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

print(cell.coordinate)

"""
B2
"""

from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

print(cell.coordinate)

"""

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl 명령을

[PYTHON/OPENPYXL] Cell 클래스 : value 속성을 사용해 셀 값 구하기

■ Cell 클래스의 value 속성을 사용해 셀 값을 구하는 방법을 보여준다. ▶ main.py


from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

print(cell.value)

"""
문자열1
"""

from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

print(cell.value)

"""

문자열1

"""

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl 명령을

[PYTHON/OPENPYXL] Worksheet 클래스 : cell 함수에서 row/column 인자를 사용해 Cell 객체 구하기

■ Worksheet 클래스의 cell 함수에서 row/column 인자를 사용해 Cell 객체를 구하는 방법을 보여준다. ▶ main.py


from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install

[PYTHON/OPENPYXL] Font 클래스 : 생성자에서 name/size/bold/italic 인자를 사용해 Font 객체 만들기

■ Font 클래스의 생성자에서 name/size/bold/italic 인자를 사용해 Font 객체를 만드는 방법을 보여준다. ▶ main.py


from openpyxl.styles import Font

font = Font(name = "나눔고딕코딩", size = 12, bold = False, italic = False)

from openpyxl.styles import Font

font = Font(name = "나눔고딕코딩", size = 12, bold = False, italic = False)

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl

[PYTHON/OPENPYXL] Worksheet 클래스 : cell 함수에서 row/column/value 인자를 사용해 셀 값 설정하기

■ Worksheet 클래스의 cell 함수에서 row/column/value 인자를 사용해 셀 값을 설정하는 방법을 보여준다. ▶ main.py


from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install

[PYTHON/OPENPYXL] Workbook 클래스 : active 속성을 사용해 활성 Worksheet 객체 구하기

■ Workbook 클래스의 active 속성을 사용해 활성 Worksheet 객체를 구하는 방법을 보여준다. ▶ main.py


from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl

[PYTHON/OPENPYXL] Workbook 클래스 : [] 연산자를 사용해 Worksheet 객체 구하기

■ Workbook 클래스에서 [] 연산자를 사용해 Worksheet 객체를 구하는 방법을 보여준다. ▶ main.py


from openpyxl import Workbook

workbook = Workbook()

worksheet1 = workbook.create_sheet("시트1")

worksheet2 = workbook["시트1"]

print(worksheet2.title)

"""
시트1
"""

from openpyxl import Workbook

workbook = Workbook()

worksheet1 = workbook.create_sheet("시트1")

worksheet2 = workbook["시트1"]

print(worksheet2.title)

"""

시트1

"""

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl 명령을

[PYTHON/OPENPYXL] Workbook 클래스 : create_sheet 메소드를 사용해 워크시트 생성하기

■ Workbook 클래스의 create_sheet 메소드를 사용해 워크시트를 생성하는 방법을 보여준다. ▶ 예제 코드 (PY)


from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.create_sheet("시트1")

from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.create_sheet("시트1")

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl

[PYTHON/OPENPYXL] openpyxl 패키지 : 엑셀 파일 생성하기

■ openpyxl 패키지를 사용해 엑셀 파일을 생성하는 방법을 보여준다. ▶ main.py


from openpyxl                     import Workbook
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.styles              import Font
from typing                       import Any
from openpyxl.styles              import PatternFill
from openpyxl.styles              import Side
from openpyxl.styles              import Border

# 워크북 생성하기
def createWorkbook() -> Workbook:
    workbook = Workbook()
    return workbook

# 워크시트 추가하기
def addWorksheet(workbook : Workbook, sheetName : str) -> Worksheet:
    worksheet = workbook.create_sheet(sheetName)
    return worksheet

# 워크시트 구하기
def getWorksheet(workbook : Workbook, sheetName : str) -> Worksheet:
    try:
        return workbook[sheetName]
    except KeyError:
        return None

# 범위 구하기
def getRange(worksheet : Worksheet, y1 : int, x1 : int, y2 : int, x2 : int) -> str:
    mergeRange = f"{worksheet.cell(row = y1, column = x1).coordinate}:{worksheet.cell(row = y2, column = x2).coordinate}"
    return mergeRange

# 셀 폰트 워크시트 설정하기
def setCellFontWorksheet(worksheet : Worksheet, fontName : str, fontSize : int, isBold : bool = False, isItalic : bool = False) -> None:
    font = Font(name = fontName, size = fontSize, bold = isBold, italic = isItalic)
    for row in worksheet.rows:
        for cell in row:
            cell.font = font

# 셀 폰트 범위 설정하기
def setCellFontRange(worksheet : Worksheet, y1 : int, x1 : int, y2 : int, x2 : int, fontName : str,  fontSize : int, isBold : bool = False, isItalic : bool = False) -> None:
    font = Font(name = fontName, size = fontSize, bold = isBold, italic = isItalic)
    for y in range(y1, y2 + 1):
        for x in range(x1, x2 + 1):
            worksheet.cell(row = y, column = x).font = font

# 셀 병합하기
def mergeCells(worksheet : Worksheet, y1 : int, x1 : int, y2 : int, x2 : int) -> None:
    mergeRange = f"{worksheet.cell(row = y1, column = x1).coordinate}:{worksheet.cell(row = y2, column = x2).coordinate}"
    worksheet.merge_cells(mergeRange)

# 셀 병합 취소하기
def unmergeCells(worksheet : Worksheet, y1 : int, x1 : int, y2 : int, x2 : int) -> None:
    mergeRange = f"{worksheet.cell(row = y1, column = x1).coordinate}:{worksheet.cell(row = y2, column = x2).coordinate}"
    worksheet.unmerge_cells(mergeRange)

# 셀 값 설정하기
def setCellValue(worksheet : Worksheet, y : int, x : int, value : str) -> None:
    worksheet.cell(row = y, column = x, value = value)

# 셀 값 구하기
def getCellValue(worksheet : Worksheet, y : int, x : int) -> Any:
    return worksheet.cell(row = y, column = x).value

# 셀 범위 스타일 설정하기
def setCellRangeStyle(worksheet : Worksheet, y1 : int, x1 : int, y2 : int, x2 : int, backgroundColor : str, borderThickness : float = 1.0, drawInnerBorder : bool = True) -> None:
    patternFill = PatternFill(start_color = backgroundColor, end_color = backgroundColor, fill_type = "solid")
    borderStyle = "thin" if borderThickness <= 1 else "medium"

    for y in range(y1, y2 + 1):
        for x in range(x1, x2 + 1):
            cell      = worksheet.cell(row = y, column = x)
            cell.fill = patternFill

            isTop    = y == y1
            isBottom = y == y2
            isLeft   = x == x1
            isRight  = x == x2

            borderSideDictionary = {
                "top"    : Side(border_style = borderStyle) if isTop    or (drawInnerBorder and y > y1) else Side(border_style = None),
                "bottom" : Side(border_style = borderStyle) if isBottom or (drawInnerBorder and y < y2) else Side(border_style = None),
                "left"   : Side(border_style = borderStyle) if isLeft   or (drawInnerBorder and x > x1) else Side(border_style = None),
                "right"  : Side(border_style = borderStyle) if isRight  or (drawInnerBorder and x < x2) else Side(border_style = None)
            }

            cell.border = Border(**borderSideDictionary)

# 엑셀 파일 저장하기
def saveEXCELFile(filePath : str, workbook : Workbook) -> None:
    workbook.save(filePath)

workbook = createWorkbook()

addWorksheet(workbook, "Sheet1")
addWorksheet(workbook, "Sheet2")

worksheet1 = getWorksheet(workbook, "Sheet1")

setCellValue(worksheet1,  2, 2, "내부선 있음")
setCellValue(worksheet1,  2, 3, "예제1"      )
setCellValue(worksheet1,  3, 2, "Excel"      )
setCellValue(worksheet1,  3, 3, "테스트"     )

setCellRangeStyle(worksheet1, 2, 2, 3, 3, "ffff00", borderThickness = 1.0, drawInnerBorder = True)

setCellValue(worksheet1, 5, 2, "내부선 없음")
setCellValue(worksheet1, 5, 3, "예제2"      )
setCellValue(worksheet1, 6, 2, "Excel"      )
setCellValue(worksheet1, 6, 3, "테스트"     )

setCellRangeStyle(worksheet1, 5, 2, 6, 3, "90ee90", borderThickness = 1.0, drawInnerBorder = False)

setCellFontWorksheet(worksheet1, "나눔고딕코딩", 12)

setCellFontRange(worksheet1, 2, 2, 3, 3, "나눔고딕코딩", 14)

worksheet2 = getWorksheet(workbook, "Sheet2")

setCellValue(worksheet2, 1, 1, "병합된 셀")
setCellValue(worksheet2, 1, 2, "테스트1"  )

mergeCells(worksheet2, 1, 1, 1, 2)

setCellValue(worksheet2, 3, 1, "병합 후 해제될 셀")
setCellValue(worksheet2, 3, 2, "테스트2"          )

mergeCells(worksheet2, 3, 1, 3, 2)

unmergeCells(worksheet2, 3, 1, 3, 2)

range = getRange(worksheet2, 3, 1, 3, 2)

print(range) # A3:B3

saveEXCELFile("example.xlsx", workbook)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

from openpyxl import Workbook

from openpyxl.worksheet.worksheet import Worksheet

from openpyxl.styles import Font

from typing import Any

from openpyxl.styles import PatternFill

from openpyxl.styles import Side

from openpyxl.styles import Border

# 워크북 생성하기

def createWorkbook() -> Workbook:

workbook = Workbook()

return workbook

# 워크시트 추가하기

def addWorksheet(workbook : Workbook, sheetName : str) -> Worksheet:

worksheet = workbook.create_sheet(sheetName)

return worksheet

# 워크시트 구하기

def getWorksheet(workbook : Workbook, sheetName : str) -> Worksheet:

try:

return workbook[sheetName]

except KeyError:

return None

# 범위 구하기

def getRange(worksheet : Worksheet, y1 : int, x1 : int, y2 : int, x2 : int) -> str:

mergeRange = f"{worksheet.cell(row = y1, column = x1).coordinate}:{worksheet.cell(row = y2, column = x2).coordinate}"

return mergeRange

# 셀 폰트 워크시트 설정하기

def setCellFontWorksheet(worksheet : Worksheet, fontName : str, fontSize : int, isBold : bool = False, isItalic : bool = False) -> None:

font = Font(name = fontName, size = fontSize, bold = isBold, italic = isItalic)

for row in worksheet.rows:

for cell in row:

cell.font = font

# 셀 폰트 범위 설정하기

def setCellFontRange(worksheet : Worksheet, y1 : int, x1 : int, y2 : int, x2 : int, fontName : str, fontSize : int, isBold : bool = False, isItalic : bool = False) -> None:

font = Font(name = fontName, size = fontSize, bold = isBold, italic = isItalic)

for y in range(y1, y2 + 1):

for x in range(x1, x2 + 1):

worksheet.cell(row = y, column = x).font = font

# 셀 병합하기

def mergeCells(worksheet : Worksheet, y1 : int, x1 : int, y2 : int, x2 : int) -> None:

mergeRange = f"{worksheet.cell(row = y1, column = x1).coordinate}:{worksheet.cell(row = y2, column = x2).coordinate}"

worksheet.merge_cells(mergeRange)

# 셀 병합 취소하기

def unmergeCells(worksheet : Worksheet, y1 : int, x1 : int, y2 : int, x2 : int) -> None:

mergeRange = f"{worksheet.cell(row = y1, column = x1).coordinate}:{worksheet.cell(row = y2, column = x2).coordinate}"

worksheet.unmerge_cells(mergeRange)

# 셀 값 설정하기

def setCellValue(worksheet : Worksheet, y : int, x : int, value : str) -> None:

worksheet.cell(row = y, column = x, value = value)

# 셀 값 구하기

def getCellValue(worksheet : Worksheet, y : int, x : int) -> Any:

return worksheet.cell(row = y, column = x).value

# 셀 범위 스타일 설정하기

def setCellRangeStyle(worksheet : Worksheet, y1 : int, x1 : int, y2 : int, x2 : int, backgroundColor : str, borderThickness : float = 1.0, drawInnerBorder : bool = True) -> None:

patternFill = PatternFill(start_color = backgroundColor, end_color = backgroundColor, fill_type = "solid")

borderStyle = "thin" if borderThickness <= 1 else "medium"

for y in range(y1, y2 + 1):

for x in range(x1, x2 + 1):

cell = worksheet.cell(row = y, column = x)

cell.fill = patternFill

isTop = y == y1

isBottom = y == y2

isLeft = x == x1

isRight = x == x2

borderSideDictionary = {

"top" : Side(border_style = borderStyle) if isTop or (drawInnerBorder and y > y1) else Side(border_style = None),

"bottom" : Side(border_style = borderStyle) if isBottom or (drawInnerBorder and y < y2) else Side(border_style = None),

"left" : Side(border_style = borderStyle) if isLeft or (drawInnerBorder and x > x1) else Side(border_style = None),

"right" : Side(border_style = borderStyle) if isRight or (drawInnerBorder and x < x2) else Side(border_style = None)

}

cell.border = Border(**borderSideDictionary)

# 엑셀 파일 저장하기

def saveEXCELFile(filePath : str, workbook : Workbook) -> None:

workbook.save(filePath)

workbook = createWorkbook()

addWorksheet(workbook, "Sheet1")

addWorksheet(workbook, "Sheet2")

worksheet1 = getWorksheet(workbook, "Sheet1")

setCellValue(worksheet1, 2, 2, "내부선 있음")

setCellValue(worksheet1, 2, 3, "예제1" )

setCellValue(worksheet1, 3, 2, "Excel" )

setCellValue(worksheet1, 3, 3, "테스트" )

setCellRangeStyle(worksheet1, 2, 2, 3, 3, "ffff00", borderThickness = 1.0, drawInnerBorder = True)

setCellValue(worksheet1, 5, 2, "내부선 없음")

setCellValue(worksheet1, 5, 3, "예제2" )

setCellValue(worksheet1, 6, 2, "Excel" )

setCellValue(worksheet1, 6, 3, "테스트" )

setCellRangeStyle(worksheet1, 5, 2, 6, 3, "90ee90", borderThickness = 1.0, drawInnerBorder = False)

setCellFontWorksheet(worksheet1, "나눔고딕코딩", 12)

setCellFontRange(worksheet1, 2, 2, 3, 3, "나눔고딕코딩", 14)

worksheet2 = getWorksheet(workbook, "Sheet2")

setCellValue(worksheet2, 1, 1, "병합된 셀")

setCellValue(worksheet2, 1, 2, "테스트1" )

mergeCells(worksheet2, 1, 1, 1, 2)

setCellValue(worksheet2, 3, 1, "병합 후 해제될 셀")

setCellValue(worksheet2, 3, 2, "테스트2" )

mergeCells(worksheet2, 3, 1, 3, 2)

unmergeCells(worksheet2, 3, 1, 3, 2)

range = getRange(worksheet2, 3, 1, 3, 2)

print(range) # A3:B3

saveEXCELFile("example.xlsx", workbook)

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl 명령을 실행했다.

[PYTHON/OPENPYXL] openpyxl 패키지 설치하기

■ openpyxl 패키지를 설치하는 방법을 보여준다. 1. 명령 프롬프트를 실행한다. 2. 명령 프롬프트에서 아래 명령을 실행한다. ▶ 실행 명령


pip install openpyxl

pip install openpyxl

[PYTHON/PANDAS] DataFrame 클래스 : read_excel 메소드를 사용해 계층 데이터 로드하기

■ DataFrame 클래스 : read_excel 메소드를 사용해 계층 데이터 로드하기 [source.xlsx] ▶ main.py


import pandas as pd

from typing import Optional
from typing import List
from typing import Dict

class CategoryNode:
    def __init__(self, name : str, level : int, parentNode : Optional["CategoryNode"] = None):
        self.name          : str = name
        self.level         : int = level
        self.parentNode    : Optional[CategoryNode] = parentNode
        self.childNodeList : List[CategoryNode] = []
        self.costCodeList  : List[str] = []
        self.nodeCount     : int = 0
        self.nodePathList  : List[CategoryNode] = self._initializeNodePathList()

    def _initializeNodePathList(self) -> List["CategoryNode"]:
        nodePathList = []
        currentParentNode = self.parentNode
        while currentParentNode is not None:
            nodePathList.insert(0, currentParentNode)
            currentParentNode = currentParentNode.parentNode
        nodePathList.append(self)
        return nodePathList

    def addChild(self, childNode : "CategoryNode") -> None:
        self.childNodeList.append(childNode)

    def addCostCode(self, costCode : str) -> None:
        if costCode and costCode not in self.costCodeList:
            self.costCodeList.append(costCode)

    def getAncestorNodeList(self) -> List["CategoryNode"]:
        return self.nodePathList

class Category:
    def __init__(self):
        self.rootNodeList       : List[CategoryNode] = []
        self.costCodeDictionary : Dict[str, CategoryNode] = {}

    def addRootNode(self, rootNode : CategoryNode) -> None:
        self.rootNodeList.append(rootNode)

    def addCodeCode(self, costCode : str, node : CategoryNode) -> None:
        self.costCodeDictionary[costCode] = node

    def setNodeCount(self) -> None:
        def calculateNodeCount(node : CategoryNode) -> int:
            count = 1
            for child in node.childNodeList:
                count += calculateNodeCount(child)
            node.nodeCount = count
            return count
        for rootNode in self.rootNodeList:
            calculateNodeCount(rootNode)

def createCategory(sourceFilePath : str) -> Category:
    sourceDataFrame = pd.read_excel(sourceFilePath)
    category        = Category()
    nodeDictionary  = {}
    for _, rowSeries in sourceDataFrame.iterrows():
        currentParentNode = None
        currentPathList   = []
        for level in range(1, 6): # 구분1 ~ 구분5
            columnName = f"구분{level}"
            value = rowSeries[columnName]
            if pd.isna(value):
                break
            currentPathList.append(value)
            pathKeyTuple = tuple(currentPathList)
            if pathKeyTuple not in nodeDictionary:
                newNode = CategoryNode(value, level - 1, currentParentNode)
                nodeDictionary[pathKeyTuple] = newNode
                if currentParentNode:
                    currentParentNode.addChild(newNode)
                else:
                    if newNode.name not in [rootNode.name for rootNode in category.rootNodeList]:
                        category.addRootNode(newNode)
            currentParentNode = nodeDictionary[pathKeyTuple]
        costCode = rowSeries["비용코드"]
        if pd.notna(costCode) and currentParentNode:
            currentParentNode.addCostCode(costCode)
            category.addCodeCode(costCode, currentParentNode)
    category.setNodeCount()
    return category

def printCategoryNode(node : CategoryNode, indent : str = "") -> None:
    print(f"{indent}{node.name} (Level : {node.level}, Node Count : {node.nodeCount})", end = "")
    if node.costCodeList:
        print(f", Cost Code List : {node.costCodeList}")
    else:
        print()
    for child in node.childNodeList:
        printCategoryNode(child, indent + "    ")

def printNodePathList(node : CategoryNode) -> str:
    nodePathString = ""
    for node in node.nodePathList:
        if len(nodePathString) == 0:
            nodePathString += node.name
        else:
            nodePathString += f",{node.name}"
    return nodePathString

def printCostCodeDictionary(category : Category) -> None:
    for costCode, node in category.costCodeDictionary.items():
        print(f"Cost Code : {costCode} -> Node : {node.name} (Level : {node.level}, Node Path List : {printNodePathList(node)})")

sourceFilePath = "source.xlsx"

category = createCategory(sourceFilePath)

for rootNode in category.rootNodeList:
    printCategoryNode(rootNode)
print()
printCostCodeDictionary(category)

"""
A (Level : 0, Node Count : 7)
    A1 (Level : 1, Node Count : 4)
        A2 (Level : 2, Node Count : 3)
            A3 (Level : 3, Node Count : 2)
                A4 (Level : 4, Node Count : 1), Cost Code List : ['CFA01', 'CFA02', 'CFA03']
    A5 (Level : 1, Node Count : 2)
        A6 (Level : 2, Node Count : 1), Cost Code List : ['CFA04', 'CFA05']
B (Level : 0, Node Count : 4)
    B1 (Level : 1, Node Count : 2)
        B2 (Level : 2, Node Count : 1), Cost Code List : ['CFA06', 'CFA07']
    B3 (Level : 1, Node Count : 1), Cost Code List : ['CFA08']
C (Level : 0, Node Count : 7)
    C1 (Level : 1, Node Count : 3)
        C2 (Level : 2, Node Count : 2)
            C3 (Level : 3, Node Count : 1), Cost Code List : ['CFA09', 'CFA10']
    C4 (Level : 1, Node Count : 3)
        C5 (Level : 2, Node Count : 2)
            C6 (Level : 3, Node Count : 1), Cost Code List : ['CFA11', 'CFA12']

Cost Code : CFA01 -> Node : A4 (Level : 4, Node Path List : A,A1,A2,A3,A4)
Cost Code : CFA02 -> Node : A4 (Level : 4, Node Path List : A,A1,A2,A3,A4)
Cost Code : CFA03 -> Node : A4 (Level : 4, Node Path List : A,A1,A2,A3,A4)
Cost Code : CFA04 -> Node : A6 (Level : 2, Node Path List : A,A5,A6)
Cost Code : CFA05 -> Node : A6 (Level : 2, Node Path List : A,A5,A6)
Cost Code : CFA06 -> Node : B2 (Level : 2, Node Path List : B,B1,B2)
Cost Code : CFA07 -> Node : B2 (Level : 2, Node Path List : B,B1,B2)
Cost Code : CFA08 -> Node : B3 (Level : 1, Node Path List : B,B3)
Cost Code : CFA09 -> Node : C3 (Level : 3, Node Path List : C,C1,C2,C3)
Cost Code : CFA10 -> Node : C3 (Level : 3, Node Path List : C,C1,C2,C3)
Cost Code : CFA11 -> Node : C6 (Level : 3, Node Path List : C,C4,C5,C6)
Cost Code : CFA12 -> Node : C6 (Level : 3, Node Path List : C,C4,C5,C6)
"""

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

import pandas as pd

from typing import Optional

from typing import List

from typing import Dict

class CategoryNode:

def __init__(self, name : str, level : int, parentNode : Optional["CategoryNode"] = None):

self.name : str = name

self.level : int = level

self.parentNode : Optional[CategoryNode] = parentNode

self.childNodeList : List[CategoryNode] = []

self.costCodeList : List[str] = []

self.nodeCount : int = 0

self.nodePathList : List[CategoryNode] = self._initializeNodePathList()

def _initializeNodePathList(self) -> List["CategoryNode"]:

nodePathList = []

currentParentNode = self.parentNode

while currentParentNode is not None:

nodePathList.insert(0, currentParentNode)

currentParentNode = currentParentNode.parentNode

nodePathList.append(self)

return nodePathList

def addChild(self, childNode : "CategoryNode") -> None:

self.childNodeList.append(childNode)

def addCostCode(self, costCode : str) -> None:

if costCode and costCode not in self.costCodeList:

self.costCodeList.append(costCode)

def getAncestorNodeList(self) -> List["CategoryNode"]:

return self.nodePathList

class Category:

def __init__(self):

self.rootNodeList : List[CategoryNode] = []

self.costCodeDictionary : Dict[str, CategoryNode] = {}

def addRootNode(self, rootNode : CategoryNode) -> None:

self.rootNodeList.append(rootNode)

def addCodeCode(self, costCode : str, node : CategoryNode) -> None:

self.costCodeDictionary[costCode] = node

def setNodeCount(self) -> None:

def calculateNodeCount(node : CategoryNode) -> int:

count = 1

for child in node.childNodeList:

count += calculateNodeCount(child)

node.nodeCount = count

return count

for rootNode in self.rootNodeList:

calculateNodeCount(rootNode)

def createCategory(sourceFilePath : str) -> Category:

sourceDataFrame = pd.read_excel(sourceFilePath)

category = Category()

nodeDictionary = {}

for _, rowSeries in sourceDataFrame.iterrows():

currentParentNode = None

currentPathList = []

for level in range(1, 6): # 구분1 ~ 구분5

columnName = f"구분{level}"

value = rowSeries[columnName]

if pd.isna(value):

break

currentPathList.append(value)

pathKeyTuple = tuple(currentPathList)

if pathKeyTuple not in nodeDictionary:

newNode = CategoryNode(value, level - 1, currentParentNode)

nodeDictionary[pathKeyTuple] = newNode

if currentParentNode:

currentParentNode.addChild(newNode)

else:

if newNode.name not in [rootNode.name for rootNode in category.rootNodeList]:

category.addRootNode(newNode)

currentParentNode = nodeDictionary[pathKeyTuple]

costCode = rowSeries["비용코드"]

if pd.notna(costCode) and currentParentNode:

currentParentNode.addCostCode(costCode)

category.addCodeCode(costCode, currentParentNode)

category.setNodeCount()

return category

def printCategoryNode(node : CategoryNode, indent : str = "") -> None:

print(f"{indent}{node.name} (Level : {node.level}, Node Count : {node.nodeCount})", end = "")

if node.costCodeList:

print(f", Cost Code List : {node.costCodeList}")

else:

print()

for child in node.childNodeList:

printCategoryNode(child, indent + " ")

def printNodePathList(node : CategoryNode) -> str:

nodePathString = ""

for node in node.nodePathList:

if len(nodePathString) == 0:

nodePathString += node.name

else:

nodePathString += f",{node.name}"

return nodePathString

def printCostCodeDictionary(category : Category) -> None:

for costCode, node in category.costCodeDictionary.items():

print(f"Cost Code : {costCode} -> Node : {node.name} (Level : {node.level}, Node Path List : {printNodePathList(node)})")

sourceFilePath = "source.xlsx"

category = createCategory(sourceFilePath)

for rootNode in category.rootNodeList:

printCategoryNode(rootNode)

print()

printCostCodeDictionary(category)

"""

A (Level : 0, Node Count : 7)

A1 (Level : 1, Node Count : 4)

A2 (Level : 2, Node Count : 3)

A3 (Level : 3, Node Count : 2)

A4 (Level : 4, Node Count : 1), Cost Code List : ['CFA01', 'CFA02', 'CFA03']

A5 (Level : 1, Node Count : 2)

A6 (Level : 2, Node Count : 1), Cost Code List : ['CFA04', 'CFA05']

B (Level : 0, Node Count : 4)

B1 (Level : 1, Node Count : 2)

B2 (Level : 2, Node Count : 1), Cost Code List : ['CFA06', 'CFA07']

B3 (Level : 1, Node Count : 1), Cost Code List : ['CFA08']

C (Level : 0, Node Count : 7)

C1 (Level : 1, Node Count : 3)

C2 (Level : 2, Node Count : 2)

C3 (Level : 3, Node Count : 1), Cost Code List : ['CFA09', 'CFA10']

C4 (Level : 1, Node Count : 3)

C5 (Level : 2, Node Count : 2)

C6 (Level : 3, Node Count : 1), Cost Code List : ['CFA11', 'CFA12']

Cost Code : CFA01 -> Node : A4 (Level : 4, Node Path List : A,A1,A2,A3,A4)

Cost Code : CFA02 -> Node : A4 (Level : 4, Node Path List : A,A1,A2,A3,A4)

Cost Code : CFA03 -> Node : A4 (Level : 4, Node Path List : A,A1,A2,A3,A4)

Cost Code : CFA04 -> Node : A6 (Level : 2, Node Path List : A,A5,A6)

Cost Code : CFA05 -> Node : A6 (Level : 2, Node Path List : A,A5,A6)

Cost Code : CFA06 -> Node : B2 (Level : 2, Node Path List : B,B1,B2)

Cost Code : CFA07 -> Node : B2 (Level : 2, Node Path List : B,B1,B2)

Cost Code : CFA08 -> Node : B3 (Level : 1, Node Path List : B,B3)

Cost Code : CFA09 -> Node : C3 (Level : 3, Node Path List : C,C1,C2,C3)

Cost Code : CFA10 -> Node : C3 (Level : 3, Node Path List : C,C1,C2,C3)

Cost Code : CFA11 -> Node : C6 (Level : 3, Node Path List : C,C4,C5,C6)

Cost Code : CFA12 -> Node : C6 (Level : 3, Node Path List : C,C4,C5,C6)

"""

▶ requirements.txt


defusedxml==0.7.1
et_xmlfile==2.0.0
numpy==2.2.0
odfpy==1.4.1
openpyxl==3.1.5
packaging==24.2
pandas==2.2.3
python-calamine==0.3.1
python-dateutil==2.9.0.post0
pytz==2024.2
pyxlsb==1.0.10
six==1.17.0
tzdata==2024.2
xlrd==2.0.1
XlsxWriter==3.2.0

defusedxml==0.7.1

et_xmlfile==2.0.0

numpy==2.2.0

odfpy==1.4.1

openpyxl==3.1.5

packaging==24.2

pandas==2.2.3

python-calamine==0.3.1

python-dateutil==2.9.0.post0

pytz==2024.2

pyxlsb==1.0.10

six==1.17.0

tzdata==2024.2

xlrd==2.0.1

XlsxWriter==3.2.0

※ pip install pandas[excel] 명령을

[PYTHON/PANDAS] DataFrame 클래스 : 카테고리 키 구하기

■ DataFrame 클래스를 사용해 카테고리 키를 구하는 방법을 보여준다. ▶ main.py


import pandas as pd

valueListList = [
    ["A",   "",   "",   "",   ""],
    ["A", "A1",   "",   "",   ""],
    ["A", "A1", "A2",   "",   ""],
    ["A", "A1", "A2", "A3", "A4"],
    ["B",   "",   "",   "",   ""],
    ["B", "B1",   "",   "",   ""],
    ["B", "B2",   "",   "",   ""],
    ["B", "B2", "B3",   "",   ""]
]

sourceDataFrame = pd.DataFrame(valueListList, columns = ["구분1", "구분2", "구분3", "구분4", "구분5"])

def getCategoryKey(rowSeries):
    catgoryKey = ""
    i = 0
    for value in rowSeries:
        if i == 0:
            if value == "":
                return None
            else:
                catgoryKey += value
        else:
            if value == "":
                return catgoryKey
            else:
                catgoryKey += "_" + value
        i += 1
    return catgoryKey

for index, rowSeries in sourceDataFrame.iterrows():
    catgoryKey = getCategoryKey(rowSeries)
    print(index, catgoryKey)

"""
0 A
1 A_A1
2 A_A1_A2
3 A_A1_A2_A3_A4
4 B
5 B_B1
6 B_B2
7 B_B2_B3
"""

import pandas as pd

valueListList = [

["A", "", "", "", ""],

["A", "A1", "", "", ""],

["A", "A1", "A2", "", ""],

["A", "A1", "A2", "A3", "A4"],

["B", "", "", "", ""],

["B", "B1", "", "", ""],

["B", "B2", "", "", ""],

["B", "B2", "B3", "", ""]

]

sourceDataFrame = pd.DataFrame(valueListList, columns = ["구분1", "구분2", "구분3", "구분4", "구분5"])

def getCategoryKey(rowSeries):

catgoryKey = ""

i = 0

for value in rowSeries:

if i == 0:

if value == "":

return None

else:

catgoryKey += value

else:

if value == "":

return catgoryKey

else:

catgoryKey += "_" + value

i += 1

return catgoryKey

for index, rowSeries in sourceDataFrame.iterrows():

catgoryKey = getCategoryKey(rowSeries)

print(index, catgoryKey)

"""

0 A

1 A_A1

2 A_A1_A2

3 A_A1_A2_A3_A4

4 B

5 B_B1

6 B_B2

7 B_B2_B3

"""

▶ requirements.txt


numpy==2.2.0
pandas==2.2.3
python-dateutil==2.9.0.post0
pytz==2024.2
six==1.17.0
tzdata==2024.2

numpy==2.2.0

pandas==2.2.3

python-dateutil==2.9.0.post0

pytz==2024.2

six==1.17.0

tzdata==2024.2

※ pip install pandas 명령을 실행한다.

[PYTHON/PANDAS] DataFrame 클래스 : read_excel/to_excel 메소드를 사용해 계층적 데이터 엑셀 파일 변환하기 3

■ DataFrame 클래스의 read_excel/to_excel 메소드를 사용해 계층적 데이터 엑셀 파일을 변환하는 방법을 보여준다. [source.xlsx] [target.xlsx] ▶ main.py


import pandas as pd

def expandCategoryData(sourceDataFrame):
    targetRowValueDictionaryList = []
    for _, rowSeries in sourceDataFrame.iterrows():
        currentRowValueDictionary = {}
        for i in range(1, 6):
            columnName = f"구분{i}"
            if pd.notna(rowSeries[columnName]) and rowSeries[columnName] != "":
                currentRowValueDictionary[columnName] = rowSeries[columnName]
                newRowValueDictionary = {
                    "구분1"    : currentRowValueDictionary.get("구분1", ""),
                    "구분2"    : currentRowValueDictionary.get("구분2", ""),
                    "구분3"    : currentRowValueDictionary.get("구분3", ""),
                    "구분4"    : currentRowValueDictionary.get("구분4", ""),
                    "구분5"    : currentRowValueDictionary.get("구분5", ""),
                    "비용코드" : ""
                }
                targetRowValueDictionaryList.append(newRowValueDictionary)
        targetRowValueDictionary = currentRowValueDictionary.copy()
        for i in range(1, 6):
            columnName = f"구분{i}"
            if columnName not in targetRowValueDictionary:
                targetRowValueDictionary[columnName] = ""
        targetRowValueDictionary["비용코드"] = rowSeries["비용코드"]
        targetRowValueDictionaryList.append(targetRowValueDictionary)
    targetDataFrame = pd.DataFrame(targetRowValueDictionaryList)
    targetDataFrame = targetDataFrame.drop_duplicates()
    sortColumnList  = ["구분1", "구분2", "구분3", "구분4", "구분5", "비용코드"]
    targetDataFrame = targetDataFrame.sort_values(by = sortColumnList)
    return targetDataFrame

sourceDataFrame = pd.read_excel("source.xlsx", keep_default_na = False)
targetDataFrame = expandCategoryData(sourceDataFrame)
targetDataFrame.to_excel("target.xlsx", index = False)

import pandas as pd

def expandCategoryData(sourceDataFrame):

targetRowValueDictionaryList = []

for _, rowSeries in sourceDataFrame.iterrows():

currentRowValueDictionary = {}

for i in range(1, 6):

columnName = f"구분{i}"

if pd.notna(rowSeries[columnName]) and rowSeries[columnName] != "":

currentRowValueDictionary[columnName] = rowSeries[columnName]

newRowValueDictionary = {

"구분1" : currentRowValueDictionary.get("구분1", ""),

"구분2" : currentRowValueDictionary.get("구분2", ""),

"구분3" : currentRowValueDictionary.get("구분3", ""),

"구분4" : currentRowValueDictionary.get("구분4", ""),

"구분5" : currentRowValueDictionary.get("구분5", ""),

"비용코드" : ""

}

targetRowValueDictionaryList.append(newRowValueDictionary)

targetRowValueDictionary = currentRowValueDictionary.copy()

for i in range(1, 6):

columnName = f"구분{i}"

if columnName not in targetRowValueDictionary:

targetRowValueDictionary[columnName] = ""

targetRowValueDictionary["비용코드"] = rowSeries["비용코드"]

targetRowValueDictionaryList.append(targetRowValueDictionary)

targetDataFrame = pd.DataFrame(targetRowValueDictionaryList)

targetDataFrame = targetDataFrame.drop_duplicates()

sortColumnList = ["구분1", "구분2", "구분3", "구분4", "구분5", "비용코드"]

targetDataFrame = targetDataFrame.sort_values(by = sortColumnList)

return targetDataFrame

sourceDataFrame = pd.read_excel("source.xlsx", keep_default_na = False)

targetDataFrame = expandCategoryData(sourceDataFrame)

targetDataFrame.to_excel("target.xlsx", index = False)

▶ requirements.txt


defusedxml==0.7.1
et_xmlfile==2.0.0
numpy==2.2.0
odfpy==1.4.1
openpyxl==3.1.5
packaging==24.2
pandas==2.2.3
python-calamine==0.3.1
python-dateutil==2.9.0.post0
pytz==2024.2
pyxlsb==1.0.10
six==1.17.0
tzdata==2024.2
xlrd==2.0.1
XlsxWriter==3.2.0

defusedxml==0.7.1

et_xmlfile==2.0.0

numpy==2.2.0

odfpy==1.4.1

openpyxl==3.1.5

packaging==24.2

pandas==2.2.3

python-calamine==0.3.1

python-dateutil==2.9.0.post0

pytz==2024.2

pyxlsb==1.0.10

six==1.17.0

tzdata==2024.2

xlrd==2.0.1

XlsxWriter==3.2.0

※

[PYTHON/PANDAS] DataFrame 클래스 : read_excel/to_excel 메소드를 사용해 계층적 데이터 엑셀 파일 변환하기 2

■ DataFrame 클래스의 read_excel/to_excel 메소드를 사용해 계층적 데이터 엑셀 파일을 변환하는 방법을 보여준다. [source.xlsx] [target.xlsx] ▶ main.py


import pandas as pd

def transformCategoryData(sourceDataFrame):
    targetRowValueListList = []
    currentRowValueDictionary = {
        "구분1" : "",
        "구분2" : "",
        "구분3" : "",
        "구분4" : "",
        "구분5" : ""
    }
    for _, rowSeries in sourceDataFrame.iterrows():
        for columnName in ["구분1", "구분2", "구분3", "구분4", "구분5"]:
            if pd.notna(rowSeries[columnName]) and rowSeries[columnName] != "":
                currentRowValueDictionary[columnName] = rowSeries[columnName]
                for lowerColumnName in list(currentRowValueDictionary.keys())[list(currentRowValueDictionary.keys()).index(columnName) + 1:]:
                    currentRowValueDictionary[lowerColumnName] = ""
        if pd.notna(rowSeries["비용코드"]) and rowSeries["비용코드"] != "":
            newRowValueDictionary = currentRowValueDictionary.copy()
            newRowValueDictionary["비용코드"] = rowSeries["비용코드"]
            targetRowValueListList.append(newRowValueDictionary)
        else:
            if any(currentRowValueDictionary.values()):
                newRowValueDictionary = currentRowValueDictionary.copy()
                newRowValueDictionary["비용코드"] = ""
                targetRowValueListList.append(newRowValueDictionary)
    targetDataFrame = pd.DataFrame(targetRowValueListList)
    return targetDataFrame

def removeNoCostCodeData(sourceDataFrame):
    targetDataFrame1 = sourceDataFrame.dropna(subset = ["비용코드"])
    targetDataFrame2 = targetDataFrame1[targetDataFrame1['비용코드'] != ""]
    return targetDataFrame2

def transformCategoryFile(sourceFilePath, targetFilePath):
    sourceDataFrame  = pd.read_excel(sourceFilePath, keep_default_na = False)
    targetDataFrame1 = transformCategoryData(sourceDataFrame)
    targetDataFrame2 = removeNoCostCodeData(targetDataFrame1)
    targetDataFrame2.to_excel(targetFilePath, index = False)

transformCategoryFile("source.xlsx", "target.xlsx")

import pandas as pd

def transformCategoryData(sourceDataFrame):

targetRowValueListList = []

currentRowValueDictionary = {

"구분1" : "",

"구분2" : "",

"구분3" : "",

"구분4" : "",

"구분5" : ""

}

for _, rowSeries in sourceDataFrame.iterrows():

for columnName in ["구분1", "구분2", "구분3", "구분4", "구분5"]:

if pd.notna(rowSeries[columnName]) and rowSeries[columnName] != "":

currentRowValueDictionary[columnName] = rowSeries[columnName]

for lowerColumnName in list(currentRowValueDictionary.keys())[list(currentRowValueDictionary.keys()).index(columnName) + 1:]:

currentRowValueDictionary[lowerColumnName] = ""

if pd.notna(rowSeries["비용코드"]) and rowSeries["비용코드"] != "":

newRowValueDictionary = currentRowValueDictionary.copy()

newRowValueDictionary["비용코드"] = rowSeries["비용코드"]

targetRowValueListList.append(newRowValueDictionary)

else:

if any(currentRowValueDictionary.values()):

newRowValueDictionary = currentRowValueDictionary.copy()

newRowValueDictionary["비용코드"] = ""

targetRowValueListList.append(newRowValueDictionary)

targetDataFrame = pd.DataFrame(targetRowValueListList)

return targetDataFrame

def removeNoCostCodeData(sourceDataFrame):

targetDataFrame1 = sourceDataFrame.dropna(subset = ["비용코드"])

targetDataFrame2 = targetDataFrame1[targetDataFrame1['비용코드'] != ""]

return targetDataFrame2

def transformCategoryFile(sourceFilePath, targetFilePath):

sourceDataFrame = pd.read_excel(sourceFilePath, keep_default_na = False)

targetDataFrame1 = transformCategoryData(sourceDataFrame)

targetDataFrame2 = removeNoCostCodeData(targetDataFrame1)

targetDataFrame2.to_excel(targetFilePath, index = False)

transformCategoryFile("source.xlsx", "target.xlsx")

▶ requirements.txt


defusedxml==0.7.1
et_xmlfile==2.0.0
numpy==2.2.0
odfpy==1.4.1
openpyxl==3.1.5
packaging==24.2
pandas==2.2.3
python-calamine==0.3.1
python-dateutil==2.9.0.post0
pytz==2024.2
pyxlsb==1.0.10
six==1.17.0
tzdata==2024.2
xlrd==2.0.1
XlsxWriter==3.2.0

defusedxml==0.7.1

et_xmlfile==2.0.0

numpy==2.2.0

odfpy==1.4.1

openpyxl==3.1.5

packaging==24.2

pandas==2.2.3

python-calamine==0.3.1

python-dateutil==2.9.0.post0

pytz==2024.2

pyxlsb==1.0.10

six==1.17.0

tzdata==2024.2

xlrd==2.0.1

XlsxWriter==3.2.0

※

[PYTHON/PANDAS] DataFrame 클래스 : read_excel/to_excel 메소드를 사용해 계층적 데이터 엑셀 파일 변환하기 1

■ DataFrame 클래스 : read_excel/to_excel 메소드를 사용해 계층적 데이터 엑셀 파일 변환하기 [source.xlsx] [target.xlsx] ▶ main.py


import pandas as pd

def transformCategoryData(sourceDataFrame):
    targetRowValueListList = []
    currentRowValueDictionary = {
        "구분1" : "",
        "구분2" : "",
        "구분3" : "",
        "구분4" : "",
        "구분5" : ""
    }
    for _, rowSeries in sourceDataFrame.iterrows():
        for columnName in ["구분1", "구분2", "구분3", "구분4", "구분5"]:
            if pd.notna(rowSeries[columnName]) and rowSeries[columnName] != "":
                currentRowValueDictionary[columnName] = rowSeries[columnName]
                for lowerColumnName in list(currentRowValueDictionary.keys())[list(currentRowValueDictionary.keys()).index(columnName) + 1:]:
                    currentRowValueDictionary[lowerColumnName] = ""
        if pd.notna(rowSeries["비용코드"]) and rowSeries["비용코드"] != "":
            newRowValueDictionary = currentRowValueDictionary.copy()
            newRowValueDictionary["비용코드"] = rowSeries["비용코드"]
            targetRowValueListList.append(newRowValueDictionary)
        else:
            if any(currentRowValueDictionary.values()):
                newRowValueDictionary = currentRowValueDictionary.copy()
                newRowValueDictionary["비용코드"] = ""
                targetRowValueListList.append(newRowValueDictionary)
    targetDataFrame = pd.DataFrame(targetRowValueListList)
    return targetDataFrame

def transformCategoryFile(sourceFilePath, targetFilePath):
    sourceDataFrame = pd.read_excel(sourceFilePath, keep_default_na = False)
    targetDataFrame = transformCategoryData(sourceDataFrame)
    targetDataFrame.to_excel(targetFilePath, index = False)

transformCategoryFile("source.xlsx", "target.xlsx")

import pandas as pd

def transformCategoryData(sourceDataFrame):

targetRowValueListList = []

currentRowValueDictionary = {

"구분1" : "",

"구분2" : "",

"구분3" : "",

"구분4" : "",

"구분5" : ""

}

for _, rowSeries in sourceDataFrame.iterrows():

for columnName in ["구분1", "구분2", "구분3", "구분4", "구분5"]:

if pd.notna(rowSeries[columnName]) and rowSeries[columnName] != "":

currentRowValueDictionary[columnName] = rowSeries[columnName]

for lowerColumnName in list(currentRowValueDictionary.keys())[list(currentRowValueDictionary.keys()).index(columnName) + 1:]:

currentRowValueDictionary[lowerColumnName] = ""

if pd.notna(rowSeries["비용코드"]) and rowSeries["비용코드"] != "":

newRowValueDictionary = currentRowValueDictionary.copy()

newRowValueDictionary["비용코드"] = rowSeries["비용코드"]

targetRowValueListList.append(newRowValueDictionary)

else:

if any(currentRowValueDictionary.values()):

newRowValueDictionary = currentRowValueDictionary.copy()

newRowValueDictionary["비용코드"] = ""

targetRowValueListList.append(newRowValueDictionary)

targetDataFrame = pd.DataFrame(targetRowValueListList)

return targetDataFrame

def transformCategoryFile(sourceFilePath, targetFilePath):

sourceDataFrame = pd.read_excel(sourceFilePath, keep_default_na = False)

targetDataFrame = transformCategoryData(sourceDataFrame)

targetDataFrame.to_excel(targetFilePath, index = False)

transformCategoryFile("source.xlsx", "target.xlsx")

▶ requirements.txt


defusedxml==0.7.1
et_xmlfile==2.0.0
numpy==2.2.0
odfpy==1.4.1
openpyxl==3.1.5
packaging==24.2
pandas==2.2.3
python-calamine==0.3.1
python-dateutil==2.9.0.post0
pytz==2024.2
pyxlsb==1.0.10
six==1.17.0
tzdata==2024.2
xlrd==2.0.1
XlsxWriter==3.2.0

defusedxml==0.7.1

et_xmlfile==2.0.0

numpy==2.2.0

odfpy==1.4.1

openpyxl==3.1.5

packaging==24.2

pandas==2.2.3

python-calamine==0.3.1

python-dateutil==2.9.0.post0

pytz==2024.2

pyxlsb==1.0.10

six==1.17.0

tzdata==2024.2

xlrd==2.0.1

XlsxWriter==3.2.0

※ pip

[PYTHON/PANDAS] DataFrame 클래스 : read_csv/to_csv 메소드를 사용해 계층적 데이터 CSV 파일 변환하기

■ DataFrame 클래스의 read_csv/to_csv 메소드를 사용해 계층적 데이터 CSV 파일을 변환하는 방법을 보여준다. ▶ main.py


import pandas as pd

def transformCategoryData(sourceDataFrame):
    targetRowValueListList = []
    currentRowValueDictionary = {
        "구분1" : "",
        "구분2" : "",
        "구분3" : "",
        "구분4" : "",
        "구분5" : ""
    }
    for _, rowSeries in sourceDataFrame.iterrows():
        for columnName in ["구분1", "구분2", "구분3", "구분4", "구분5"]:
            if pd.notna(rowSeries[columnName]) and rowSeries[columnName] != "":
                currentRowValueDictionary[columnName] = rowSeries[columnName]
                for lowerColumnName in list(currentRowValueDictionary.keys())[list(currentRowValueDictionary.keys()).index(columnName) + 1:]:
                    currentRowValueDictionary[lowerColumnName] = ""
        if pd.notna(rowSeries["비용코드"]) and rowSeries["비용코드"] != "":
            newRowValueDictionary = currentRowValueDictionary.copy()
            newRowValueDictionary["비용코드"] = rowSeries["비용코드"]
            targetRowValueListList.append(newRowValueDictionary)
        else:
            if any(currentRowValueDictionary.values()):
                newRowValueDictionary = currentRowValueDictionary.copy()
                newRowValueDictionary["비용코드"] = ""
                targetRowValueListList.append(newRowValueDictionary)
    targetDataFrame = pd.DataFrame(targetRowValueListList)
    return targetDataFrame

def transformCategoryFile(sourceFilePath, targetFilePath):
    sourceDataFrame = pd.read_csv(sourceFilePath)
    targetDataFrame = transformCategoryData(sourceDataFrame)
    targetDataFrame.to_csv(targetFilePath, encoding = "utf-8-sig", index = False) # encoding 인자를 utf-8-sig로 설정한 이유는 엑셀에서 로드시 한글 깨짐 방지

transformCategoryFile("source.csv", "target.csv")

import pandas as pd

def transformCategoryData(sourceDataFrame):

targetRowValueListList = []

currentRowValueDictionary = {

"구분1" : "",

"구분2" : "",

"구분3" : "",

"구분4" : "",

"구분5" : ""

}

for _, rowSeries in sourceDataFrame.iterrows():

for columnName in ["구분1", "구분2", "구분3", "구분4", "구분5"]:

if pd.notna(rowSeries[columnName]) and rowSeries[columnName] != "":

currentRowValueDictionary[columnName] = rowSeries[columnName]

for lowerColumnName in list(currentRowValueDictionary.keys())[list(currentRowValueDictionary.keys()).index(columnName) + 1:]:

currentRowValueDictionary[lowerColumnName] = ""

if pd.notna(rowSeries["비용코드"]) and rowSeries["비용코드"] != "":

newRowValueDictionary = currentRowValueDictionary.copy()

newRowValueDictionary["비용코드"] = rowSeries["비용코드"]

targetRowValueListList.append(newRowValueDictionary)

else:

if any(currentRowValueDictionary.values()):

newRowValueDictionary = currentRowValueDictionary.copy()

newRowValueDictionary["비용코드"] = ""

targetRowValueListList.append(newRowValueDictionary)

targetDataFrame = pd.DataFrame(targetRowValueListList)

return targetDataFrame

def transformCategoryFile(sourceFilePath, targetFilePath):

sourceDataFrame = pd.read_csv(sourceFilePath)

targetDataFrame = transformCategoryData(sourceDataFrame)

targetDataFrame.to_csv(targetFilePath, encoding = "utf-8-sig", index = False) # encoding 인자를 utf-8-sig로 설정한 이유는 엑셀에서 로드시 한글 깨짐 방지

transformCategoryFile("source.csv", "target.csv")

▶ requirements.txt


numpy==2.2.0
pandas==2.2.3
python-dateutil==2.9.0.post0
pytz==2024.2
six==1.17.0
tzdata==2024.2

numpy==2.2.0

pandas==2.2.3

python-dateutil==2.9.0.post0

pytz==2024.2

six==1.17.0

tzdata==2024.2

※ pip install

[PYTHON/LANGCHAIN] RunnableSequence 클래스 : stream 메소드를 사용해 검색기를 체인에서 사용하기

■ RunnableSequence 클래스의 stream 메소드를 사용해 검색기를 체인에서 사용하는 방법을 보여준다. ※ OPENAI_API_KEY 환경 변수 값은 .env 파일에 정의한다. ▶ main.py


from dotenv                               import load_dotenv
from langchain_community.document_loaders import WebBaseLoader
from langchain_text_splitters             import RecursiveCharacterTextSplitter
from langchain_openai                     import OpenAIEmbeddings
from langchain_chroma                     import Chroma
from langchain_core.prompts               import ChatPromptTemplate
from langchain_core.prompts               import MessagesPlaceholder
from langchain_openai                     import ChatOpenAI
from langchain_core.runnables             import RunnableBranch
from langchain_core.output_parsers        import StrOutputParser
from langchain.chains.combine_documents   import create_stuff_documents_chain
from langchain_core.runnables             import RunnablePassthrough
from langchain_core.messages              import HumanMessage
from langchain_core.messages              import AIMessage

load_dotenv()

webBaseLoader = WebBaseLoader("https://docs.smith.langchain.com/overview")

documentList1 = webBaseLoader.load()

recursiveCharacterTextSplitter = RecursiveCharacterTextSplitter(chunk_size = 500, chunk_overlap = 0)

documentList2 = recursiveCharacterTextSplitter.split_documents(documentList1)

openAIEmbeddings = OpenAIEmbeddings()

chroma = Chroma.from_documents(documents = documentList2, embedding = openAIEmbeddings)

vectorStoreRetriever = chroma.as_retriever(k = 4)

# 위의 대화를 감안하여 대화와 관련된 정보를 얻기 위해 검색할 검색어를 생성합니다. 검색어로만 응답하고 다른 것은 응답하지 않습니다.
contextChatPromptTemplate = ChatPromptTemplate.from_messages(
    [
        MessagesPlaceholder(variable_name = "messages"),
        ("user", "Given the above conversation, generate a search query to look up in order to get information relevant to the conversation. Only respond with the query, nothing else.")
    ]
)

chatOpenAI = ChatOpenAI(model = "gpt-4o", temperature = 0.2)

contextRunnableBranch = RunnableBranch(
    (lambda x : len(x.get("messages", [])) == 1, (lambda x : x["messages"][-1].content) | vectorStoreRetriever),
    contextChatPromptTemplate | chatOpenAI | StrOutputParser() | vectorStoreRetriever,
)

# 아래 맥락에 따라 사용자의 질문에 답하세요.
# 맥락에 질문과 관련된 정보가 없다면, 무언가를 만들어내지 말고 "모르겠습니다"라고만 말하세요.
answerSystemTemplateString = """
Answer the user's questions based on the below context.
If the context doesn't contain any relevant information to the question, don't make something up and just say "I don't know" :

<context>
{context}
</context>
"""

answerChatPromptTemplate = ChatPromptTemplate.from_messages(
    [
        ("system", answerSystemTemplateString),
        MessagesPlaceholder(variable_name = "messages")
    ]
)

answerRunnableBinding = create_stuff_documents_chain(chatOpenAI, answerChatPromptTemplate)

runnableSequence = RunnablePassthrough.assign(context = contextRunnableBranch).assign(answer = answerRunnableBinding)

responseDictionary1 = runnableSequence.invoke({"messages" : [HumanMessage(content = "Can LangSmith help test my LLM applications?")]})

print(responseDictionary1["answer"])
print("-" * 50)

generator = runnableSequence.stream(
    {
        "messages" : [
            HumanMessage(content = "Can LangSmith help test my LLM applications?"),
            AIMessage(content = responseDictionary1["answer"]),
            HumanMessage(content = "Tell me more!")
        ]
    }
)

for addableDict in generator:
    if "answer" in addableDict:
        print(addableDict["answer"], end = "", flush = True)
print()
print("-" * 50)

"""
Yes, LangSmith is a platform for building production-grade LLM applications and allows you to closely monitor and evaluate your application.
--------------------------------------------------
LangSmith is designed to help you build and manage production-grade LLM applications. It provides tools for monitoring and evaluating your applications, enabling you to ship them quickly and confidently. While it integrates seamlessly with LangChain's open-source frameworks like langchain and langgraph, it can also function independently. You can start using LangSmith by installing it, creating an API key, and setting up your environment. Additionally, you can access the LangSmith experiments UI to explore the results of your evaluations.
--------------------------------------------------
"""

from dotenv import load_dotenv

from langchain_community.document_loaders import WebBaseLoader

from langchain_text_splitters import RecursiveCharacterTextSplitter

from langchain_openai import OpenAIEmbeddings

from langchain_chroma import Chroma

from langchain_core.prompts import ChatPromptTemplate

from langchain_core.prompts import MessagesPlaceholder

from langchain_openai import ChatOpenAI

from langchain_core.runnables import RunnableBranch

from langchain_core.output_parsers import StrOutputParser

from langchain.chains.combine_documents import create_stuff_documents_chain

from langchain_core.runnables import RunnablePassthrough

from langchain_core.messages import HumanMessage

from langchain_core.messages import AIMessage

load_dotenv()

webBaseLoader = WebBaseLoader("https://docs.smith.langchain.com/overview")

documentList1 = webBaseLoader.load()

recursiveCharacterTextSplitter = RecursiveCharacterTextSplitter(chunk_size = 500, chunk_overlap = 0)

documentList2 = recursiveCharacterTextSplitter.split_documents(documentList1)

openAIEmbeddings = OpenAIEmbeddings()

chroma = Chroma.from_documents(documents = documentList2, embedding = openAIEmbeddings)

vectorStoreRetriever = chroma.as_retriever(k = 4)

# 위의 대화를 감안하여 대화와 관련된 정보를 얻기 위해 검색할 검색어를 생성합니다. 검색어로만 응답하고 다른 것은 응답하지 않습니다.

contextChatPromptTemplate = ChatPromptTemplate.from_messages(

[

MessagesPlaceholder(variable_name = "messages"),

("user", "Given the above conversation, generate a search query to look up in order to get information relevant to the conversation. Only respond with the query, nothing else.")

]

)

chatOpenAI = ChatOpenAI(model = "gpt-4o", temperature = 0.2)

contextRunnableBranch = RunnableBranch(

(lambda x : len(x.get("messages", [])) == 1, (lambda x : x["messages"][-1].content) | vectorStoreRetriever),

contextChatPromptTemplate | chatOpenAI | StrOutputParser() | vectorStoreRetriever,

)

# 아래 맥락에 따라 사용자의 질문에 답하세요.

# 맥락에 질문과 관련된 정보가 없다면, 무언가를 만들어내지 말고 "모르겠습니다"라고만 말하세요.

answerSystemTemplateString = """

Answer the user's questions based on the below context.

If the context doesn't contain any relevant information to the question, don't make something up and just say "I don't know" :

{context}

</context>

"""

answerChatPromptTemplate = ChatPromptTemplate.from_messages(

[

("system", answerSystemTemplateString),

MessagesPlaceholder(variable_name = "messages")

]

)

answerRunnableBinding = create_stuff_documents_chain(chatOpenAI, answerChatPromptTemplate)

runnableSequence = RunnablePassthrough.assign(context = contextRunnableBranch).assign(answer = answerRunnableBinding)

responseDictionary1 = runnableSequence.invoke({"messages" : [HumanMessage(content = "Can LangSmith help test my LLM applications?")]})

print(responseDictionary1["answer"])

print("-" * 50)

generator = runnableSequence.stream(

{

"messages" : [

HumanMessage(content = "Can LangSmith help test my LLM applications?"),

AIMessage(content = responseDictionary1["answer"]),

HumanMessage(content = "Tell me more!")

]

}

)

for addableDict in generator:

if "answer" in addableDict:

print(addableDict["answer"], end = "", flush = True)

print()

print("-" * 50)

"""

Yes, LangSmith is a platform for building production-grade LLM applications and allows you to closely monitor and evaluate your application.

--------------------------------------------------

LangSmith is designed to help you build and manage production-grade LLM applications. It provides tools for monitoring and evaluating your applications, enabling you to ship them quickly and confidently. While it integrates seamlessly with LangChain's open-source frameworks like langchain and langgraph, it can also function independently. You can start using LangSmith by installing it, creating an API key, and setting up your environment. Additionally, you can access the LangSmith experiments UI to explore the results of your evaluations.

--------------------------------------------------

"""

[PYTHON/LANGCHAIN] RunnablePassthrough 클래스 : assign 정적 메소드를 사용해 검색기를 체인에서 사용하기 3

■ RunnablePassthrough 클래스의 assign 정적 메소드를 사용해 검색기를 체인에서 사용하는 방법을 보여준다. ※ OPENAI_API_KEY 환경 변수 값은 .env 파일에 정의한다. ▶ main.py

[PYTHON/LANGCHAIN] RunnablePassthrough 클래스 : assign 정적 메소드를 사용해 검색기를 체인에서 사용하기 1

[PYTHON/LANGCHAIN] create_stuff_documents_chain 함수 : 검색 문서 내에서 LLM 채팅하기

■ create_stuff_documents_chain 함수를 사용해 검색 문서 내에서 LLM과 채팅하는 방법을 보여준다. ※ OPENAI_API_KEY 환경 변수 값은 .env 파일에 정의한다. ▶ main.py


from dotenv                               import load_dotenv
from langchain_community.document_loaders import WebBaseLoader
from langchain_text_splitters             import RecursiveCharacterTextSplitter
from langchain_openai                     import OpenAIEmbeddings
from langchain_chroma                     import Chroma
from langchain_openai                     import ChatOpenAI
from langchain_core.prompts               import ChatPromptTemplate
from langchain_core.prompts               import MessagesPlaceholder
from langchain.chains.combine_documents   import create_stuff_documents_chain
from langchain_core.messages              import HumanMessage

load_dotenv()

webBaseLoader = WebBaseLoader("https://docs.smith.langchain.com/overview")

documentList = webBaseLoader.load()

recursiveCharacterTextSplitter = RecursiveCharacterTextSplitter(chunk_size = 500, chunk_overlap = 0)

splitDocumentList = recursiveCharacterTextSplitter.split_documents(documentList)

openAIEmbeddings = OpenAIEmbeddings()

chroma = Chroma.from_documents(documents = splitDocumentList, embedding = openAIEmbeddings)

vectorStoreRetriever = chroma.as_retriever(k = 4)

retrieveDocumentList = vectorStoreRetriever.invoke("Can LangSmith help test my LLM applications?")

chatOpenAI = ChatOpenAI(model = "gpt-4o-mini", temperature = 0.2)

systemTemplateString = """Answer the user's questions based on the below context.
If the context doesn't contain any relevant information to the question, don't make something up and just say "I don't know" :

<context>
{context}
</context>
"""

chatPromptTemplate = ChatPromptTemplate.from_messages(
    [
        ("system", systemTemplateString),
        MessagesPlaceholder(variable_name = "messages")
    ]
)

runnableBindable = create_stuff_documents_chain(chatOpenAI, chatPromptTemplate)

responseString = runnableBindable.invoke(
    {
        "context"  : retrieveDocumentList,
        "messages" : [HumanMessage(content = "Can LangSmith help test my LLM applications?")]
    }
)

print(responseString)

"""
Yes, LangSmith allows you to closely monitor and evaluate your application, which can help you test your LLM applications.
"""

from dotenv import load_dotenv

from langchain_community.document_loaders import WebBaseLoader

from langchain_text_splitters import RecursiveCharacterTextSplitter

from langchain_openai import OpenAIEmbeddings

from langchain_chroma import Chroma

from langchain_openai import ChatOpenAI

from langchain_core.prompts import ChatPromptTemplate

from langchain_core.prompts import MessagesPlaceholder

from langchain.chains.combine_documents import create_stuff_documents_chain

from langchain_core.messages import HumanMessage

load_dotenv()

webBaseLoader = WebBaseLoader("https://docs.smith.langchain.com/overview")

documentList = webBaseLoader.load()

recursiveCharacterTextSplitter = RecursiveCharacterTextSplitter(chunk_size = 500, chunk_overlap = 0)

splitDocumentList = recursiveCharacterTextSplitter.split_documents(documentList)

openAIEmbeddings = OpenAIEmbeddings()

chroma = Chroma.from_documents(documents = splitDocumentList, embedding = openAIEmbeddings)

vectorStoreRetriever = chroma.as_retriever(k = 4)

retrieveDocumentList = vectorStoreRetriever.invoke("Can LangSmith help test my LLM applications?")

chatOpenAI = ChatOpenAI(model = "gpt-4o-mini", temperature = 0.2)

systemTemplateString = """Answer the user's questions based on the below context.

If the context doesn't contain any relevant information to the question, don't make something up and just say "I don't know" :

{context}

</context>

"""

chatPromptTemplate = ChatPromptTemplate.from_messages(

[

("system", systemTemplateString),

MessagesPlaceholder(variable_name = "messages")

]

)

runnableBindable = create_stuff_documents_chain(chatOpenAI, chatPromptTemplate)

responseString = runnableBindable.invoke(

{

"context" : retrieveDocumentList,

"messages" : [HumanMessage(content = "Can LangSmith help test my LLM applications?")]

}

)

print(responseString)

"""

Yes, LangSmith allows you to closely monitor and evaluate your application, which can help you test your LLM applications.

"""

[PYTHON/LANGCHAIN] ChatMessageHistory 클래스 : 채팅 메시지 히스토리 요약해서 LLM과 채팅하기

■ ChatMessageHistory 클래스에서 채팅 메시지 히스토리를 요약해서 LLM과 채팅하는 방법을 보여준다. ※ OPENAI_API_KEY 환경 변수 값은 .env 파일에 정의한다. ▶ main.py


from dotenv                                     import load_dotenv
from langchain_core.prompts                     import ChatPromptTemplate
from langchain_openai                           import ChatOpenAI
from langchain_community.chat_message_histories import ChatMessageHistory
from langchain_core.runnables.history           import RunnableWithMessageHistory
from langchain_core.runnables                   import RunnablePassthrough

load_dotenv()

chatPromptTemplate = ChatPromptTemplate.from_messages(
    [
        ("system"     , "You are a helpful assistant. Answer all questions to the best of your ability."),
        ("placeholder", "{chat_history}"),
        ("human"      , "{input}")
    ]
)

chatOpenAI = ChatOpenAI(model = "gpt-4o-mini")

runnableSequence1 = chatPromptTemplate | chatOpenAI

chatMessageHistory = ChatMessageHistory()

chatMessageHistory.add_user_message("Hey there! I'm Nemo.")
chatMessageHistory.add_ai_message("Hello!")
chatMessageHistory.add_user_message("How are you today?")
chatMessageHistory.add_ai_message("Fine thanks!")

runnableWithMessageHistory = RunnableWithMessageHistory(
    runnableSequence1,
    lambda session_id : chatMessageHistory,
    input_messages_key   = "input",
    history_messages_key = "chat_history"
)

def summarizeMessageList(inputDictionary):
    messageList = chatMessageHistory.messages
    if len(messageList) == 0:
        return False
    summarizeChatPromptTemplate = ChatPromptTemplate.from_messages(
        [
            ("placeholder", "{chat_history}"),
            ("user"      , "Distill the above chat messages into a single summary message. Include as many specific details as you can.")
        ]
    )

    summarizeRunnableSequence = summarizeChatPromptTemplate | chatOpenAI

    summaryResponseAIMessage = summarizeRunnableSequence.invoke({"chat_history" : messageList})

    chatMessageHistory.clear()

    chatMessageHistory.add_message(summaryResponseAIMessage)

    return True

runnableSequence2 = RunnablePassthrough.assign(is_summarized = summarizeMessageList) | runnableWithMessageHistory

responseAIMessage = runnableSequence2.invoke({"input" : "What did I say my name was?"}, {"configurable" : {"session_id" : "unused"}})

print(responseAIMessage.content)

"""
You mentioned that your name is Nemo.
"""

from dotenv import load_dotenv

from langchain_core.prompts import ChatPromptTemplate

from langchain_openai import ChatOpenAI

from langchain_community.chat_message_histories import ChatMessageHistory

from langchain_core.runnables.history import RunnableWithMessageHistory

from langchain_core.runnables import RunnablePassthrough

load_dotenv()

chatPromptTemplate = ChatPromptTemplate.from_messages(

[

("system" , "You are a helpful assistant. Answer all questions to the best of your ability."),

("placeholder", "{chat_history}"),

("human" , "{input}")

]

)

chatOpenAI = ChatOpenAI(model = "gpt-4o-mini")

runnableSequence1 = chatPromptTemplate | chatOpenAI

chatMessageHistory = ChatMessageHistory()

chatMessageHistory.add_user_message("Hey there! I'm Nemo.")

chatMessageHistory.add_ai_message("Hello!")

chatMessageHistory.add_user_message("How are you today?")

chatMessageHistory.add_ai_message("Fine thanks!")

runnableWithMessageHistory = RunnableWithMessageHistory(

runnableSequence1,

lambda session_id : chatMessageHistory,

input_messages_key = "input",

history_messages_key = "chat_history"

)

def summarizeMessageList(inputDictionary):

messageList = chatMessageHistory.messages

if len(messageList) == 0:

return False

summarizeChatPromptTemplate = ChatPromptTemplate.from_messages(

[

("placeholder", "{chat_history}"),

("user" , "Distill the above chat messages into a single summary message. Include as many specific details as you can.")

]

)

summarizeRunnableSequence = summarizeChatPromptTemplate | chatOpenAI

summaryResponseAIMessage = summarizeRunnableSequence.invoke({"chat_history" : messageList})

chatMessageHistory.clear()

chatMessageHistory.add_message(summaryResponseAIMessage)

return True

runnableSequence2 = RunnablePassthrough.assign(is_summarized = summarizeMessageList) | runnableWithMessageHistory

responseAIMessage = runnableSequence2.invoke({"input" : "What did I say my name was?"}, {"configurable" : {"session_id" : "unused"}})

print(responseAIMessage.content)

"""

You mentioned that your name is Nemo.

"""

[PYTHON/LANGCHAIN] trim_messages 함수 : strategy/max_tokens/token_counter 인자를 사용해 RunnableLambda 객체 만들기

■ trim_messages 함수의 strategy/max_tokens/token_counter 인자를 사용해 RunnableLambda 객체를 만드는 방법을 보여준다. ※ OPENAI_API_KEY 환경 변수 값은 .env 파일에 정의한다. ▶ main.py


from dotenv                                     import load_dotenv
from langchain_core.messages                    import trim_messages
from langchain_core.prompts                     import ChatPromptTemplate
from langchain_openai                           import ChatOpenAI
from langchain_core.runnables                   import RunnablePassthrough
from operator                                   import itemgetter
from langchain_community.chat_message_histories import ChatMessageHistory
from langchain_core.runnables.history           import RunnableWithMessageHistory

load_dotenv()

runnableLambda = trim_messages(strategy = "last", max_tokens = 2, token_counter = len)

chatPromptTemplate = ChatPromptTemplate.from_messages(
    [
        ("system"     , "You are a helpful assistant. Answer all questions to the best of your ability."),
        ("placeholder", "{chat_history}"),
        ("human"      , "{input}")
    ]
)

chatOpenAI = ChatOpenAI(model = "gpt-4o-mini")

runnableSequence = RunnablePassthrough.assign(chat_history = itemgetter("chat_history") | runnableLambda) | chatPromptTemplate | chatOpenAI

chatMessageHistory = ChatMessageHistory()

chatMessageHistory.add_user_message("Hey there! I'm Nemo.")
chatMessageHistory.add_ai_message("Hello!")
chatMessageHistory.add_user_message("How are you today?")
chatMessageHistory.add_ai_message("Fine thanks!")

runnableWithMessageHistory = RunnableWithMessageHistory(
    runnableSequence,
    lambda session_id : chatMessageHistory,
    input_messages_key   = "input",
    history_messages_key = "chat_history",
)

responseMessage1 = runnableWithMessageHistory.invoke(
    {"input"        : "What's my name?"},
    {"configurable" : {"session_id" : "unused"}}
)

print(responseMessage1.content)
print("-" * 50)

responseMessage2 = runnableWithMessageHistory.invoke(
    {"input"        : "Where does P. Sherman live?"},
    {"configurable" : {"session_id" : "unused"}}
)

print(responseMessage2.content)
print("-" * 50)

"""
I'm sorry, but I don't know your name. If you'd like to share it, feel free!
--------------------------------------------------
P. Sherman lives at 42 Wallaby Way, Sydney. This is a reference from the animated movie "Finding Nemo."
--------------------------------------------------
"""

from dotenv import load_dotenv

from langchain_core.messages import trim_messages

from langchain_core.prompts import ChatPromptTemplate

from langchain_openai import ChatOpenAI

from langchain_core.runnables import RunnablePassthrough

from operator import itemgetter

from langchain_community.chat_message_histories import ChatMessageHistory

from langchain_core.runnables.history import RunnableWithMessageHistory

load_dotenv()

runnableLambda = trim_messages(strategy = "last", max_tokens = 2, token_counter = len)

chatPromptTemplate = ChatPromptTemplate.from_messages(

[

("system" , "You are a helpful assistant. Answer all questions to the best of your ability."),

("placeholder", "{chat_history}"),

("human" , "{input}")

]

)

chatOpenAI = ChatOpenAI(model = "gpt-4o-mini")

runnableSequence = RunnablePassthrough.assign(chat_history = itemgetter("chat_history") | runnableLambda) | chatPromptTemplate | chatOpenAI

chatMessageHistory = ChatMessageHistory()

chatMessageHistory.add_user_message("Hey there! I'm Nemo.")

chatMessageHistory.add_ai_message("Hello!")

chatMessageHistory.add_user_message("How are you today?")

chatMessageHistory.add_ai_message("Fine thanks!")

runnableWithMessageHistory = RunnableWithMessageHistory(

runnableSequence,

lambda session_id : chatMessageHistory,

input_messages_key = "input",

history_messages_key = "chat_history",

)

responseMessage1 = runnableWithMessageHistory.invoke(

{"input" : "What's my name?"},

{"configurable" : {"session_id" : "unused"}}

)

print(responseMessage1.content)

print("-" * 50)

responseMessage2 = runnableWithMessageHistory.invoke(

{"input" : "Where does P. Sherman live?"},

{"configurable" : {"session_id" : "unused"}}

)

print(responseMessage2.content)

print("-" * 50)

"""

I'm sorry, but I don't know your name. If you'd like to share it, feel free!

--------------------------------------------------

P. Sherman lives at 42 Wallaby Way, Sydney. This is a reference from the animated movie "Finding Nemo."

--------------------------------------------------

"""

[PYTHON/LANGCHAIN] RunnableWithMessageHistory 클래스 : invoke 메소드를 사용해 채팅 히스토리 관리하기

■ RunnableWithMessageHistory 클래스의 invoke 메소드를 사용해 채팅 히스토리를 관리하는 방법을 보여준다. ※ OPENAI_API_KEY 환경 변수 값은 .env 파일에 정의한다. ▶ main.py


from dotenv                                     import load_dotenv
from langchain_core.prompts                     import ChatPromptTemplate
from langchain_openai                           import ChatOpenAI
from langchain_community.chat_message_histories import ChatMessageHistory
from langchain_core.runnables.history           import RunnableWithMessageHistory

load_dotenv()

chatPromptTemplate = ChatPromptTemplate.from_messages(
    [
        ("system"     , "You are a helpful assistant. Answer all questions to the best of your ability."),
        ("placeholder", "{chat_history}"),
        ("human"      , "{input}")
    ]
)

chatOpenAI = ChatOpenAI(model = "gpt-4o-mini")

runnableSequence = chatPromptTemplate | chatOpenAI

chatMessageHistory = ChatMessageHistory()

runnableWithMessageHistory = RunnableWithMessageHistory(
    runnableSequence,
    lambda session_id : chatMessageHistory,
    input_messages_key   = "input",
    history_messages_key = "chat_history",
)

responseAIMessage1 = runnableWithMessageHistory.invoke(
    {"input"        : "Translate this sentence from English to French: I love programming."},
    {"configurable" : {"session_id" : "unused"}}
)

print(responseAIMessage1.content)
print("-" * 50)

responseAIMessage2 = runnableWithMessageHistory.invoke(
    {"input"        : "What did I just ask you?"},
    {"configurable" : {"session_id" : "unused"}}
)

print(responseAIMessage2.content)
print("-" * 50)

"""
The translation of "I love programming" in French is "J'aime programmer."
--------------------------------------------------
You asked me to translate the sentence "I love programming" from English to French.
--------------------------------------------------
"""

from dotenv import load_dotenv

from langchain_core.prompts import ChatPromptTemplate

from langchain_openai import ChatOpenAI

from langchain_community.chat_message_histories import ChatMessageHistory

from langchain_core.runnables.history import RunnableWithMessageHistory

load_dotenv()

chatPromptTemplate = ChatPromptTemplate.from_messages(

[

("system" , "You are a helpful assistant. Answer all questions to the best of your ability."),

("placeholder", "{chat_history}"),

("human" , "{input}")

]

)

chatOpenAI = ChatOpenAI(model = "gpt-4o-mini")

runnableSequence = chatPromptTemplate | chatOpenAI

chatMessageHistory = ChatMessageHistory()

runnableWithMessageHistory = RunnableWithMessageHistory(

runnableSequence,

lambda session_id : chatMessageHistory,

input_messages_key = "input",

history_messages_key = "chat_history",

)

responseAIMessage1 = runnableWithMessageHistory.invoke(

{"input" : "Translate this sentence from English to French: I love programming."},

{"configurable" : {"session_id" : "unused"}}

)

print(responseAIMessage1.content)

print("-" * 50)

responseAIMessage2 = runnableWithMessageHistory.invoke(

{"input" : "What did I just ask you?"},

{"configurable" : {"session_id" : "unused"}}

)

print(responseAIMessage2.content)

print("-" * 50)

"""

The translation of "I love programming" in French is "J'aime programmer."

--------------------------------------------------

You asked me to translate the sentence "I love programming" from English to French.

--------------------------------------------------

"""