Twoway line graphs where each subject ID has a separate, individual line and line colours are specified by another var group

[Jack Treliving]

Hi there, I am trying to plot a twoway line graph where each subject has a separate line connect 2 points (the age at each timepoint, 1 and 3) and the line colour is given by another variable, group.

At the moment, I am getting connections between the lines of different subject IDs (sjid).

Here is an example dataset and code:

clear
input str11 sjid group timepoint age analyte
000-000-001 0 1 44.32 30.22
000-000-001 0 3 46.43 40.54
000-000-002 3 1 78.54 500.32
000-000-002 3 3 80.32 700.45
000-000-003 2 1 50.03 435.02
000-000-003 2 3 51.99 567.34
000-000-004 3 1 90.34 1002.23
000-000-004 3 3 92.03 1345.43
000-000-005 1 1 35.21 25.43
000-000-005 1 3 37.45 40.12
000-000-006 2 1 55.10 320.76
000-000-006 2 3 57.62 410.88
000-000-007 0 1 40.76 28.90
000-000-007 0 3 42.89 35.21
000-000-008 3 1 85.23 920.11
000-000-008 3 3 87.55 1150.33
000-000-009 1 1 33.14 20.78
000-000-009 1 3 35.09 32.45
000-000-010 2 1 52.87 500.23
000-000-010 2 3 54.99 640.12
000-000-011 3 1 88.45 980.54
000-000-011 3 3 90.99 1260.76
000-000-012 0 1 45.32 33.65
000-000-012 0 3 47.44 43.12
000-000-013 1 1 38.21 27.54
000-000-013 1 3 40.15 37.23
000-000-014 2 1 59.67 560.45
000-000-014 2 3 61.32 710.87
000-000-015 3 1 92.01 1300.45
000-000-015 3 3 94.78 1450.22
000-000-016 0 1 42.99 29.87
000-000-016 0 3 45.33 39.12
000-000-017 1 1 36.12 24.55
000-000-017 1 3 38.99 34.90
000-000-018 2 1 49.32 400.89
000-000-018 2 3 51.78 530.23
000-000-019 3 1 91.21 1100.44
000-000-019 3 3 93.99 1356.21
000-000-020 0 1 41.12 31.45
000-000-020 0 3 43.76 38.12
000-000-021 1 1 37.22 26.78
000-000-021 1 3 39.87 36.32
000-000-022 2 1 60.45 575.34
000-000-022 2 3 62.87 725.44
000-000-023 3 1 95.23 1400.98
000-000-023 3 3 97.12 1600.45
000-000-024 0 1 43.54 32.10
000-000-024 0 3 46.22 41.78
000-000-025 1 1 34.89 22.33
000-000-025 1 3 37.23 30.99
000-000-026 2 1 58.21 540.32
000-000-026 2 3 60.67 680.76
000-000-027 3 1 93.65 1250.54
000-000-027 3 3 95.98 1500.88
000-000-028 0 1 44.76 35.12
000-000-028 0 3 47.33 45.98
000-000-029 1 1 32.99 19.87
000-000-029 1 3 35.55 28.65
000-000-030 2 1 53.87 480.44
000-000-030 2 3 56.12 620.54
end

local cat0 = "154 149 148"
local cat1 = "242 175 175"
local cat2 = "245 111 98"
local cat3 = "245 26 0"
local cat4 = "180 19 0"
local cat5 = "130 10 0"

twoway (line analyte age if group==0, lcolor("`cat0'") lwidth(med) c(L) sort(sjid)) ///
(line analyte age if group==1, lcolor("`cat1'") lwidth(med) c(L) sort(sjid)) ///
(line analyte age if group==2, lcolor("`cat2'") lwidth(med) c(L) sort(sjid)) ///
(line analyte age if group==3, lcolor("`cat3'") lwidth(med) c(L) sort(sjid)) ///
(line analyte age if group==4, lcolor("`cat4'") lwidth(med) c(L) sort(sjid)), ///
aspectratio(1) legend(off)

