DadOverflow.com

Musings of a dad with too much time on his hands and not enough to do. Wait. Reverse that.

Tag: genealogy (Page 4 of 6)

Roots Magic and Jupyter Notebook: like peas and carrots

July 5, 2018 /


via GIPHY

Roots Magic seems to be a popular tool among genealogists. At least, the Genealogy Guys certainly recommend it.

I’ve been using the tool for about a year now. My previous genealogy database tool seemed to lock away my data in its own proprietary database confining me to the queries and views exposed only in its user interface. Before I switched away, though, I wanted to make sure my next tool would give me a little more latitude with regard to accessing my data. At a genealogy conference in 2017, I actually had a short conversation with the head honcho himself, Bruce Buzbee, and voiced this concern. Bruce briefly mentioned Roots Magic’s connection with sqlite. Interest piqued, I bought his software and made the switch.

It seems like most of the work exploring the sqlite foundation of Roots Magic has been captured at the site SQLiteToolsForRootsMagic. [Side note: Wikispaces, the platform on which SQLiteToolsForRootsMagic is built is going away, so, by the time you read this, the SQLiteTools site might be no more. Fortunately, the site owners are developing migration plans, so stay tuned.] These folks tend to use clients like SQLiteSpy to run their queries. Nothing wrong with that, but since my favorite development canvas lately is Jupyter Notebook, I asked myself, “Self, could you query a Roots Magic database in Jupyter Notebook?” The answer is: absolutely!  Here are some of the steps I took to query a Roots Magic database in Jupyter Notebook.

Step 1: Import the necessary packages

Load all my go-to packages including pandas and matplotlib as well as sqlite3 and Pivotal’s SQL Magic (SQL Magic isn’t necessary, but it makes writing SQL queries a little nicer):



1
2
3
4
5
6
7
8
9
10
import sqlite3

import pandas as pd

import numpy as np

import matplotlib

import matplotlib.pyplot as plt

import matplotlib.style as style



%load_ext sql_magic

%matplotlib inline

plt.style.use('fivethirtyeight')

Step 2: Connect to my Roots Magic file

For demonstration purposes, I grabbed a copy of George Washington’s family tree and saved it off in Roots Magic.  Connecting to the file is pretty darn easy:



1
2
3
4
# Note that sqlite seems to require a full path to the file you wish to load

conn = sqlite3.connect("C:\\data_files\\qsync_laptop\\jupyter_notebooks\\query_gen_dbs\\GeorgeWashingtonFamilyBig.rmgc")

%config SQL.conn_name='conn'  # useful when using sql_magic

cur = conn.cursor()

Step 3: Go to town!

At this point, the only real challenge is dealing with the COLLATE NOCASE issue, but that’s just a minor inconvenience.  After that, it’s just spending time understanding the Roots Magic database schema and the relationships between tables.  SQLiteToolsForRootsMagic has really blazed the trail here, so I encourage you to spend some time on the site looking over the hundreds of posted queries to get a better understanding of the database schema.

With direct access to the database, you can print out facts about your data that may not be exposed in the Roots Magic user interface:



1
2
3
4
5
6
7
8
9
10
11
12
cur.execute("SELECT OwnerID FROM NameTable")

nbr_of_people = len(cur.fetchall())

cur.execute("SELECT FamilyID FROM FamilyTable")

nbr_of_families = len(cur.fetchall())

cur.execute("SELECT FamilyID FROM FamilyTable")

nbr_of_families = len(cur.fetchall())

cur.execute("SELECT FamilyID FROM EventTable")

nbr_of_events = len(cur.fetchall())



print('This database contains {0} individuals.'.format(nbr_of_people))

print('It includes {0} families.'.format(nbr_of_families))

print('It includes {0} events.'.format(nbr_of_events))

Output:

This database contains 529 individuals.
It includes 114 families.
It includes 2679 events.

Or this:



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
%%read_sql df_ages -d

SELECT OwnerID

    ,Surname COLLATE NOCASE AS Surname

    ,Given COLLATE NOCASE AS Given

    ,BirthYear AS BirthYear

    ,DeathYear AS DeathYear

    ,(DeathYear - BirthYear) AS age

