R语言神经网络Shift数据集构建：汇率时间序列MLP输入生成

Got it, let's break this down step by step since you're looking to prep your exchange rate data for MLP time series prediction using that 3-lag formula.

Your formula Y(t+1) = f(Y(t), Y(t-1), Y(t-2)) means we're using three consecutive past exchange rates to predict the next day's rate. To put it in plain terms:

• For each target value (next day's rate, Y(t+1)), we need the rate from the current day (Y(t)), the day before that (Y(t-1)), and two days prior (Y(t-2)).
• This will give us 4 columns total: 3 input columns and 1 target column for your MLP.

Let's assume your raw exchange rate data is in column A, starting at cell A2 (with A1 as the header "Exchange Rate"). Here's how to build your 4-column dataset:

1. Add headers for your new columns in row 1:

   • B1: Y(t-2) (2 days prior)
   • C1: Y(t-1) (1 day prior)
   • D1: Y(t) (Current day)
   • E1: Y(t+1) (Target - Next day)

2. Populate the first valid row (row 4, since we need 3 prior days to predict the 4th):

   • B4 = A2 (2 days before the target day)
   • C4 = A3 (1 day before the target day)
   • D4 = A4 (the day immediately before the target)
   • E4 = A5 (the target value we want to predict)

3. Fill down the formulas
   Select cells B4:E4, then drag the fill handle (small square at the bottom-right of the selection) down to the last row where you have a valid target value. Excel will automatically adjust the cell references for you.

Example of Your Processed Data #

Here's what the first few rows will look like with your provided rates:

• Handle missing rows: You'll notice the last few rows of your raw data won't have a corresponding target value (since there's no "next day" data). Just delete those incomplete rows—they can't be used for training/testing. For your 12 provided rates, you'll end up with 9 valid rows of training data.
• Normalize your data: MLPs are sensitive to data scales. Before feeding into your model, scale all values to a range like [0,1] or [-1,1]. In Excel, you can use this formula (assuming your min rate is in A2 and max in A13):
  =(A2-MIN($A$2:$A$13))/(MAX($A$2:$A$13)-MIN($A$2:$A$13))
• Time-based train/test split: Don't randomly shuffle your data! For time series, split your dataset so the first ~80% is training data and the last ~20% is test data—this mimics real-world prediction where you don't have future data.

内容的提问来源于stack exchange，提问作者S.Student