Extracting Numbers from Graphs in PDFs

Year	REER-Unit Labor
1980	310.9
1981	260.3
1982	201.3
...
2004	105.8

In research, it is common to find publications that have graphs of data you are interested in, but no numeric tables of the underlying data. Some analysis requires the numeric form, and getting that data from authors can be a hit-or-miss proposition. This page describes how to extract numeric data from graphs with a high degree of accuracy. I'll start with some background on vector images and selecting objects in Illustrator; you can skim or skip the intro if you already know that stuff.

This exact method will work on most recent pdfs that use vector-based images. If you have old papers (e.g. from the 1970's) that were scanned in and have rasterized graphs, you'll need to do some extra steps to clip out the graph and have Illustrator construct a vector overlay. Vector-based images are a family of file formats that record a series of lines, shapes, etc. in a geometric form (e.g. x1, y1 to x2, y2). Bitmap images (sometimes called rasterized images) are a family that basically draw a grid and record the color of every point on the grid. The PDF file format can store either type of specification. Vector-based images are better for publications because they don't get pixelated when you zoom in or resize them. But until recently, Word couldn't deal with vector-based images outside of excel graphs copied in. Here's a longer explanation you can read if you want more information. Moving on...

When you open a pdf in Illustrator that is vectorized, you can select text or figures in two ways. In the toolbox, you'll see a black arrow in the upper left and a white arrow to its right. The black arrow selects entire groups of objects, and the white arrow selects individual components from a group. These groups are have the same functionality as ones you can make in powerpoint or word. That is, clicking any member of a group selects the entire group, so all the components will get deleted, moved, or resized together. To break apart a group, select it, right click, and choose "Ungroup" from the pop-up menu. Occasionally in Illustrator, you'll also need to choose "Release Compound Path" or "Release Clipping Mask" in order to get at pieces you are interested in.

Using either the black or white arrows in illustrator, you can drag a box to select many objects at one time. If your box contains too many objects, you can hold down select and click on objects to unselect them. Holding down shift while you click also lets you add objects to your selection. It takes a little practice to efficiently select lots of objects, but with the above knowledge and playing around, you can figure it out no problem.

In this example, I'm using Figure 3 at the bottom of page 22 of an IMF report that shows the Real Effective Exchange Rate in Senegal in 1980-2004 calculated using Unit Labor Cost. The X variable is year, and the Y variable is REER-index, which I'll sometimes call REER for short.

The steps:

Use Adobe Illustrator to isolate the graph

You can probably use other software to extract the graph from a pdf file, but I have access to Illustrator and am familiar with it.

• Open the pdf in Illustrator to the page containing the graph (illustrator only opens one page at a time)
• Delete everything but the graph. Save a copy (e.g. REER_UnitLabor.pdf)
• Delete everything but series line. Save a copy in SVG format. (e.g. REER_UnitLabor.svg).
  SVG is a vector-based format written in plain text.

Reformat the numbers into a table

• Open the SVG file in Word or your favorite text editor. You'll see a section with a list of numbers and letters that looks like this:
  

  <path fill="none" stroke="#000000" stroke-width="0.626" stroke-linecap="round" stroke-linejoin="round" stroke-miterlimit="10" d="
       M178.873,69.014l7.74-1.14 M171.073,72.073l7.8-3.06 M163.333,73.754l7.74-1.681 M155.533,69.794l7.8,3.96 M147.793,70.153
       l7.74-0.359 M140.053,68.714l7.74,1.439 M132.313,70.273l7.74-1.56 M124.513,79.334l7.8-9.061 M116.773,79.394l7.74-0.06
        M108.973,80.294l7.8-0.9 M101.233,58.813l7.74,21.48 M93.493,56.594l7.74,2.22 M85.693,46.813l7.8,9.78 M77.953,44.953l7.74,1.86
        M70.153,46.153l7.8-1.2 M62.413,41.413l7.74,4.74 M54.613,39.313l7.8,2.1 M46.873,39.974l7.74-0.66 M39.133,46.754l7.74-6.78
        M31.333,44.233l7.8,2.521 M23.593,39.374l7.74,4.859 M15.853,36.434l7.74,2.94 M8.053,16.993l7.8,19.44 M0.313,0.313l7.74,16.68"/>
  

  There are many ways of drawing that path, and my copy of Illustrator uses the pattern M x,y l delta_x, delta_y, only without a lot of spacing or punctuation. "M" means move the "pencil" to absolute coordinates on the page. "l" (lower case L) means draw a line segment from that point over by delta_x and up by delta_y. In the SVG format, the Y-axis is reversed from cartesian space, so a bigger number means you move down instead of up, a negative delta_y moves up, and a positive delta_y moves down. Illustrator occasionally uses "v" and "h" for vertical and horizontal line segments. Those are infrequent in a graph and can be normalized in a step below. If you have a different copy of Illustrator or use another program to convert the graph to an svg file, you may need to reference the svg path specification, and/or work out another strategy of processing the data after it is in excel.

• Delete everything but the data so it looks like this:
  

       M178.873,69.014l7.74-1.14 M171.073,72.073l7.8-3.06 M163.333,73.754l7.74-1.681 M155.533,69.794l7.8,3.96 M147.793,70.153
       l7.74-0.359 M140.053,68.714l7.74,1.439 M132.313,70.273l7.74-1.56 M124.513,79.334l7.8-9.061 M116.773,79.394l7.74-0.06
        M108.973,80.294l7.8-0.9 M101.233,58.813l7.74,21.48 M93.493,56.594l7.74,2.22 M85.693,46.813l7.8,9.78 M77.953,44.953l7.74,1.86
        M70.153,46.153l7.8-1.2 M62.413,41.413l7.74,4.74 M54.613,39.313l7.8,2.1 M46.873,39.974l7.74-0.66 M39.133,46.754l7.74-6.78
        M31.333,44.233l7.8,2.521 M23.593,39.374l7.74,4.859 M15.853,36.434l7.74,2.94 M8.053,16.993l7.8,19.44 M0.313,0.313l7.74,16.68
  

Now you need to massage these numbers so they can be copied into excel. The replacement strings below are assuming you are using Word. Most text editor's I'm familiar with use "\t" for tabs and "\n" or "\r" for new lines, whereas Word uses "^t" and "^p" for tabs and new lines. If you are in doubt, select a tab or new line from text and paste it into the replace field of your text editor.

• Search and replace "l" with "^tl" to put a tab in front of the lower case L's
• Search and replace "M" with "^pM" to make each M block start on a new line
• Search and replace "-" with "^t-" to put a tab between negative numbers that were shoved up against another number
• Search and replace "," with "^t"
• Clean up messy lines so every line is formatted like this:
  

  M155.533     69.794     l7.8     3.96

  A few lines may be blank or have the "l" section on a different line. Delete the blank lines, as well as the extra returns that push the l onto a different line.

  Occasionally, you'll see a h followed by one number instead of an l followed by two, or maybe a v. An "h" denotes a horizontal line, so only the x value is given. Change the h to an l, and add a zero for the y. e.g. change "h7.8" to "l7.8 0". A "v" denotes a vertical line and should be dealt with similarly. ex. change "v3.4" to "l0 3.4"

• Replace M with a blank to delete them.
• Do the same with l (lower case L)
• You should now have four columns of numbers separated by tabs with one row for every line segment of the graph:
  

  178.873	69.014	7.74	-1.14
  171.073	72.073	7.8	-3.06
  ...
  8.053	16.993	7.8	19.44
  0.313	0.313	7.74	16.68
  

• Copy and paste this data into excel (see REER_UnitLabor.xls)

In Excel, convert the pixel positions to the original data

Each row is a line segment, so if we have N data points, we'll have N-1 rows. We need to extract the points from the line data.

• In Excel, label the columns like so: Line start X, Line start Y, Line delta x, Line delta y
• Sort the data by "Line start X".
• Add columns: "Point X" and "Point Y".
• For rows 1 to N-1, set the value equal to Line Start X and Line Start Y.
• For row N, set the X value to "Line Start X" + "Line Delta X" of row N-1, and do the same for Y.
• Add columns for the X & Y data. In my case, I labeled them "Year" and "REER - Unit Labor", but you could call them "Data X" and "Data Y". The important thing to remember is the units of the numbers in the data columns are different than the units of the numbers in the "Point X" and "Point Y" columns.

In my data, the x-values were supposed to be evenly spaced and each represented a single year, so I could basically ignore the graph's x-values except for diagnostics. If this is the case for you, set those data values manually. If it is not the case, you can adapt the strategy used to get Y-values that is explained below.

Get reference points for two data points

You can populate the "Data Y" (REER) column, from the "graph Y" column with a simple linear equation if you know REER values for two data points. You can get these reference values by either eyeballing the graph, or reading off numbers in Illustrator's info panel and doing some calculations. If you use the eyeball method and are a little off, you can update your guess in the "Check your work" step below.

• Re-open the first copy of the graph you saved in Illustrator to use as a reference (e.g. REER_UnitLabor.pdf)



Eyeball method

• Use the white selection arrow to grab one side of a tick mark on the y-axis, and drag it out to the right. Hold down shift while you are dragging so the line will stay horizontal. (See REER_UnitLabor-2.pdf)

In this example, I got lucky and was able to eyeball near-exact intersections between points in the scatter plot and y-tick marks: 200 for 1982 and 100 for 1997. Normally, I'm not this lucky, and have to read the coordinates of two tick marks and a line segments in Illustrator and use those to calculate the REER values for two reference points.

Exact method

Read the coordinates of an object in illustrator by selecting it with the white selection arrow, then clicking "Info" under the "Window" menu. A small toolbar will pop up that shows X, Y, W, & H.

• X & Y denote the coordinate of the left side of the line
• W & H (Width & Height) denote absolute values of delta x & delta y
• Read the Y-coordinates of two tick marks on the Y-axis.
• Determine the conversion between Y-coordinates in inches according to Illustrator and REER values.

	REER value	Y-coordinate (inches)
Tick A	200	3.2158
Tick B	100	2.7583
Difference	100	0.4575

Conversion_REER,inches = Delta_REER / Delta_inches = 100 / 0.4575
• Read the Y and H (height) values of the first line segment on the graph.
  Clicking on the first line segment of the graph that runs between 1980 and 1981, I get Y=3.7233 inches and H=0.2317 inches.
• Calculate the REER values for the first reference point (1980) with the formula:
  SampleY_{1980, REER} = (SampleY_1980,inch - REF_TICK_inch) * DELTA_TICK_REER / DELTA_TICK_inch + REF_TICK_REER
  
  If I use the 200 tick mark as a reference, this comes to (3.7233 - 3.2158 ) * 100 / 0.4575 + 200 = 310.9290, so 1980 had a REER index of 310.9290.
• Calculate the REER value for the second reference point (1981).
  I can get the difference between 1980 and 1981 using the H (height) value with the formula:
  Height * DELTA_TICK_REER / DELTA_TICK_inch = 0.2317 * 100 / 0.4575 = 50.6448
  
  The H value for that line segment will always be positive, but looking at the line segment, I can see that the REER value in 1981 was less than that in 1980, so to get the 1981 value, I subtract: 310.9290 - 50.6448 = 260.2842.

Now I have two reference points: (1980, 310.9290) and (1981, 260.2842).

Calculate the values for the Y Data column

Whether you calculate the reference points numerically, or eyeball them, you end up with two points from which you calculate the slope, and apply a linear formula to get the data Y. The linear formula follows a similar strategy described in the paragraph above. You calculate the conversion between pixels in the SVG file to data units (REER index in this example), and use that and one of your reference points for constants in the formula:

• Enter the reference points into a table like the one below. Also, copy the pixel value from the "Point Y" column of your main table.
Reference points and conversion factor

Year	REER value	Pixel value (Point Y)
1980	310.929	0.313
1981	260.2842	16.993
Difference	50.6448	-16.68

Conversion = Delta_REER / Delta_Pixels = 50.6448 / -16.68 = -2.95517007

• Enter the formula given above into the Y data column: "REER-Unit Labor". The formula will work with either reference point.
  Many people type will type the numbers into the formulas for the constant values. I advise against that. Instead, refer to the cells that hold the relevant numbers, then type a dollar sign in front of the letter and number of the constants (e.g. D31 would become $D$31). Do not type the dollar sign in front of the "Point Y" cell. The big benefit of following this this advise is that you can update the numbers in the "Reference points and conversion factor" table, and your calculations will spontaneously update. This is very useful if you eyeballed the reference points and didn't get them quite right.

Now, your main table should look something like this:

Check your work

The final test is to overlay a graph drawn from the extracted numbers on top of the original graph.

• Make a scatter plot of the data in excel. In the formatting palette under "Chart Colors, Lines, and Fills", under "Fill options" set the color to no fill, or set it to complete transparency. Set the limits of the X & Y axes to match those of the original graph.
• Save the original graph from illustrator as a png file. Or, if your version of Illustrator has "Save for Microsoft Office..." use that option instead.
• Insert the original graph as a picture in excel. Enlargen it as needed. Arrange it on top of the new graph, then right click and choose "Arrange" -> "Send to back"
• Resize the new graph and move it around until it matches up with the original graph underneath. If it is not an exact alignment, consider adjusting your estimates of the data Y-values of your reference points until the alignment improves. Alternately, double-check that you read off the reference points properly.
  While constructing this example, a misalignment made me realize I had misread the graph and used 1981 as the year for the first reference point instead of 1982, and had consequently taken the wrong "Point Y" value from the list.
  

List of files referenced in this example

• Real Effective Exchange Rate.pdf: The orginal paper I took the graph from.
• REER_UnitLabor.pdf: The graph isolated from page 22, after I deleted everything else on the page.
• REER_UnitLabor.svg: The series line from the graph, exported as an SVG file, after I deleted the axes and the legends and everything else.
• REER_UnitLabor.xls: A spreadsheet containing: the numbers of the graph coordinates that I took out of the SVG file, intermediate values used in calculations, the reference points, the final numeric data, and the reality check of the overlaid graphs.
• REER_UnitLabor-2.pdf: The original graph, after I stretched out some Y and X tick marks to facilitate the eyeball method.
• REER_UnitLabor.png: The orginal graph, saved for excel, and used to check the results with the the overlay method.