FROM NameTable n

WHERE COALESCE(BirthYear, 0) > 0 AND COALESCE(DeathYear, 0) > 0

    AND (age BETWEEN 0 AND 110) --remove anyone over 110 years of age or under 0 as that's a likely error



oldest = df_ages.sort_values('age', ascending=False).head(1)

youngest = df_ages.sort_values('age').head(1)

print('This family tree contains {0} individuals with recorded birth and death dates.'.format(df_ages.shape[0]))

print('The oldest person in the tree is {0} {1} at age {2}.'.format(oldest.Given.values[0], oldest.Surname.values[0], 

                                                                   oldest.age.values[0]))

print('The youngest person in the tree is {0} {1} at age {2}.'.format(youngest.Given.values[0], youngest.Surname.values[0], 

                                                                      youngest.age.values[0]))

print('The average age for family members in this tree is {0:.1f} years.'.format(df_ages.age.mean()))

print('The median age for family members in this tree is {0:.1f} years.'.format(df_ages.age.median()))

Output:

This family tree contains 192 individuals with recorded birth and death dates.
The oldest person in the tree is John WASHINGTON at age 99.
The youngest person in the tree is Mildred WASHINGTON at age 1.
The average age for family members in this tree is 52.9 years.
The median age for family members in this tree is 52.0 years.

How about some charts?

You can find this work and more on my Github page.

Why do genealogy?

June 18, 2018 /

Dick Eastman recently posted a thought-provoking piece on why people devote large portions of their lives to recording their family histories. Eastman correctly asserted that genealogy demands time and attention to detail. It can be expensive and require sacrifice to ensure you’re as accurate as possible. Meanwhile, the rest of our families sit bewildered as to why we would find seemingly mundane ancestors and events so fascinating.

In his post, Eastman postulated on the different motivations of amateur genealogists and identified a dichotomy in the endeavor between family historians and simple name gatherers. Name gatherers fill out pedigree charts with names and dates and little more. Family historians go much deeper, looking for the back stories and nuances that give our families character. The metaphor I’ve always used is one of human anatomy: your names and dates compose the skeleton of your family tree. But skeletons aren’t that interesting to look at. The pictures, biographical information, and stories add flesh to that skeleton and turn it into something truly attractive.

I have no issues with the name gatherer: at least, he’s taking the time to document that which might otherwise be lost. I started as a name gatherer, though I’ve since fallen into the deep end of amateur genealogy. Have you taken the genealogical red pill yet? If not, here are a few reasons why you should consider the hobby:

If not you, who?

I’m quite certain you and your family, living and dead, have achieved noteworthy accomplishments. These accomplishments may have been recorded in random newspapers, trophy cases, or elsewhere but they’ll never be collected in one compendium unless someone takes the time to do so. Who’s going to do that? It’s time to get off the bench and get into the game!

The ol’ Kid’s Family Report

If you have children, it’s almost a certainty that one day they’ll approach you with a school assignment to write about their family history. Imagine the hero you’ll be if you can, within minutes, generate a report for your child detailing the last five or six generations of your family.

Know your medical history

Is there a history of some particular illness in your family? That knowledge might help you adjust habits in your life to avoid participating in that history. You might help your children in that regard, as well. Your family’s medical history is a detail you can uncover as a family historian. Related to my earlier point, my daughter took an anatomy class this year in which one of her assignments was to produce a report of her family’s medical history. Because I had been documenting this information for years, I was able to quickly generate a report for her going back four or five generations of the different medical conditions endured by our ancestors.

Help you better understand your own strengths and weaknesses

Were your ancestors inventive and entrepreneurial? Were they consistent and hard working? Did they have a tendency to take risks or did they live more on the safe side? Understanding the general dispositions of your ancestors might help you feel more confident to start that new business or take on a rather risky task–or maybe just play it safe.

Write the history

If you write your family’s history, you control the narrative. If you have particular heroes in your family, you can choose to emphasize their achievements. You have the power to influence future generations. As Uncle Ben once said, “with great power comes great responsibility.” Obviously, honesty and fairness are critical to incorporate in your narrative. This is your opportunity to paint an appropriate picture of your family for future generations.