I have also tried with the spagplot command, but I was not sure how to colour by group and the lines still remained connected across different sjid. Thank you!

[Nick Cox]

This may help:

local cat0 = "154 149 148"
local cat1 = "242 175 175"
local cat2 = "245 111 98"
local cat3 = "245 26 0"
local cat4 = "180 19 0"
local cat5 = "130 10 0"

separate analyte, by(group) veryshortlabel

line analyte? age, lcolor("`cat0'" "`cat1'" "`cat2'" "`cat3'" "`cat4'" "`cat5'") lwidth(med ..) c(L ..) aspect(1)

[Jack Treliving]

Thanks so much for the suggestion, however the lines still connect multiple sjid. I am trying to plot something that looks similar to like this, with each sjid represented by a single line, a straight line spanning age at timepoint 1 to age at timepoint 3:

Many thanks,
Jack

[Jack Treliving]

Separating analyte by(sjid) instead of by(group) works for plotting separate lines but no longer works for the colours.

Alternatively, is there a way of looping over each sjid and adding to an existing plot?

Many thanks!

[Nick Cox]

I think this is progress. But getting a legend for the first graph would be harder work, and wouldn't I think work well, as some of your colours are too close together.

clear
input str11 sjid group timepoint age analyte
000-000-001 0 1 44.32 30.22
000-000-001 0 3 46.43 40.54
000-000-002 3 1 78.54 500.32
000-000-002 3 3 80.32 700.45
000-000-003 2 1 50.03 435.02
000-000-003 2 3 51.99 567.34
000-000-004 3 1 90.34 1002.23
000-000-004 3 3 92.03 1345.43
000-000-005 1 1 35.21 25.43
000-000-005 1 3 37.45 40.12
000-000-006 2 1 55.10 320.76
000-000-006 2 3 57.62 410.88
000-000-007 0 1 40.76 28.90
000-000-007 0 3 42.89 35.21
000-000-008 3 1 85.23 920.11
000-000-008 3 3 87.55 1150.33
000-000-009 1 1 33.14 20.78
000-000-009 1 3 35.09 32.45
000-000-010 2 1 52.87 500.23
000-000-010 2 3 54.99 640.12
000-000-011 3 1 88.45 980.54
000-000-011 3 3 90.99 1260.76
000-000-012 0 1 45.32 33.65
000-000-012 0 3 47.44 43.12
000-000-013 1 1 38.21 27.54
000-000-013 1 3 40.15 37.23
000-000-014 2 1 59.67 560.45
000-000-014 2 3 61.32 710.87
000-000-015 3 1 92.01 1300.45
000-000-015 3 3 94.78 1450.22
000-000-016 0 1 42.99 29.87
000-000-016 0 3 45.33 39.12
000-000-017 1 1 36.12 24.55
000-000-017 1 3 38.99 34.90
000-000-018 2 1 49.32 400.89
000-000-018 2 3 51.78 530.23
000-000-019 3 1 91.21 1100.44
000-000-019 3 3 93.99 1356.21
000-000-020 0 1 41.12 31.45
000-000-020 0 3 43.76 38.12
000-000-021 1 1 37.22 26.78
000-000-021 1 3 39.87 36.32
000-000-022 2 1 60.45 575.34
000-000-022 2 3 62.87 725.44
000-000-023 3 1 95.23 1400.98
000-000-023 3 3 97.12 1600.45
000-000-024 0 1 43.54 32.10
000-000-024 0 3 46.22 41.78
000-000-025 1 1 34.89 22.33
000-000-025 1 3 37.23 30.99
000-000-026 2 1 58.21 540.32
000-000-026 2 3 60.67 680.76
000-000-027 3 1 93.65 1250.54
000-000-027 3 3 95.98 1500.88
000-000-028 0 1 44.76 35.12
000-000-028 0 3 47.33 45.98
000-000-029 1 1 32.99 19.87
000-000-029 1 3 35.55 28.65
000-000-030 2 1 53.87 480.44
000-000-030 2 3 56.12 620.54
end

