筆記如何使用 Python 透過 pands 來處理大量資料的關鍵字比對與取代 (Replace If) 的方式。
說明
import pandas as pd
from collections import OrderedDict
# Function to assign a category based on keywords in the 'Message' column
def categorizer(row):
message_text = row['Message']
# Define keywords associated with each category color
category_keywords = OrderedDict({
"Red": ["Apple", "Strawberry"],
"Yellow": ["Banana", "Lemon", "Pineapple"],
"Purple": ["Grapes"],
"Orange": ["Orange"],
"Blue": ["Blueberry"],
"Green": ["Watermelon"],
"Brown": ["Kiwi"]
})
for category, keywords in category_keywords.items():
if any(keyword.lower() in message_text.lower() for keyword in keywords):
return category # Return color if any keyword matches
# Return the original message text if no match is found
return message_text
# Read data from Excel file
df = pd.read_excel('input.xlsx')
# Apply the categorization function directly to the DataFrame
df['Category'] = df.apply(categorizer, axis=1)
# Write the updated DataFrame back to Excel
df.to_excel('output.xlsx', index=False)
示範資料
Message | Category |
---|---|
She eats an apple every morning to start her day with a healthy snack. | Red |
The strawberry shortcake was topped with fresh, juicy strawberries. | Red |
He blends a banana into his smoothie for extra creaminess. | Yellow |
Grapes are a great option for a quick and easy snack during the day. | Purple |
The freshly squeezed orange juice tasted both sweet and tangy. | Orange |
Her favorite muffin flavor is blueberry because of the tiny bursts of sweetness. | Blue |
The tart flavor of the kiwi pairs well with the sweetness of other fruits. | Brown |
They added fresh pineapple chunks to the tropical fruit salad. | Red |
Mango smoothies are perfect for a refreshing and exotic taste. | |
Watermelon slices are a staple at summer picnics for their hydrating qualities. | Green |