使用R编程为缺失记录的日期-州组合生成历史数据虚拟行

Hey there! Let's work through this problem together—it's a super common task when dealing with time-series data across different groups (like states here). I'll walk you through using the tidyverse tools since they're intuitive for R beginners and perfect for this kind of data wrangling.

Step 1: Prepare your environment and data #

First, make sure you have the necessary packages installed and loaded. We'll use tidyverse for data manipulation and lubridate to handle date formats easily:

# Install packages if you haven't already
install.packages(c("tidyverse", "lubridate"))

# Load the packages
library(tidyverse)
library(lubridate)

Next, convert the Sale_date column from character strings to actual date objects—this is crucial for working with time-series data correctly:

# Assume your original data frame is named sales_data
sales_data <- sales_data %>%
  mutate(Sale_date = dmy(Sale_date))  # dmy() handles day/month/year format

Step 2: Generate all date-state combinations #

We need to create a "full grid" of every date present in your data paired with every state. The complete() function will do this automatically, adding rows for any date-state pair that's missing from your original data:

full_sales_grid <- sales_data %>%
  complete(Sale_date, Sale_State)

At this point, the missing rows (like Kerala on 3/2/2020) will have NA values for units_sold and Cummulative_unit_sold.

Step 3: Fill missing values with the most recent valid data #

Now we'll use the fill() function to replace those NAs with the latest non-missing values from the same state. We'll group by Sale_State first to ensure we only pull historical data from the correct state:

final_sales_data <- full_sales_grid %>%
  group_by(Sale_State) %>%
  # Fill downwards with the last valid value for each column
  fill(units_sold, Cummulative_unit_sold, .direction = "down") %>%
  ungroup()

What this does exactly: #

• For Kerala on 3/2/2020: It pulls the last valid data from Kerala's previous entry (2/2/2020: units_sold = 2, Cummulative_unit_sold = 4)
• For Tamil Nadu on 4/2/2020: It uses the last valid entry from Tamil Nadu (3/2/2020: units_sold = 1, Cummulative_unit_sold = 1)

Verify the output #

If you print final_sales_data, you'll see exactly the structure you wanted—every date has a row for every state, with missing records filled in using the most recent valid data from that state.

内容的提问来源于stack exchange，提问作者Hemanth_Joshi