Calculating mean on a value in a certain period in the same varlist

[Nicholas Juan]

Firm Sector Quarter TradePayable
TMPO Communication Services 2013Q1 .0500968
ISAT Communication Services 2013Q1 .007206
BMTR Communication Services 2013Q1 .0521027
CENT Communication Services 2013Q1 .0966917
MNCN Communication Services 2013Q1 .0504886
EMTK Communication Services 2013Q1 .0307749
TLKM Communication Services 2013Q1 .0614018
ABBA Communication Services 2013Q1 .1307011
MSKY Communication Services 2013Q1 .1275893
FORU Communication Services 2013Q1 .2096529
BHIT Communication Services 2013Q1 .0429413
KBLV Communication Services 2013Q1 .0902451
BMTR Communication Services 2013Q2 .049237
FORU Communication Services 2013Q2 .279485
KBLV Communication Services 2013Q2 .0697843
EMTK Communication Services 2013Q2 .0304034
MSKY Communication Services 2013Q2 .1236343
BHIT Communication Services 2013Q2 .0359511
TMPO Communication Services 2013Q2 .0403681
TLKM Communication Services 2013Q2 .0746017
ISAT Communication Services 2013Q2 .0077352
CENT Communication Services 2013Q2 .0351647
MNCN Communication Services 2013Q2 .0707349
IBST Communication Services 2013Q2 .0029691
ABBA Communication Services 2013Q2 .1269866


Here is a piece of my data. I have a problem where I am trying to calculate the mean for "TradePayable" in each quarter
Before I've tried the egen IndTP = mean(TradePayable), by(Quarter), even though the format is right, the results (mean average) were all awfully wrong, since I do double check on the calculations by summing up tradepayables for 2013Q1 only and divide it by 12.

Does the error have to do with stata's different method of calculating the mean? Appreciate the help!

[Nick Cox]

Stata doesn't use any calculation of the mean that isn't what you would expect.

What we need here is not a listing but output from dataex as requested in FAQ Advice #12.

There is one scenario in which the means would be very different from what you expect.

1. The data for TradePayable were imported as string

2. You converted them to numeric using encode. By default, the results would then be integers 1 up. The original string variable would become a set of value labels.

3. In your case means then would typically be (much) larger than 1.

If this isn't the story, please run

dataex Firm Sector Quarter TradePayable

and copy and paste the results, incliding CODE delimiters, to this thread.

