graphing lifelines
E. J. Gold is the Hi-Tech Shaman
I'm looking for a tool which will take a dataset of tuples indicating
the year of birth and death of a person:
(1872, 1950, "Sri Aurobindo")
(1821, 1910, "Mary Baker Eddy")
(1831, 1891, "HP. Blavatksy")
And graph them out, in bars, annotating them with the person's name.
A simple spreadsheet would've worked, but they seem to start from
zero. Thus, I would only be able to indicate the span of life (by
subtracting death year from birth year).
Mensanator
<metap...@gmail.com> wrote:
> (crossposted to comp.lang.python)
>
> I'm looking for a tool which will take a dataset of tuples indicating
> the year of birth and death of a person:
>
> (1872, 1950, "Sri Aurobindo")
> (1821, 1910, "Mary Baker Eddy")
> (1831, 1891, "HP. Blavatksy")
>
> And graph them out, in bars, annotating them with the person's name.
>
> A simple spreadsheet would've worked, but they seem to start from
> zero.
A simple spreadsheet WILL work, IF you use the X-Y scatter plot.
> Thus, I would only be able to indicate the span of life (by
> subtracting death year from birth year).
Try this:
1 1 1872 1950 "Sri Aurobindo"
2 2 1821 1910 "Mary Baker Eddy"
3 3 1831 1891 "HP. Blavatksy"
The numbers in the first two columns will be the Y coordinate.
when both are the same, it draws a horizontal line that starts
at the birth date X-value and ends at the death date X-value.
For example the series "Sri Aurobindo" in the Source Data would
look like:
Series
------
"Sri Aurobindo" Name: [=Sheet1!R1C5 ]
X-values: [=Sheet1!R1C3:R1C4]
Y-values: [=Sheet1!R1C1:R1C2]
Make each row a series, use the names as series labels, lose the
grid, thicken the lines, color to taste, add data labels (series
name)
and once they are created, set label alignment to be "above".
Finally, scale your axes accordingly, such as 1800-2000.
You'll see something that looks like
"HP Blavatsky" "HP Blavatsky"
3 ------------------
"Mary Baker Eddy" "Mary Baker Eddy"
2 ------------------------------
"Sri Aurobindo" "Sri Aurobindo"
1 -----------------
1800 2000
Each label appears twice, once at the birth point
and again at the death point.
E. J. Gold is the Hi-Tech Shaman
> On Jul 15, 1:56 pm, "E. J. Gold is the Hi-Tech Shaman"
>
> <metap...@gmail.com> wrote:
> > (crossposted to comp.lang.python)
>
> > I'm looking for a tool which will take a dataset of tuples indicating
> > the year of birth and death of a person:
>
> > (1872, 1950, "Sri Aurobindo")
> > (1821, 1910, "Mary Baker Eddy")
> > (1831, 1891, "HP. Blavatksy")
>
> > And graph them out, in bars, annotating them with the person's name.
>
> > A simple spreadsheet would've worked, but they seem to start from
> > zero.
>
> A simple spreadsheet WILL work,
Ok, here is the simple spreadsheet in Excel CSV format:
1,1,1872,1950,Sri Aurobindo
2,2,1821,1910,Mary Baker Eddy
3,3,1831,1891,HP Blavatsky
>IF you use the X-Y scatter plot.
Ok, let's take this step by step. I select "Chart Wizard" from the
toolbar. Then I choose "XY (Scatter)" from the presented menu.
>
> > Thus, I would only be able to indicate the span of life (by
> > subtracting death year from birth year).
>
> Try this:
>
> 1 1 1872 1950 "Sri Aurobindo"
> 2 2 1821 1910 "Mary Baker Eddy"
> 3 3 1831 1891 "HP. Blavatksy"
1,1,1872,1950,Sri Aurobindo
2,2,1821,1910,Mary Baker Eddy
3,3,1831,1891,HP Blavatsky
>
> The numbers in the first two columns will be the Y coordinate.
2 numbers to indicate a single coordinate? hmm, never encountered that
before... Oh I get it. We have to X coords, so we need 2 y coords..
> when both are the same, it draws a horizontal line that starts
> at the birth date X-value and ends at the death date X-value.
>
> For example the series "Sri Aurobindo" in the Source Data would
> look like:
>
> Series
> ------
> "Sri Aurobindo" Name: [=Sheet1!R1C5 ]
> X-values: [=Sheet1!R1C3:R1C4]
> Y-values: [=Sheet1!R1C1:R1C2]
>
> Make each row a series,
yes, that is the default
> use the names as series labels,
click on the Series tab. for the name option click to the right of the
current data and then highlight E1 to E3 in the graph.
Setting the X-values to A1 through B3 worked fine.
But when I tried to set the Y-values to C1 to D3, I got this error:
"""the reference is not valid. References for titles, values or sizes
must be a single cell, row, or column"""
E. J. Gold is the Hi-Tech Shaman
>
> <metap...@gmail.com> wrote:
> > (crossposted to comp.lang.python)
>
> > I'm looking for a tool which will take a dataset of tuples indicating
> > the year of birth and death of a person:
>
> > (1872, 1950, "Sri Aurobindo")
> > (1821, 1910, "Mary Baker Eddy")
> > (1831, 1891, "HP. Blavatksy")
>
> > And graph them out, in bars, annotating them with the person's name.
>
> > A simple spreadsheet would've worked, but they seem to start from
> > zero.
>
> A simple spreadsheet WILL work, IF you use the X-Y scatter plot.
there are 5 sub-charts for the X-Y scatter plot...
I'm not sure which to choose, but I'm hard at work trying to figure it
out :)
E. J. Gold is the Hi-Tech Shaman
<metap...@gmail.com> wrote:
>
> Setting the X-values to A1 through B3 worked fine.
WRONG CITY - X-values should've been C1 to D3
E. J. Gold is the Hi-Tech Shaman
<metap...@gmail.com> wrote:
> But when I tried to set the Y-values to C1 to D3,
The Y-values should be A1 to B3, but the problem is still the same.
The Y-values cannot span two columns... and you still get the error
below:
Mensanator
Use straight lines without points.
I think I neglected to say you have to select data in
rows, not columns.
Look again at the example series:
> Series
> ------
> "Sri Aurobindo" Name: [=Sheet1!R1C5 ]
> X-values: [=Sheet1!R1C3:R1C4]
> Y-values: [=Sheet1!R1C1:R1C2]
x-values are in row 1, columns 3 & 4.
y-values are in row 1, columns 1 & 2.
E. J. Gold is the Hi-Tech Shaman
>
> Use straight lines without points.
Done. There are 2 sub-graphs which fulfill that criteria - one with
smoothed lines and one without. I chose the one without.
>
> I think I neglected to say you have to select data in
> rows, not columns.
>
No, you didn't neglect that. In your first post, you said: """Make
each row a series..."""
> Look again at the example series:
>
> > Series
> > ------
> > "Sri Aurobindo" Name: [=Sheet1!R1C5 ]
> > X-values: [=Sheet1!R1C3:R1C4]
> > Y-values: [=Sheet1!R1C1:R1C2]
Yes, for a single row, your Y-values are valid and I was able to
produce a chart of that single row with no problem.
I was also able to produce a graphic like you showed in your original
post, but I had manually add each row as a series...
So it worked, but it is far too manual a process. I will probably have
to resort to graphing it myself with a programming language.
Mensanator
<metap...@gmail.com> wrote:
> On Jul 16, 12:44 am, Mensanator <mensana...@aol.com> wrote:
>
>
>
> > Use straight lines without points.
>
> Done. There are 2 sub-graphs which fulfill that criteria - one with
> smoothed lines and one without. I chose the one without.
>
>
>
> > I think I neglected to say you have to select data in
> > rows, not columns.
>
> No, you didn't neglect that. In your first post, you said: """Make
> each row a series..."""
>
> > Look again at the example series:
>
> > > Series
> > > ------
> > > "Sri Aurobindo" Name: [=Sheet1!R1C5 ]
> > > X-values: [=Sheet1!R1C3:R1C4]
> > > Y-values: [=Sheet1!R1C1:R1C2]
>
> Yes, for a single row, your Y-values are valid and I was able to
> produce a chart of that single row with no problem.
>
> I was also able to produce a graphic like you showed in your original
> post, but I had manually add each row as a series...
Well, yeah, I did too. I always create an x-y scatter plot
with a single series and manually add additional ones if needed.
>
> So it worked, but it is far too manual a process. I will probably have
> to resort to graphing it myself with a programming language.
You can probably do that with VBA. Turn on your macro recorder,
go through all the steps of creating an x-y scatter plot with
a single series. Turn off the recorder. Most times, you won't
be able to use the recorded code as is. Typically, where you
see
Range.Select
you'll want to change that to
For each cell in Selection
This is a great way to process arbitrary user selected cells
that can minimize your programming. Keep in that you'll also
need to change things like
Selection.Font.Bold = True
to
cell.Font.Bold = True
What you'll need to focus on is how the macro created the graph
(which you don't have to do) and how the individual series
was added. If that's not seperate from the creation process,
turn the recorder on and log the steps necessary to add an
additional series. You'll be able to tell when you have to
create a new series by tracking cell.Row, when the selection
moves to a new row, start a new series.
For that matter, you don't have to select ALL the cells of the
various series, just the first. And then use
cell.Offset(0,n)
to get the unselected n cells that make up the rest of the series.
Yes, you can access Excel from external programming languages,
but I usually don't bother because I've never run into anything
that couldn't be done in VBA.
Would you like to see an example?