Meet heretofore unknown family members

Documenting your family tree will reveal family members you never knew you had–perhaps even in your own town. These people are sure to provide a perspective on your ancestors you may be unaware of.

Connect with a community of creative and hard-working genealogists

My fellow amateur genealogists always seem to surprise me with the creative and tech-savvy ways they solve their genealogical mysteries. Here I am–a professional technologist–and some retired grandmother is showing me a Google search technique I’ve never seen before or demonstrating a use of certain genealogical software I didn’t know existed. Genealogy really does require tenacity, energy, and an attention to detail that is commonplace in this community. It can be refreshing to brush elbows with such people.

Inject your own creativity into your work

Every family historian must be a name gatherer first. The family historian, though, goes well beyond name gathering to truly “flesh out” his tree. Here, I think, is a great opportunity for fun and creativity. For example, I video tape virtually every family event I attend, especially reunions, and will occasionally edit reunion video together for fun family artifacts. I’ve interviewed several family members and both recorded and transcribed those interviews. I try to record interesting details like each member’s job histories and even their childhood heroes. Here is a chance to be creative and really represent your relatives in interesting and unconventional ways.

Channel your inner data nerd

This one may not be for everyone, but if you have data nerdist tendencies, what better data to explore than that of your own family? Analyze family migrations or life expectancy or occupation choices. Your family data is probably completely untapped and filled with interesting trends and outliers.

Time is fleeting

I was lucky enough to catch the genealogy bug while still in college. For the next several years, I would spend one week every summer at my grandfather’s farm, computer in front of me, Grandpa to one side, keying in hundreds of handwritten pedigree charts he had created and maintained for the last several decades. I was truly a name gatherer at that point. My grandfather’s efforts saved me countless hours of research. He helped me answer questions that might have forever gone unanswered. Because I started as early as I did, I was able to interview three of my grandparents and several great aunts and uncles and collect information that may have been lost forever. At the very least, for time sake, start your name gathering today!

If you decide you want to get started, make sure to check out Dick Eastman’s excellent “getting started” guide.

Wrapping up the school year

June 6, 2018 /

In the US, this is the time when schools let out for the summer–well, actually, that time began a week or two ago, but just humor me here.  Clearly, this is an important transition time from school year to summer break.  The rigor of the school schedule is gone, the “free” yellow bus transportation services are no more, late night homework and study sessions take a hiatus.  We’re now looking at the blank canvas of Summer.  Of course, as parents, our schedules don’t change and, in many ways, become harder as we struggle to find summer activities for the kids and ways to transport them there.

Nevertheless, as our children wrap up their school years, here are some actions I try to take to make the transition into Summer a little easier and more organized:

Scan, Scan, Scan

Hopefully, you’re scanning the hard copy documents, awards, and pictures your children bring home with them all throughout the year (and otherwise retaining electronic versions of report cards, etc).  Scanning, say, an award certificate as soon as your child receives it can be helpful for at least two reasons: 1) your scanner will affix the current date to your file helping you know, years from now, when your child earned that award (particularly if the award date isn’t on the certificate) and 2) the affixed date will help you organize such files so that it’s easier to include them with other, related family media.

Regardless, your children will still bring home large amounts of documents in the days before school ends.  I try to go through all these documents and scan as many as makes sense.  School photos?  Check.  Awards and certificates?  Absolutely.  Report cards, standardized test results, and the like?  Definitely.  Writing assignments, poems, and so forth?  Yep.  Going through the effort of scanning these items now can be time consuming, but is well worth it years later when the hard copies are long gone and all you have are those electronic memories.

Take Pictures

I certainly take pictures of my children at their various school events throughout the year and especially at their various end-of-year ceremonies.  I also try to take a few pictures of them in their school uniforms and at the bus stop awaiting its arrival.  I try to include their fellow travelers, as well, as some of those students may be graduating and moving on.  One could even go so far as to print up a sign saying something like, “last day for 4th grade!” and have his child hold the sign in a picture–I wouldn’t recommend that for your older children, though.

