Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • "Variable Calculation Logic in a Dataset: Subtracting 'avg_aar_before' from 'avg_aar_after'"

    Hi all

    In my dataset, I possess a dataset with observations falling within the range of 1 to 1622 for the variable "sum." Complementing this, I have variables denoted as "switch," "avg_aar_before," and "avg_aar_after." The objective is to compute the subtraction of "avg_aar_before" from "avg_aar_after." To elucidate, when the "sum" variable assumes an even value, it aligns with the "avg_aar_after" value, whereas odd values of the "sum" variable correspond to the "avg_aar_before" value. This pattern persists for all values of "sum" from 1 to 1622, following a simple logic pattern such as (2-1), (4-3), and so forth. The subtraction process is iteratively applied to all dataset entries. Kindly Help with this query


    Code:
    * Example generated by -dataex-. For more info, type help dataex
    clear
    input int original_order byte(switch sum) float(avg_aar_before avg_bar_before avg_aar_after avg_bar_after)
      1 . .         .          .         .          .
      2 . .         .          .         .          .
      3 . .         .          .         .          .
      4 . .         .          .         .          .
      5 . .         .          .         .          .
      6 . .         .          .         .          .
      7 . .         .          .         .          .
      8 . .         .          .         .          .
      9 . .         .          .         .          .
     10 . .         .          .         .          .
     11 . .         .          .         .          .
     12 . .         .          .         .          .
     13 . .         .          .         .          .
     14 . .         .          .         .          .
     15 . .         .          .         .          .
     16 . .         .          .         .          .
     17 . .         .          .         .          .
     18 . .         .          .         .          .
     19 . .         .          .         .          .
     20 . .         .          .         .          .
     21 . .         .          .         .          .
     22 . .         .          .         .          .
     23 . .         .          .         .          .
     24 . .         .          .         .          .
     25 . .         .          .         .          .
     26 . .         .          .         .          .
     27 . .         .          .         .          .
     28 . .         .          .         .          .
     29 . .         .          .         .          .
     30 . .         .          .         .          .
     31 . .         .          .         .          .
     32 . .         .          .         .          .
     33 . .         .          .         .          .
     34 1 1 .10350805 .018040802         .          .
     35 . 1         .          .         .          .
     36 . 1         .          .         .          .
     37 . 1         .          .         .          .
     38 . 1         .          .         .          .
     39 . .         .          .         .          .
     40 1 2         .          . .18747446 .022247463
     41 . 2         .          .         .          .
     42 . 2         .          .         .          .
     43 . 2         .          .         .          .
     44 . 2         .          .         .          .
     45 . .         .          .         .          .
     46 . .         .          .         .          .
     47 . .         .          .         .          .
     48 . .         .          .         .          .
     49 . .         .          .         .          .
     50 . .         .          .         .          .
     51 . .         .          .         .          .
     52 . .         .          .         .          .
     53 . .         .          .         .          .
     54 . .         .          .         .          .
     55 . .         .          .         .          .
     56 . .         .          .         .          .
     57 . .         .          .         .          .
     58 . .         .          .         .          .
     59 . .         .          .         .          .
     60 . .         .          .         .          .
     61 . .         .          .         .          .
     62 1 3 .13930307 .030586015         .          .
     63 . 3         .          .         .          .
     64 . 3         .          .         .          .
     65 . 3         .          .         .          .
     66 . 3         .          .         .          .
     67 . .         .          .         .          .
     68 . .         .          .         .          .
     69 1 4         .          . .13479441 .027604437
     70 . 4         .          .         .          .
     71 . 4         .          .         .          .
     72 . 4         .          .         .          .
     73 . 4         .          .         .          .
     74 . .         .          .         .          .
     75 . .         .          .         .          .
     76 . .         .          .         .          .
     77 . .         .          .         .          .
     78 . .         .          .         .          .
     79 . .         .          .         .          .
     80 . .         .          .         .          .
     81 . .         .          .         .          .
     82 . .         .          .         .          .
     83 . .         .          .         .          .
     84 . .         .          .         .          .
     85 . .         .          .         .          .
     86 . .         .          .         .          .
     87 . .         .          .         .          .
     88 . .         .          .         .          .
     89 . .         .          .         .          .
     90 . .         .          .         .          .
     91 . .         .          .         .          .
     92 1 5  .1675365  .02722965         .          .
     93 . 5         .          .         .          .
     94 . 5         .          .         .          .
     95 . 5         .          .         .          .
     96 . 5         .          .         .          .
     97 . .         .          .         .          .
     98 . .         .          .         .          .
     99 1 6         .          . .17935535  .02552348
    100 . 6         .          .         .          .
    101 . 6         .          .         .          .
    102 . 6         .          .         .          .
    103 . 6         .          .         .          .
    104 . .         .          .         .          .
    105 . .         .          .         .          .
    106 . .         .          .         .          .
    107 . .         .          .         .          .
    108 . .         .          .         .          .
    109 . .         .          .         .          .
    110 . .         .          .         .          .
    111 . .         .          .         .          .
    112 . .         .          .         .          .
    113 . .         .          .         .          .
    114 . .         .          .         .          .
    115 . .         .          .         .          .
    116 . .         .          .         .          .
    117 . .         .          .         .          .
    118 . .         .          .         .          .
    119 1 7 .16191193  .02544296         .          .
    120 . 7         .          .         .          .
    121 . 7         .          .         .          .
    122 . 7         .          .         .          .
    123 . 7         .          .         .          .
    124 . .         .          .         .          .
    125 . .         .          .         .          .
    126 1 8         .          .  .1609211  .02792638
    127 . 8         .          .         .          .
    128 . 8         .          .         .          .
    129 . 8         .          .         .          .
    130 . 8         .          .         .          .
    131 . .         .          .         .          .
    132 . .         .          .         .          .
    133 . .         .          .         .          .
    134 . .         .          .         .          .
    135 . .         .          .         .          .
    136 . .         .          .         .          .
    137 . .         .          .         .          .
    138 . .         .          .         .          .
    139 . .         .          .         .          .
    140 . .         .          .         .          .
    141 . .         .          .         .          .
    142 . .         .          .         .          .
    143 . .         .          .         .          .
    144 . .         .          .         .          .
    145 . .         .          .         .          .
    146 . .         .          .         .          .
    147 . .         .          .         .          .
    148 . .         .          .         .          .
    149 . .         .          .         .          .
    150 . .         .          .         .          .
    end



  • #2
    I think you are looking for something like this:

    Code:
    sort switch sum
    gen diff_avg_aar = avg_aar_after - avg_aar_before[_n - 1]

    Comment


    • #3
      This is a rather strange data layout--lots of observations with no data at all, and observations that you wish to relate to each other scattered at a distance in the data set. One of the most compelling use-cases for -frame-s is precisely this setup.

      Code:
      frame put sum avg_aar* if !missing(avg_aar_before) | !missing(avg_aar_after), into(working)
      frame working {
          gen int pair = ceil(sum/2)
          by pair (sum), sort: gen wanted = avg_aar_after[2]-avg_aar_before[1]
      }
      frlink m:1 sum, frame(working)
      frget wanted, from(working)
      Your question says what you want to calculate, but doesn't state where/how you wish to store the results. You will see what I have done with it. If you had something else in mind, you can revise the code to place the results where you wish.

      Added: Crossed with #2. The solution there is simpler, and, I would say, better than mine. One limitation of that approach is that it is not apparent how to restore the original organization of the data, if that is necessary.
      Last edited by Clyde Schechter; 20 Nov 2023, 10:09.

      Comment


      • #4
        Clyde Schechter looks like Reddy has been working on some kind of data management task for a few days now. Based on his previous post it looks like the data was originally some kind of time series.

        Comment


        • #5
          Dear Daniel and Clyde,

          I hope this message finds you well. Recently, I've been immersed in data management and tackling some challenging tasks. I'm eager to see the results and have decided to work with both of your codes. I will diligently review and test them, providing my feedback shortly.

          Comment

          Working...
          X