如何在Stata中按秒计算Price2变量（加权运算实现）

Got it, let's walk through exactly how to compute your Price2 variable in Stata. The core logic is multiplying Price by Size, then dividing that product by the total Size within each second-level group. Here's the step-by-step breakdown:

Step 1: Ensure you have a second-level grouping variable #

First, you need a variable that defines each second-level group. If your data already has a variable (e.g., second_id) that groups observations by the same second, skip this step. If not, generate it from your timestamp variable:

// Example: If your timestamp is a Stata datetime variable (e.g., named "event_time")
gen second_group = date(event_time, "DMYhms") * 86400 + hh(event_time)*3600 + mm(event_time)*60 + ss(event_time)

// Alternative: If timestamp is in milliseconds (numeric, e.g., Unix epoch ms)
gen second_group = floor(event_time / 1000)

Step 2: Compute Price2 with grouped total Size #

Once you have your second-level group variable, use bysort to calculate the total Size per group, then compute Price2:

Simplified one-line version #

This is the most concise way to do it, combining the group sum and variable calculation into one command:

bysort second_group: gen Price2 = (Price * Size) / sum(Size)[_N]

Detailed step-by-step version #

If you prefer to see the intermediate total size variable for verification:

// Calculate cumulative Size per group, then retain the total sum for all observations in the group
bysort second_group: gen total_size_per_second = sum(Size)
bysort second_group: replace total_size_per_second = total_size_per_second[_N]

// Compute Price2
gen Price2 = (Price * Size) / total_size_per_second

Step 3: Handle edge cases (optional) #

If your data has missing values for Price/Size or groups where total Size is 0, add safeguards to avoid errors:

bysort second_group: gen total_size_per_second = sum(Size) if Size != . & Size > 0
bysort second_group: replace total_size_per_second = total_size_per_second[_N]

// Set Price2 to missing if total size is invalid, otherwise compute
gen Price2 = cond(total_size_per_second == 0 | total_size_per_second == ., ., (Price * Size)/total_size_per_second)

Key explanations #

• bysort second_group: tells Stata to perform the following operations separately for each unique second-level group.
• sum(Size)[_N] calculates the cumulative sum of Size within the group, then [_N] grabs the final value of that cumulative sum (which equals the total Size for the group).
• The cond() function in the edge case handling checks for invalid total sizes and sets Price2 to missing instead of producing an error.

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