订单数据可视化生成空白图表？原因排查及修复方案

需求说明：

• 匹配用户输入的日期，按Customer No对比订单数据
• 同一Customer No对应Non-Regular和Regular两类客户行时，需选取订单金额与订单数量之和最低的行
• 为每个Customer No单独绘制图表，x轴为Order Time，y轴为Order Quantity

数据样例：

Customer No | Customer Type | Order Time |  Order Amount |Order Quantity
120023 | Non Regular| 25/1/2023 00:00:00 |  470.92 |    13
120023 | Regular         |25/1/2023 00:00:00  | 64.03   |             7
120024 |Non Regular  |  25/1/2023 00:00:00 |    143.18  |   3
120024  |Regular           |25/1/2023 00:00:00 |    143.18  |            3
120025  |Non Regular   |25/1/2023 00:00:00 |    222.14  |            1
120025  |Regular            |25/1/2023 00:00:00 |   222.14  |   1
120006  |Non Regular    |25/1/2023 00:00:00 |   157.18  |            5
120007  |Non Regular    |25/1/2023 00:00:00 |   151.51  |   7
120008  |Non Regular    |25/1/2023 00:00:00 |   99.7    |            7 
119990  |Non Regular    |25/1/2023 00:00:00 |   212.48  |            1
119991  |Non Regular    |25/1/2023 00:00:00 |   96.53   |            3
119992  |Non Regular    |25/1/2023 00:00:00 |   62.72   |            7
119993 |Non Regular |25/1/2023 00:00:00 |   87.89    |            7
119994  |Non Regular    |25/1/2023 00:00:00  | 96.56    |            1
119995 |Non Regular |25/1/2023 00:00:00 | 95.44 |           1
119996  |Non Regular    |25/1/2023 00:00:00 | 97.03 |           3

运行下方代码后得到空白图表，分析原因并给出修复方法：

import pandas as pd
import datetime
import ipywidgets as widgets
import numpy as np
import matplotlib.pyplot as plt

df = pd.read_excel('FoodOrder.xlsx')

input_date = input("Enter a date (format: yyyy/mm/dd): ")
input_date = datetime.datetime.strptime(input_date, "%Y/%m/%d")

df['Order Time'] = pd.to_datetime(df['Order Time'])

df['time_diff'] = (df['Order Time'] - input_date).dt.days

df_filtered = df[(df['time_diff'] >= -20) & (df['time_diff'] <= 20)]


df_grouped = df_filtered.groupby('Customer No').apply(lambda x: x.loc[x['Order Amount'].idxmax()] if x['Order Amount '].max() > x['Order Quantity'].max() else x.loc[x['Order Quantity'].idxmax()])


# Reset the index of the grouped dataframe
df_grouped = df_grouped.reset_index(drop=True)


# Define a plotting function
def plot_customer(x):
    plt.plot(x['Order Time'], x['Order Amount'])
    plt.xlabel('Order Time')
    plt.ylabel('Order Amount')
    plt.title('Order Amount vs Order Time for Customer No: ' + str(x['Customer No'].iloc[0]))
    plt.gcf().autofmt_xdate()
    plt.ylim(0, x['Order Amount'].max() * 1.1)
    plt.show()

    plt.show()

# Apply the plotting function to each group
df_grouped.groupby('Customer No').apply(plot_customer)

1. 筛选逻辑完全偏离需求：需求是取订单金额+订单数量最低的行，但代码用idxmax()取最大值行，且判断条件错误比较两类最大值，完全不符合要求；另外x['Order Amount ']多了空格，可能导致列名查找失败。
2. 分组后数据结构问题：df_grouped每个Customer No仅保留一行数据，再次分组后每个分组只有单个数据点，plt.plot()默认不显示单个点，导致图表空白。
3. 绘图逻辑错误：需求要求y轴为Order Quantity，但代码绘制的是Order Amount，标题和轴标签也对应错误。

import pandas as pd
import datetime
import matplotlib.pyplot as plt

df = pd.read_excel('FoodOrder.xlsx')

# 获取用户输入日期并转换格式
input_date = input("Enter a date (format: yyyy/mm/dd): ")
input_date = datetime.datetime.strptime(input_date, "%Y/%m/%d")

# 转换订单时间为datetime类型，适配日/月/年格式
df['Order Time'] = pd.to_datetime(df['Order Time'], dayfirst=True)

# 计算时间差并筛选20天内的数据
df['time_diff'] = (df['Order Time'] - input_date).dt.days
df_filtered = df[(df['time_diff'] >= -20) & (df['time_diff'] <= 20)]

# 按Customer No分组，选取订单金额+订单数量之和最小的行
def select_min_row(group):
    group['sum_amount_qty'] = group['Order Amount'] + group['Order Quantity']
    return group.loc[group['sum_amount_qty'].idxmin()]

df_grouped = df_filtered.groupby('Customer No').apply(select_min_row).reset_index(drop=True)

# 定义绘图函数：绘制订单时间vs订单数量
def plot_customer(group):
    # 单个数据点用散点图显示
    plt.scatter(group['Order Time'], group['Order Quantity'], s=100, marker='o')
    plt.xlabel('Order Time')
    plt.ylabel('Order Quantity')
    plt.title(f'Order Quantity vs Order Time for Customer No: {group["Customer No"].iloc[0]}')
    plt.gcf().autofmt_xdate()
    # 调整y轴范围确保点清晰显示
    plt.ylim(0, group['Order Quantity'].max() * 1.2)
    plt.show()

# 按Customer No分组绘图
df_grouped.groupby('Customer No').apply(plot_customer)

• 修正筛选逻辑：新增sum_amount_qty列计算金额+数量之和，按分组选取和最小的行，匹配需求。
• 修复日期解析：指定dayfirst=True适配数据中的日/月/年格式，避免日期解析错误。
• 调整绘图方式：用plt.scatter()绘制单个数据点，确保内容可见；同时修正y轴、标题和标签为需求指定的内容。
• 修正列名笔误：去掉Order Amount后的多余空格，避免列名查找错误。

内容的提问来源于stack exchange，提问作者Amelia R.