Graphing a subsection of panel data

[Kate Pryce]

Hello all,

I'm working with panel data covering the company information of firms in the S&P500 between 1980 - 2020.

Specifically, I'm following how companys growth rates change over the years—following a subsection of companies (CompaniesToFollow) and seeing how they compare to the rest of the S&P500 over this 40 year period. I'm trying to visualize how the firms I'm following compare to the S&P500 firms, so I've divided the S&P500 performance into quartiles and generated a mean growth rate for each quartile using:

 bys quartiles FiscalYear: egen QuartileMeanGR = mean(mean_SetGrowthRate)

I've also created a mean growth rate for the companies in my CompanyToFollow group.

I want to create a line graph with FiscalYear on the X axis, Mean Growth Rate on the Y, with fives lines on the graph (Quartile 1, Quartile 2, 3, 4, and my Companies to Follow).

I've found a bad way to do this where I collapse all the data by each quartile individually and then transfer the results over to excel to build the graph. Is there a way to do this where I don't delete (collapse) my data for each category that I want to collect information from?

This is what I do for each quartile:

drop if quartiles>1
collapse QuartilesMeanGR, by(FiscalYear)

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 gvkey int FiscalYear float quartiles double mean_SetGrowthRate float CompaniesToFollow
"001010" 1980 . . .
"001010" 1981 . . .
"001010" 1982 . . .
"001010" 1983 . . .
"001010" 1984 . . .
"001013" 1999 4 .34133187830448153 .
"001013" 2000 4 .42130912840366364 .
"001013" 2001 4 .28497938215732577 .
"001013" 2002 3 .09092091619968415 .
"001013" 2003 1 .0015539586544036864 .
"001013" 2004 1 -.07490771505981683 .
"001013" 2005 1 -.11801133845001459 .
"001013" 2006 1 -.044893361069262026 .
"001013" 2007 2 .07418759074062109 .
"001040" 1980 . . .
"001040" 1981 . . .
"001040" 1982 . . .
"001040" 1983 . . .
"001040" 1984 . . .
"001045" 1980 . . .
"001045" 1981 . . .
"001045" 1982 . . .
"001045" 1983 . . .
"001045" 1984 . . .
"001045" 1985 3 .1002859465777874 .
"001045" 1986 3 .08154487237334251 .
"001045" 1987 3 .11742915362119674 .
"001045" 1988 3 .13454309850931168 .
"001045" 1989 3 .14726985692977906 .
"001045" 1990 3 .1418969750404358 .
"001045" 1991 4 .1655003696680069 .
"001045" 1992 4 .14971145242452621 .
"001045" 1993 4 .12265304923057556 .
"001045" 1994 3 .09068997949361801 .
"001045" 1995 3 .0766054205596447 .
"001045" 1996 2 .06665367037057876 .
"001045" 1997 2 .052438799291849136 .
"001045" 1998 2 .04114775136113167 .
"001045" 1999 1 .020233381539583206 .
"001045" 2000 1 .03290897756814957 .
"001045" 2001 1 .01542699933052063 .
"001045" 2002 1 -.011327045410871506 .
"001045" 2003 1 -.016535881161689758 .
"001045" 2015 4 .15013308301568032 .
"001045" 2016 4 .12947363704442977 .
"001045" 2017 4 .13262792453169822 .
"001045" 2018 4 .12874503433704376 .
"001045" 2019 2 .014922429993748664 .
"001045" 2020 1 -.10153292678296566 .
"001062" 1980 . . .
"001062" 1981 . . .
"001062" 1982 . . .
"001062" 1983 . . .
"001062" 1984 . . .
"001062" 1985 1 -.11511584520339965 .
"001062" 1986 1 -.11108340919017792 .
"001062" 1987 1 .0042041420936584474 .
"001062" 1988 1 -.06421885490417481 .
"001062" 1989 1 -.03794295936822891 .
"001075" 1999 2 .07644412461668253 .
"001075" 2000 3 .18284919559955598 .
"001075" 2001 3 .21180223673582077 .
"001075" 2002 3 .108187834918499 .
"001075" 2003 3 .10829192399978638 .
"001075" 2004 3 .08662064783275128 .
"001075" 2005 1 -.011845119670033455 .
"001075" 2006 1 -.03082289882004261 .
"001075" 2007 2 .06045297645032406 .
"001075" 2008 1 .037873682007193564 .
"001075" 2009 2 .027906234934926034 .
"001075" 2010 1 .01979141104966402 .
"001075" 2011 1 -.009270480833947659 .
"001075" 2012 1 -.01270738709717989 .
"001075" 2013 2 .005435020290315151 .
"001075" 2014 1 .011733731254935265 .
"001075" 2015 2 .01398144371341914 .
"001075" 2016 2 .015531257388647645 .
"001075" 2017 2 .015610854176338762 .
"001075" 2018 2 .013419242051895708 .
"001075" 2019 1 -.0006451937952078879 .
"001075" 2020 2 .00580698415869847 .
"001078" 1980 . . .
"001078" 1981 . . .
"001078" 1982 . . .
"001078" 1983 . . .
"001078" 1984 . . .
"001078" 1985 3 .10561169162392617 .
"001078" 1986 3 .10237123146653175 .
"001078" 1987 3 .11065723821520805 .
"001078" 1988 3 .11067664846777917 .
"001078" 1989 3 .11658454835414886 .
"001078" 1990 3 .1290273442864418 .
"001078" 1991 3 .12571450173854828 .
"001078" 1992 4 .1236031860113144 .
"001078" 1993 4 .11273484528064728 .
"001078" 1994 4 .11259527355432511 .
"001078" 1995 3 .10233953446149827 .
"001078" 1996 3 .09902683943510056 .
"001078" 1997 2 .08645921647548675 .
"001078" 1998 2 .08230235129594803 .
end

[Sebastian Schirner]

Hey Kate,

you might want to try something along these lines. lgraph is available from SSC.

lgraph mean_SetGrowthRate FiscalYear, statistic(mean) by(quartiles)

Best
Sebastian

[Nick Cox]

Starting with

 
 bys quartiles FiscalYear: egen QuartileMeanGR = mean(mean_SetGrowthRate)

you can go first with something like

egen tag = tag(quartiles FiscalYear) 
separate QuartileMeanGR,  by(quartiles) veryshortlabel 
line QuartileMeanGR? FiscalYear if tag

and then add your other series on top. Direct labelling is a good idea to reduce the real estate given over to a legend.

I don't see that any community-contributed command is either needed or especially helpful here.

[Kate Pryce]

Nick this worked perfectly! Far preferable to deleting data.