(On this scenario, the proper solution to misreading numeric data as string would be to use destring. See e.g.
https://journals.sagepub.com/doi/pdf...867X1801800413 for a longer story there.)

[Nick Cox]

Here is a demonstration of how you could get utterly absurd results after a mistaken encode. From a Stata point of view, and as in many fairy tales or myths, the punishment is that you get what you asked for..

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 Quarter str8 TradePayable
"2013Q1" ".0500968"
"2013Q1" ".007206" 
"2013Q1" ".0521027"
"2013Q1" ".0966917"
"2013Q1" ".0504886"
"2013Q1" ".0307749"
"2013Q1" ".0614018"
"2013Q1" ".1307011"
"2013Q1" ".1275893"
"2013Q1" ".2096529"
"2013Q1" ".0429413"
"2013Q1" ".0902451"
"2013Q2" ".049237" 
"2013Q2" ".279485" 
"2013Q2" ".0697843"
"2013Q2" ".0304034"
"2013Q2" ".1236343"
"2013Q2" ".0359511"
"2013Q2" ".0403681"
"2013Q2" ".0746017"
"2013Q2" ".0077352"
"2013Q2" ".0351647"
"2013Q2" ".0707349"
"2013Q2" ".0029691"
"2013Q2" ".1269866"
end

. encode TradePayable, gen(BadIdea)

. l

     +-------------------------------+
     | Quarter   TradeP~e    BadIdea |
     |-------------------------------|
  1. |  2013Q1   .0500968   .0500968 |
  2. |  2013Q1    .007206    .007206 |
  3. |  2013Q1   .0521027   .0521027 |
  4. |  2013Q1   .0966917   .0966917 |
  5. |  2013Q1   .0504886   .0504886 |
     |-------------------------------|
  6. |  2013Q1   .0307749   .0307749 |
  7. |  2013Q1   .0614018   .0614018 |
  8. |  2013Q1   .1307011   .1307011 |
  9. |  2013Q1   .1275893   .1275893 |
 10. |  2013Q1   .2096529   .2096529 |
     |-------------------------------|
 11. |  2013Q1   .0429413   .0429413 |
 12. |  2013Q1   .0902451   .0902451 |
 13. |  2013Q2    .049237    .049237 |
 14. |  2013Q2    .279485    .279485 |
 15. |  2013Q2   .0697843   .0697843 |
     |-------------------------------|
 16. |  2013Q2   .0304034   .0304034 |
 17. |  2013Q2   .1236343   .1236343 |
 18. |  2013Q2   .0359511   .0359511 |
 19. |  2013Q2   .0403681   .0403681 |
 20. |  2013Q2   .0746017   .0746017 |
     |-------------------------------|
 21. |  2013Q2   .0077352   .0077352 |
 22. |  2013Q2   .0351647   .0351647 |
 23. |  2013Q2   .0707349   .0707349 |
 24. |  2013Q2   .0029691   .0029691 |
 25. |  2013Q2   .1269866   .1269866 |
     +-------------------------------+

. egen mean = mean(BadIdea), by(Quarter)

. l, nola

     +-----------------------------------------+
     | Quarter   TradeP~e   BadIdea       mean |
     |-----------------------------------------|
  1. |  2013Q1   .0500968        11   14.33333 |
  2. |  2013Q1    .007206         2   14.33333 |
  3. |  2013Q1   .0521027        13   14.33333 |
  4. |  2013Q1   .0966917        19   14.33333 |
  5. |  2013Q1   .0504886        12   14.33333 |
     |-----------------------------------------|
  6. |  2013Q1   .0307749         5   14.33333 |
  7. |  2013Q1   .0614018        14   14.33333 |
  8. |  2013Q1   .1307011        23   14.33333 |
  9. |  2013Q1   .1275893        22   14.33333 |
 10. |  2013Q1   .2096529        24   14.33333 |
     |-----------------------------------------|
 11. |  2013Q1   .0429413         9   14.33333 |
 12. |  2013Q1   .0902451        18   14.33333 |
 13. |  2013Q2    .049237        10   11.76923 |
 14. |  2013Q2    .279485        25   11.76923 |
 15. |  2013Q2   .0697843        15   11.76923 |
     |-----------------------------------------|
 16. |  2013Q2   .0304034         4   11.76923 |
 17. |  2013Q2   .1236343        20   11.76923 |
 18. |  2013Q2   .0359511         7   11.76923 |
 19. |  2013Q2   .0403681         8   11.76923 |
 20. |  2013Q2   .0746017        17   11.76923 |
     |-----------------------------------------|
 21. |  2013Q2   .0077352         3   11.76923 |
 22. |  2013Q2   .0351647         6   11.76923 |
 23. |  2013Q2   .0707349        16   11.76923 |
 24. |  2013Q2   .0029691         1   11.76923 |
 25. |  2013Q2   .1269866        21   11.76923 |
     +-----------------------------------------+

[Carlo Lazzaro]

Nicholas:
welcome to this forum.
Following a totally naive approach (copy and paste from a spreadsheet and playing with -string- variables via the -group- function avaulable from -egen):

. egen new_id=group( firm)

. list

     +----------------------------------------------------------------+
     |   firm                    sector   quarter   trade_~e   new_id |
     |----------------------------------------------------------------|
  1. |  TMPO    Communication Services     2013Q1    .050097       21 |
  2. | ISAT     Communication Services     2013Q1    .007206       12 |
  3. |   BMTR   Communication Services     2013Q1    .052103        4 |
  4. |   CENT   Communication Services     2013Q1    .096692        5 |
  5. |   MNCN   Communication Services     2013Q1    .050489       15 |
     |----------------------------------------------------------------|
  6. |  EMTK    Communication Services     2013Q1    .030775        8 |
  7. |   TLKM   Communication Services     2013Q1    .061402       18 |
  8. |   ABBA   Communication Services     2013Q1    .130701        1 |
  9. |   MSKY   Communication Services     2013Q1    .127589       16 |
 10. |   FORU   Communication Services     2013Q1    .209653        9 |
     |----------------------------------------------------------------|
 11. |   BHIT   Communication Services     2013Q1    .042941        2 |
 12. |   KBLV   Communication Services     2013Q1    .090245       13 |
 13. |   BMTR   Communication Services     2013Q2    .049237        4 |
 14. |   FORU   Communication Services     2013Q2    .279485        9 |
 15. |  KBLV    Communication Services     2013Q2    .069784       14 |
     |----------------------------------------------------------------|
 16. |   EMTK   Communication Services     2013Q2    .030403        7 |
 17. |  MSKY    Communication Services     2013Q2    .123634       17 |
 18. |  BHIT    Communication Services     2013Q2    .035951        3 |
 19. |   TMPO   Communication Services     2013Q2    .040368       20 |
 20. |  TLKM    Communication Services     2013Q2    .074602       19 |
     |----------------------------------------------------------------|
 21. |  ISAT    Communication Services     2013Q2    .007735       11 |
 22. |  CENT    Communication Services     2013Q2    .035165        6 |
 23. |   MNCN   Communication Services     2013Q2    .070735       15 |
 24. |   IBST   Communication Services     2013Q2    .002969       10 |
 25. |   ABBA   Communication Services     2013Q2    .126987        1 |
     +----------------------------------------------------------------+

. tab firm

      Firm  |      Freq.     Percent        Cum.
------------+-----------------------------------
       ABBA |          2        8.00        8.00
       BHIT |          1        4.00       12.00
      BHIT  |          1        4.00       16.00
       BMTR |          2        8.00       24.00
       CENT |          1        4.00       28.00
      CENT  |          1        4.00       32.00
       EMTK |          1        4.00       36.00
      EMTK  |          1        4.00       40.00
       FORU |          2        8.00       48.00
       IBST |          1        4.00       52.00
      ISAT  |          1        4.00       56.00
     ISAT   |          1        4.00       60.00
       KBLV |          1        4.00       64.00
      KBLV  |          1        4.00       68.00
       MNCN |          2        8.00       76.00
       MSKY |          1        4.00       80.00
      MSKY  |          1        4.00       84.00
       TLKM |          1        4.00       88.00
      TLKM  |          1        4.00       92.00
       TMPO |          1        4.00       96.00
      TMPO  |          1        4.00      100.00
------------+-----------------------------------
      Total |         25      100.00



. egen new_quarter=group( quarter )

. mean trade_payable, over( new_quarter )

Mean estimation                                            Number of obs = 25

-----------------------------------------------------------------------------
                            |       Mean   Std. err.     [95% conf. interval]
----------------------------+------------------------------------------------
c.trade_payable@new_quarter |
                         1  |   .0791577   .0160503      .0460314     .112284
                         2  |   .0728504   .0201825      .0311957    .1145051
-----------------------------------------------------------------------------

.