local cat0 = "154 149 148"
local cat1 = "242 175 175"
local cat2 = "245 111 98"
local cat3 = "245 26 0"
local cat4 = "180 19 0"
local cat5 = "130 10 0"

egen id = group(sjid), label 

su id, meanonly 

forval i = 1/`r(max)' { 
    su group if id == `i', meanonly 
    local call `call' line analyte age if id == `i', lcol("`cat`r(min)''") || 
}

twoway `call',  legend(off) name(G1, replace)

twoway `call',  by(group, legend(off) note("")) name(G2, replace)

[Nick Cox]

Consider also

linkplot analyte age, link(sjid) by(group) recast(line)

linkplot is from SSC. See e.g. https://www.statalist.org/forums/for...using-linkplot for a fairly detailed discussion.

Finally, perhaps, and belatedly, this is a lot easier with a reshape.

Here is the whole kit and caboodle from me, with two extra graphs.

 
clear
input str11 sjid group timepoint age analyte
000-000-001 0 1 44.32 30.22
000-000-001 0 3 46.43 40.54
000-000-002 3 1 78.54 500.32
000-000-002 3 3 80.32 700.45
000-000-003 2 1 50.03 435.02
000-000-003 2 3 51.99 567.34
000-000-004 3 1 90.34 1002.23
000-000-004 3 3 92.03 1345.43
000-000-005 1 1 35.21 25.43
000-000-005 1 3 37.45 40.12
000-000-006 2 1 55.10 320.76
000-000-006 2 3 57.62 410.88
000-000-007 0 1 40.76 28.90
000-000-007 0 3 42.89 35.21
000-000-008 3 1 85.23 920.11
000-000-008 3 3 87.55 1150.33
000-000-009 1 1 33.14 20.78
000-000-009 1 3 35.09 32.45
000-000-010 2 1 52.87 500.23
000-000-010 2 3 54.99 640.12
000-000-011 3 1 88.45 980.54
000-000-011 3 3 90.99 1260.76
000-000-012 0 1 45.32 33.65
000-000-012 0 3 47.44 43.12
000-000-013 1 1 38.21 27.54
000-000-013 1 3 40.15 37.23
000-000-014 2 1 59.67 560.45
000-000-014 2 3 61.32 710.87
000-000-015 3 1 92.01 1300.45
000-000-015 3 3 94.78 1450.22
000-000-016 0 1 42.99 29.87
000-000-016 0 3 45.33 39.12
000-000-017 1 1 36.12 24.55
000-000-017 1 3 38.99 34.90
000-000-018 2 1 49.32 400.89
000-000-018 2 3 51.78 530.23
000-000-019 3 1 91.21 1100.44
000-000-019 3 3 93.99 1356.21
000-000-020 0 1 41.12 31.45
000-000-020 0 3 43.76 38.12
000-000-021 1 1 37.22 26.78
000-000-021 1 3 39.87 36.32
000-000-022 2 1 60.45 575.34
000-000-022 2 3 62.87 725.44
000-000-023 3 1 95.23 1400.98
000-000-023 3 3 97.12 1600.45
000-000-024 0 1 43.54 32.10
000-000-024 0 3 46.22 41.78
000-000-025 1 1 34.89 22.33
000-000-025 1 3 37.23 30.99
000-000-026 2 1 58.21 540.32
000-000-026 2 3 60.67 680.76
000-000-027 3 1 93.65 1250.54
000-000-027 3 3 95.98 1500.88
000-000-028 0 1 44.76 35.12
000-000-028 0 3 47.33 45.98
000-000-029 1 1 32.99 19.87
000-000-029 1 3 35.55 28.65
000-000-030 2 1 53.87 480.44
000-000-030 2 3 56.12 620.54
end

