零售和电子商务行业 Excel 中的 Python

# Load review data from Excel
df = xl("Table3[#All]", headers=True)

# Import NLTK resources
import nltk
from nltk.corpus import stopwords
from nltk.tokenize import word_tokenize

# Define stopwords for cleaning
stop_words = set(stopwords.words('english'))

# Function to clean and tokenize text
def clean_text(text):
tokens = word_tokenize(text.lower())  # Lowercase and tokenize
return [word for word in tokens if word.isalpha() and word not in stop_words]

# Apply text cleaning
df['CleanedReview'] = df['ReviewText'].apply(clean_text)

# Define positive and negative word lists
positive_words = ["good", "great", "excellent", "love", "amazing", "satisfied", "happy"]
negative_words = ["bad", "terrible", "poor", "hate", "disappointed", "unsatisfied", "worst"]

# Function to calculate sentiment based on word lists
def analyze_sentiment(review_tokens):
pos_count = sum(1 for word in review_tokens if word in positive_words)
neg_count = sum(1 for word in review_tokens if word in negative_words)
return "Positive" if pos_count > neg_count else "Negative" if neg_count > pos_count else "Neutral"

# Apply sentiment analysis
df['Sentiment'] = df['CleanedReview'].apply(analyze_sentiment)

# Reorder the data with specific order and colorssentiment_order = ['Negative', 'Neutral', 'Positive']colors = ['red', 'grey', 'green']
# Plot with ordered categoriesdf['Sentiment'].value_counts().reindex(sentiment_order).plot(    kind='bar',    color=colors)
plt.title("Sentiment Analysis of Customer Reviews")plt.xlabel("Sentiment")plt.ylabel("Number of Reviews")sns.despine()plt.show()

# Load sales data from Excel tabledf = xl("SalesData[#All]", headers=True)
# Group by month and category and sum the salesdf['Month'] = df['Date'].dt.to_period('M')monthly_sales = df.groupby(['Month', 'Category'])['Sales'].sum().reset_index()monthly_sales.Month = monthly_sales.Month.dt.to_timestamp()monthly_sales

# Plot monthly sales trends by category
plt.figure(figsize=(10, 6))
for category in monthly_sales['Category'].unique():
category_data = monthly_sales[monthly_sales['Category'] == category]
plt.plot(category_data['Month'].astype(str), category_data['Sales'], label=category)

plt.title("Monthly Sales Trends by Product Category")
plt.xlabel("Month")
plt.ylabel("Sales")
plt.legend(title="Category")
plt.xticks(rotation=45)
plt.show()

# Calculate total sales and growth rate for each category
category_summary = monthly_sales.groupby('Category')['Sales'].agg(['sum', 'mean'])
category_summary['Growth Rate'] = monthly_sales.groupby('Category')['Sales'].pct_change().fillna(0).mean()

# Sort categories by total sales to identify top-performing product lines
top_categories = category_summary.sort_values(by='sum', ascending=False)
top_categories

# Load subscriber data from Excel
df = xl("SubscribersData[#All]", headers=True)

from sklearn.model_selection import train_test_splitfrom sklearn.linear_model import LogisticRegressionfrom sklearn.metrics import accuracy_score, classification_reportfrom sklearn.preprocessing import StandardScaler
# Separate features and targetX = df[['Age', 'EngagementScore', 'AvgPurchaseValue', 'NumPurchases']]  # Feature columnsy = df['Churn']  # Target column
# Split the data into training and testing setsX_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3, random_state=42)
# Initialize the scaler and fit it to the training datascaler = StandardScaler()X_train = scaler.fit_transform(X_train)X_test = scaler.transform(X_test)
model = LogisticRegression()model.fit(X_train, y_train)
y_pred = model.predict(X_test)
# Evaluate model performanceprint("Accuracy:", accuracy_score(y_test, y_pred))print(classification_report(y_test, y_pred))

# Predict churn probabilities for all subscribers
df['ChurnProbability'] = model.predict_proba(X)[:, 1]

# Identify subscribers with a high churn probability
at_risk_subscribers = df[df['ChurnProbability'] > 0.5]  # Threshold for high risk
at_risk_subscribers.head()