CODEWITHSUNDEEP
stata
Amin Karimi
Amin Karimi

Reputation: 407

Aggregating a variable per time value

I want to generate the Price2 variable which is a sum of price value by each second and add this new value to the last row of each time values which are the same.

Here is a sample of my data (except for Price2 variable). The result is the price2 var. which is shown below. Day and Time variables are strings.

    +------+-----------+----------+-------+-------------+
    | Name |    Day    |   Time   | Price |   Price2    |
    +------+-----------+----------+-------+-------------+
    | A    | 24-Mar-08 | 10:30:01 |     1 |            .|
    | A    | 24-Mar-08 | 10:30:01 |     4 |            .|
    | A    | 24-Mar-08 | 10:30:01 |     3 |            8|
    | A    | 24-Mar-08 | 11:03:12 |     1 |            .|
    | A    | 24-Mar-08 | 11:03:12 |     4 |            5|
    | A    | 25-Mar-08 | 10:30:01 |     3 |            .|
    | A    | 25-Mar-08 | 10:30:01 |     8 |           11|
    | A    | 25-Mar-08 | 11:13:59 |     3 |            .|
    | A    | 25-Mar-08 | 11:13:59 |     2 |            .|
    | A    | 25-Mar-08 | 11:13:59 |     5 |            .|
    | A    | 25-Mar-08 | 11:13:59 |     3 |           13|
    | A    | 25-Mar-08 | 11:59:01 |     1 |            1|
    | B    | 24-Mar-08 | 10:30:01 |     3 |            .|
    | B    | 24-Mar-08 | 10:30:01 |     4 |            7|
    | B    | 24-Mar-08 | 11:30:01 |     3 |            .|
    | B    | 24-Mar-08 | 11:30:01 |     5 |            8|
    | B    | 25-Mar-08 | 11:30:01 |     7 |            .|
    | B    | 25-Mar-08 | 11:30:01 |     4 |            .|
    | B    | 25-Mar-08 | 11:30:01 |     2 |           13|
    | B    | 25-Mar-08 | 12:00:00 |     6 |            6|
    +------+-----------+----------+-------+-------------+

How can I calculate Price2 variable using Stata?

Upvotes: 0

Views: 26

Answers (1)

Nick Cox
Nick Cox

Reputation: 37208

Please note that data as code, as below, and as in my answer to your previous thread, is much easier for people to work with. This is similar in spirit to your example above. 

clear
input str1 Name str9(Day Time) float(Price Size)
"A" "24-Mar-08" "10:30:01" 1 3
"A" "24-Mar-08" "10:30:01" 4 4
"A" "24-Mar-08" "10:30:01" 3 2
"A" "24-Mar-08" "11:03:12" 1 4
"A" "24-Mar-08" "11:03:12" 4 1
"A" "25-Mar-08" "10:30:01" 3 4
"A" "25-Mar-08" "10:30:01" 8 2
"A" "25-Mar-08" "11:13:59" 3 2
"A" "25-Mar-08" "11:13:59" 2 4
"A" "25-Mar-08" "11:13:59" 5 5
"A" "25-Mar-08" "11:13:59" 3 3
"A" "25-Mar-08" "11:59:01" 1 5
"B" "24-Mar-08" "10:30:01" 3 6
"B" "24-Mar-08" "10:30:01" 4 1
"B" "24-Mar-08" "11:30:01" 3 2
"B" "24-Mar-08" "11:30:01" 5 1
"B" "25-Mar-08" "11:30:01" 7 3
"B" "25-Mar-08" "11:30:01" 4 6
"B" "25-Mar-08" "11:30:01" 2 2
"B" "25-Mar-08" "12:00:00" 6 2
end

bysort Name Day Time: egen wanted = total(Price)
by Name Day Time: replace wanted = . if _n < _N 

list, sepby(Name Day Time) 

     +-----------------------------------------------------+
     | Name         Day       Time   Price   Size   wanted |
     |-----------------------------------------------------|
  1. |    A   24-Mar-08   10:30:01       1      3        . |
  2. |    A   24-Mar-08   10:30:01       4      4        . |
  3. |    A   24-Mar-08   10:30:01       3      2        8 |
     |-----------------------------------------------------|
  4. |    A   24-Mar-08   11:03:12       1      4        . |
  5. |    A   24-Mar-08   11:03:12       4      1        5 |
     |-----------------------------------------------------|
  6. |    A   25-Mar-08   10:30:01       3      4        . |
  7. |    A   25-Mar-08   10:30:01       8      2       11 |
     |-----------------------------------------------------|
  8. |    A   25-Mar-08   11:13:59       3      2        . |
  9. |    A   25-Mar-08   11:13:59       2      4        . |
 10. |    A   25-Mar-08   11:13:59       5      5        . |
 11. |    A   25-Mar-08   11:13:59       3      3       13 |
     |-----------------------------------------------------|
 12. |    A   25-Mar-08   11:59:01       1      5        1 |
     |-----------------------------------------------------|
 13. |    B   24-Mar-08   10:30:01       3      6        . |
 14. |    B   24-Mar-08   10:30:01       4      1        7 |
     |-----------------------------------------------------|
 15. |    B   24-Mar-08   11:30:01       3      2        . |
 16. |    B   24-Mar-08   11:30:01       5      1        8 |
     |-----------------------------------------------------|
 17. |    B   25-Mar-08   11:30:01       7      3        . |
 18. |    B   25-Mar-08   11:30:01       4      6        . |
 19. |    B   25-Mar-08   11:30:01       2      2       13 |
     |-----------------------------------------------------|
 20. |    B   25-Mar-08   12:00:00       6      2        6 |
     +-----------------------------------------------------+

Upvotes: 2

Related Questions