local cat0 = "154 149 148"
local cat1 = "242 175 175"
local cat2 = "245 111 98"
local cat3 = "245 26 0"
local cat4 = "180 19 0"
local cat5 = "130 10 0"

egen id = group(sjid), label 

su id, meanonly 

forval i = 1/`r(max)' { 
    su group if id == `i', meanonly 
    local call `call' line analyte age if id == `i', lcol("`cat`r(min)''") || 
}

twoway `call',  legend(off) name(G1, replace)

twoway `call',  by(group, legend(off) note("")) name(G2, replace)

linkplot analyte age, link(id) by(group, note("")) recast(line) name(G3, replace)

reshape wide age analyte, i(id group) j(timepoint)

local count = 1 
forval g = 0/5 { 
    local stuff `stuff' `count' "`g'"
    local Call `Call' || pcspike analyte3 age3 analyte1 age1 if group == `g', lcol("`cat`g''")
    local ++count
}

twoway `Call'  legend(order(`stuff')) name(G4, replace) ytitle(Analyte) xtitle(Age)

This is from linkplot. There is scope to call it up with multiple y variables too.

This is after a reshape. Turn and turn about, separate panels for each group are also possible.

[Jack Treliving]

Thank you so much Nick, that's exactly what I was after! Really appreciate your help on this!

[Nazzarena]

Hello I have a similar but different issue for which spagplot and linkplot as well as xtline wont work, and i have not been able to use twoway. I have a panel of ~126 people over 2 group with unbalanced repeated observations. I want a spaghetti plot (fit lines or actual data one per ID) colored by group (88 people are in one group and 38 in another). I cannot successfully loop over ID to make a local macro of graphs to twoway. Here is a fictitious example:

* Example data
clear
set obs 20
gen id = ceil(_n/5)
gen time = mod(_n,5) + 1
gen outcome = runiform()
gen group = (id <= 2) + 1
sort id time

* Get all ids
levelsof id, local(ids)

* Initialize local macro
local plotcmd ""

* Loop and append lines to local macro
foreach i of local ids {
quietly summarize group if id==`i', meanonly
local thiscolor = cond(r(mean)==1, "red", "blue")

* Append to local macro
local plotcmd "`plotcmd'" + ///
" line outcome time if id==`i', lcolor(`thiscolor') lw(vthin) ||"
}

* Remove trailing double bars
local plotcmd : subinstr local plotcmd " ||" "", all

* Check the macro contents (for debugging: it's empty)
display "`plotcmd'"

* Plot all lines overlaid in one graph (it's empty)
twoway `plotcmd'

Advice? thanks!!!

[Hemanshu Kumar]

Perhaps something like this?

* Example data
clear
set obs 20
gen id = ceil(_n/5)
gen time = mod(_n,5) + 1
gen outcome = runiform()
gen group = (id <= 2) + 1
sort id time

* Get all ids
levelsof id, local(ids)


* Loop and append lines to local macro
foreach i of local ids {

    quietly summarize group if id == `i', meanonly
    local thiscolor = cond( r(mean) == 1 , "red", "blue")
    
    if `"`plotcmd'"' != `""' local plotcmd `"`plotcmd' || "'

    * Append to local macro
    local plotcmd = `" `plotcmd' "' + ///
        `" line outcome time if id == `i', lcolor(`thiscolor') lw(vthin) "'
}

levelsof group, local(groups)
foreach g of local groups {
    quietly summarize id if group == `g', meanonly
    local keys `keys' `=r(min)'
    local labels `labels' label(`=r(min)' "group `g'")
}

local legend_opt legend(order(`keys') `labels')

local plotcmd `" `plotcmd' , `legend_opt' "'

* Check the macro contents
display `"`plotcmd'"'

* Plot all lines overlaid in one graph
twoway `plotcmd'

which results in