用for循环导入和格式化数据输入

Question

我需要导入3个.xlsx文件并将它们合并到1个DataFrame中。我想通过使用for循环来避免重复代码。

原始代码：

filepath_1 = input('Enter Revenue Month M1 File Path: ')
revenue_month_1 = pd.read_excel(filepath_1)
revenue_month_1 = revenue_month_1.apply(pd.to_numeric, errors='ignore')
revenue_month_1['Month'] = pd.to_datetime(revenue_month_1['Month'], format='%Y%m', errors='coerce').dropna()

filepath_2 = input('Enter Revenue Month M2 File Path: ')
revenue_month_2 = pd.read_excel(filepath_2)
revenue_month_2 = revenue_month_2.apply(pd.to_numeric, errors='ignore')
revenue_month_2['Month'] = pd.to_datetime(revenue_month_2['Month'], format='%Y%m', errors='coerce').dropna()

filepath_3 = input('Enter Revenue Month M3 File Path: ')
revenue_month_3 = pd.read_excel(filepath_3)
revenue_month_3 = revenue_month_3.apply(pd.to_numeric, errors='ignore')
revenue_month_3['Month'] = pd.to_datetime(revenue_month_3['Month'], format='%Y%m', errors='coerce').dropna()

循环代码：

revenue_reports = [
    input('Enter Revenue Month M1 File Path: '),
    input('Enter Revenue Month M2 File Path: '),
    input('Enter Revenue Month M3 File Path: '),
    ]

revenue = []

for revenue_report in revenue_reports:
    revenue = pd.read_excel(revenue_report)
    revenue = revenue.apply(pd.to_numeric, errors='ignore')
    revenue['Month'] = pd.to_datetime(revenue['Month'], format='%Y%m', errors='coerce').dropna()
    revenue = revenue.append(revenue)

基于这个for循环，我只从导入的3个月数据中获得上个月的数据(M3)。你能帮忙吗？

更新:解决了问题。感谢您在下面的评论中给出的想法。我修改了一点。看起来是这样的：

revenue_reports = [
    input('Enter Revenue Month M1 File Path: '),
    input('Enter Revenue Month M2 File Path: '),
    input('Enter Revenue Month M3 File Path: '),
]

revenue = []

x = 1
for revenue_report in revenue_reports:
    revenue_monthly = pd.read_excel(revenue_report)
    revenue_monthly = revenue_monthly.apply(pd.to_numeric, errors='ignore')
    revenue_monthly["M"+str(x)] = pd.to_datetime(revenue_monthly['Month'], format='%Y%m', errors='coerce').dropna()
    x += 1
    revenue.append(revenue_monthly)
    
revenue = pd.concat(revenue)

Answer 1

您的代码很好，但问题是要用前面的值替换新值，这就是为什么您只得到M3的最后一个值

试试这个

revenue_reports = [
    input('Enter Revenue Month M1 File Path: '),
    input('Enter Revenue Month M2 File Path: '),
    input('Enter Revenue Month M3 File Path: '),
]

revenue = {}
x = 1
for revenue_report in revenue_reports:
    revenue_val = pd.read_excel(revenue_report)
    revenue_val = revenue_val.apply(pd.to_numeric, errors='ignore')
    revenue["M"+x] = pd.to_datetime(revenue_val['Month'], format='%Y%m', errors='coerce').dropna()
    x += 1

Answer 2

可能只使用一个新变量来存储所有收入数据。

revenue_reports = [
    input('Enter Revenue Month M1 File Path: '),
    input('Enter Revenue Month M2 File Path: '),
    input('Enter Revenue Month M3 File Path: '),
    ]

All_revenue =[]

revenue = []

for revenue_report in revenue_reports:
    revenue = pd.read_excel(revenue_report)
    revenue = revenue.apply(pd.to_numeric, errors='ignore')
    revenue['Month'] = pd.to_datetime(revenue['Month'], format='%Y%m', errors='coerce').dropna()
    All_revenue = All_revenue.append(revenue)