Consider filing your child’s notes and related documents

My oldest is a fantastic note taker.  On more than one occasion, we’ve been able to pull her notes out of storage and use them to help the younger ones get through a class or two the older one had already traversed.  Here’s another true story: for my first semester of college, I studied abroad in Singapore.  When I returned to campus in January, I learned that students could take placement tests to test out of certain required classes.  So, I went home and collected the two or three three-ring binders I kept of my Calculus notes from my senior year in high school.  I studied these for several days, took the placement test, and was able to test out of three semesters of math classes.  Cheap credit hours there!  Thus, retaining and organizing your child’s class notes and associated documentation could be a worthwhile endeavor.

Say, “Thanks”

“Thank yous” are free–except for the outrageously priced thank-you cards.  At any rate, your children should thank their teachers, school staff, and even the bus driver.  A card and gift might even be appropriate.  A “thank you” now might be worth a recommendation letter later.

Postmortem?

Often at work, after a project, the team will gather to do a “postmortem” or collect “lessons learned”–simply a list of things that went well, things that didn’t, and ways future projects can learn from the one just finishing.  You could consider helping your child do the same.  What did he like about the year?  What did he hate?  What teachers and classes did he enjoy and why?  Which ones did he not enjoy?  What tools did he use to study for tests and organize his assignments?  Did these work out for him or should he spend some time finding replacements?  These moments of introspection might help your child become more efficient next year.  They could help him decide on career and major as college draws near.  They could help you better coach any younger children you have following in this child’s footsteps.  You might even share your notes with parents of other, younger children entering the grade your child just completed.

Plan out the summer

What, you thought you had a break for a couple of months?  To me, summer is a great time to get a jump on the competition, er, fellow students.  I don’t know if it’s good or bad, but schools seem much more academically–and, certainly, athletically–competitive than they did when I was young.  So, while the competition is lounging by the pool, consider enrolling your children in summertime academic courses that can help them get ahead.  In summers past, we’ve enrolled our kids in Mathnasium and different sessions at the local college and community center.  Ask yourself, what are some of your child’s academic goals?

  • Competing to get into a higher math class?
  • Preparing for an upcoming standardized test?
  • Simply easing some of the anticipated pressures of the next school year?

Summertime academic courses can help meet these objectives and more.

Also new these days is the dreaded Summer homework.  Establishing a plan for your child to knock these requirements out early seems a wise thing to do.  Hopefully, you also have some idea of projects to expect in the next school year.  For example, my child will have to construct a science fair project in his next year.  I hope to spend some of this Summer at least brainstorming with him what we might try to tackle and maybe even begin gathering relevant material.  Whatever your plans are, develop and implement them early before your child gets too used to a life of leisure.

Get a teacher recommendation

If your child is already a junior or senior in high school, he either already has one or more recommendation letters or is actively working on getting one for his college applications, assuming he’s going that route.  But what if your child simply needs a recommendation for a job or maybe an internship?  Before the school doors close and the teachers disappear into the hinterlands, get a recommendation letter if you think you might need one.

Friend contact information

When you see your friends everyday at school, you may not think about how to contact them outside of the establishment.  But if your child wishes to hang out with his school chums over the break, he’ll want to make sure to get phone numbers and email addresses of them before the building shutters.

Read a good book

During the summer, kids usually can’t make the claim that they have no time to read.  Challenge your child to read a good book or two: how about one from this list?

Talk to other parents

Before the year ends, I try to chat up a parent or two of students one grade higher than mine.  What sorts of challenges should my kid be aware of next year?  What teachers and classes should we be wary of?  And so on.  Gather some intelligence on the next grade and prepare for those challenges over the break so you’re ready for them.

Sell your books?

Academic books can be outrageously expensive.  If you really think you won’t need them for reference or for a younger sibling, consider selling those books to another student and try to recoup some of your expense.

 

Any other tips I missed?  If so, please leave them in the comments!

« Older posts Newer posts »

© 2024 DadOverflow.com

Theme by Anders Noren — Up ↑