Menu
I am trying to scrape PDF tables which span across multiple pages. I tried many things but the best seems to be pdftotext -layout as advised here. The problem is that the resultant text file is not easy to work with, as the table layout differs across pages, so the columns are not aligned. Also note missing values in lines beginning with "Solsonès":
TEMPERATURA MITJANA MENSUAL ( ºC ) - 2012
COMARCA CODI i NOM EMA GEN FEB MAR ABR MAI JUN JUL AGO SET OCT N
Alt Camp VY Nulles 7,5 5,5 10,9 12,3 16,7 21,6 22,3 24,4 20,1 15,9
Alt Camp DQ Vila-rodona 7,9 5,6 11,0 12,0 16,6 21,6 22,0 24,3 19,9 15,8
Alt Empordà U1 Cabanes 8,2 6,5 11,7 12,6 17,5 22,0 23,1 24,4 20,4 16,6
Alt Empordà W1 Castelló d'Empúries 8,1 6,4 11,6 12,9 17,0 21,1 22,0 23,4 20,1 16,4
[...]
TEMPERATURA MITJANA MENSUAL ( ºC ) - 2012
COMARCA CODI i NOM EMA GEN FEB MAR ABR MAI JUN JUL AGO SET OCT
Baix Empordà DF la Bisbal d'Empordà 6,6 5,3 10,9 12,6 17,2 21,9 22,9 24,6 20,3 16
Baix Empordà UB la Tallada d'Empordà 6,1 5,2 10,7 12,3 16,6 21,3 22,2 23,8 19,7 15
Baix Empordà UC Monells 6,1 4,6 9,9 11,4 16,5 21,7 23,0 24,5 19,6 15
[...]
TEMPERATURA MITJANA MENSUAL ( ºC ) - 2012
COMARCA CODI i NOM EMA GEN FEB MAR ABR MAI JUN JUL AGO SET OCT
[...]
Solsonès CA Clariana de Cardener 4,6 3,3 10,3 10,2 16,7 22,3 d.i.
Solsonès Z8 el Port del Comte (2.316 m) -0,9 -6,3 -0,2 -2,0 5,3 10,5 10,9 13,8 7,8 4,2
Solsonès VO Lladurs 3,0 2,6 9,5 9,0 15,3 21,4 21,6 24,3 17,5 13,0
Solsonès VP Pinós 3,0 1,6 8,9 9,2 15,4 21,1 21,3 23,8 17,6 13,3
Solsonès XT Solsona d.i. 24,3 18,0 13,5
Tarragonès VQ Constantí 7,9 6,0 11,2 13,1 17,1 21,9 22,6 24,6 20,6 16,6
Tarragonès XE Tarragona - Complex Educatiu 10,2 7,8 12,3 14,6 18,3 23,0 24,2 26,2 23,0 * 18,4
Tarragonès DK Torredembarra 9,7 7,7 12,3 14,3 17,9 22,8 24,3 26,2 22,7 18,5
Terra Alta WD Batea 6,3 5,0 11,2 12,1 18,3 23,0 23,3 25,5 20,2 15,9
Terra Alta XP Gandesa 6,6 5,2 11,2 12,2 18,1 22,9 23,4 25,6 20,4 16,0
complete file for download - UTF8
So, this output is not very easy to parse. What other approach is available?
It seems that every tool I use is only capable to extract information about layout of the table cells, but it doesn't extract the information of belonging to particular column. This is very much apparent if the cells are empty - the empty cells are not in the output, you only get non-empty "cells" with their layout. Does the PDF itself contain this tabular information? If not, it doesn't make sense to search for tool that will extract it.
Paid solutions are not out of question, as it might in the end be cheaper than invest several working days of my time...
What I have tried:
I have encountered several python libraries like pdftables but they are not easy to use for non-python developer like me (I was not even able to run these things). Is there any easier way to accomplish the task?
I am trying to use tm library in R as recommended here, but I have encountered some problems
EDIT: the Cloud SDK recommended by Ian. I registered but I absolutely don't know where to go from here - how to upload pages, recognize them etc:
650
Ok I took a shot at this and I think it will help, although I'm not sure what you want your final output to look like. I'm happy to work more on this so let me know if there are parts you need help with.
I started by downloading a PDF to Text application from CNET.
After installing, I checked these settings:
The important part here is we're using the physical layout option.
This gave us output that looks like this:
Taules de Dades de la Xarxa d’Estacions
Meteorològiques Automàtiques
2 Anuari de dades meteorològiques 2012 / Servei Meteorològic de Catalunya
2 TEMPERATURA MITJANA MENSUAL ( ºC ) - 2012
COMARCA CODI i NOM EMA GEN FEB MAR ABR MAI JUN JUL AGO SET OCT NOV DES ANY
Alt Camp VY Nulles 7,5 5,5 10,9 12,3 16,7 21,6 22,3 24,4 20,1 15,9 11,0 8,5 14,8
Alt Camp DQ Vila-rodona 7,9 5,6 11,0 12,0 16,6 21,6 22,0 24,3 19,9 15,8 11,0 8,6 14,7
Alt Empordà U1 Cabanes 8,2 6,5 11,7 12,6 17,5 22,0 23,1 24,4 20,4 16,6 11,8 8,3 15,3
Alt Empordà W1 Castelló d'Empúries 8,1 6,4 11,6 12,9 17,0 21,1 22,0 23,4 20,1 16,4 12,1 8,5 15,0
Alt Empordà VZ Espolla 9,0 6,7 12,4 12,7 17,8 22,0 23,3 24,8 20,9 16,7 12,0 8,9 15,6
[......]
3 Anuari de dades meteorològiques 2012 / Servei Meteorològic de Catalunya
2 TEMPERATURA MITJANA MENSUAL ( ºC ) - 2012
COMARCA CODI i NOM EMA GEN FEB MAR ABR MAI JUN JUL AGO SET OCT NOV DES ANY
Baix Empordà DF la Bisbal d'Empordà 6,6 5,3 10,9 12,6 17,2 21,9 22,9 24,6 20,3 16,6 11,9 7,6 14,9
Baix Empordà UB la Tallada d'Empordà 6,1 5,2 10,7 12,3 16,6 21,3 22,2 23,8 19,7 15,8 11,7 7,6 14,4
Baix Empordà UC Monells 6,1 4,6 9,9 11,4 16,5 21,7 23,0 24,5 19,6 15,7 11,7 7,2 14,3
Baix Empordà UD Serra de Daró 6,3 5,3 10,6 12,3 16,8 21,6 22,7 24,3 20,3 16,6 12,2 7,7 14,8
[......]
4 Anuari de dades meteorològiques 2012 / Servei Meteorològic de Catalunya
2 TEMPERATURA MITJANA MENSUAL ( ºC ) - 2012
COMARCA CODI i NOM EMA GEN FEB MAR ABR MAI JUN JUL AGO SET OCT NOV DES ANY
Maresme UQ Dosrius - PN Montnegre Corredor 7,2 4,6 10,8 10,7 15,8 20,4 20,8 23,4 18,6 15,1 10,7 7,8 13,9
Maresme WT Malgrat de Mar 7,4 5,4 11,0 13,0 16,7 21,5 22,8 24,6 20,9 17,2 12,9 8,8 15,2
Maresme DD Vilassar de Mar 10,1 7,5 12,6 13,9 17,9 22,4 23,7 25,7 22,1 18,4 13,8 10,8 16,6
Montsià US Alcanar 10,0 7,6 11,8 14,2 17,9 22,7 24,0 25,8 22,0 18,2 13,7 10,7 16,6
Montsià UU Amposta 9,6 7,5 12,1 14,3 18,3 22,8 23,5 25,3 21,6 18,0 13,1 10,8 16,4
[......]
You can see the columns line up much better, but we also have headers and page numbers. Also the COMARCA and i NOM EMA columns were variying length. We want to normalize this to fixed width columns.
I wrote a Perl program to do normalize it, and it also combines tables with the same title, and only prints the headers at the top. It creates an output folder with all the files with the title as the file name.
Here's the code:
#!/bin/perl
use strict;
use warnings;
use open qw(:std :utf8);
use utf8;
my $comarca;
my $nom;
my $print_headers;
my $title = "";
my $fh;
while(<>) {
if ( !/Xarxa d’Estacions/
and !/Meteorològiques Automàtiques/
and !/Servei/
and !/^\s*\d+\s*$/
and !/^\s*$/ ) {
chomp($_);
if ( /^\s*2/ ) { #title
s/^\s*2\s*//;
if ( $title ne $_ ) {
$title = $_;
$print_headers = 1;
}
} elsif ( /COMARCA/ ) { #column headers
my ($first_col, $second_col, @the_rest) = split(/(CODI +i NOM EMA *)/, $_);
$comarca = length $first_col;
$nom = length $second_col;
if ( $print_headers ) {
my $str = sprintf "%-50s %-50s %s\n", $first_col, $second_col, join("", @the_rest);
write_string($str);
$print_headers = 0;
}
} else { #data
my ($one, $two, $three) = unpack("A${comarca}A${nom}A*", $_);
my $str = sprintf "%-50s %-50s $three\n", $one, $two;
write_string($str);
}
}
}
sub write_string {
my $string = shift;
my $file_name = $title;
$file_name =~ s/[\/\\]//g;
open ($fh, '>>', ".\/output_folder\/${file_name}.txt") or die "Couldn't open: $!";
print $fh $string;
close ($fh);
}
There are still a few imperfections in the output (you'll see these when you run this), but I wanted to get some feedback on what output would work best for you. There is definitely more we can do to improve the code! The output directory tree looks like this:
Matt@MattPC ~/perl/pdftotext
$ find .
.
./convert.pl
./EMAtaules2012.txt
./output.txt
./output_folder
./output_folder/AMPLITUD TÈRMICA MITJANA MENSUAL ( ºC ) - 2012?.txt
./output_folder/AMPLITUD TÈRMICA MÀXIMA MENSUAL ( ºC ) - 2012?.txt
./output_folder/DIRECCIÓ DOMINANT DEL VENT - 2012?.txt
./output_folder/GRUIX MÀXIM MENSUAL DE NEU AL TERRA ( cm ) - 2012?.txt
./output_folder/HUMITAT RELATIVA MITJANA MENSUAL ( % ) - 2012?.txt
./output_folder/MITJANA MENSUAL DE LA HUMITAT RELATIVA MÀXIMA DIÀRIA ( % ) - 2012?.txt
./output_folder/MITJANA MENSUAL DE LA HUMITAT RELATIVA MÍNIMA DIÀRIA ( % ) - 2012?.txt
[......]
Where a file might look like this:
COMARCA CODI i NOM EMA GEN FEB MAR ABR MAI JUN JUL AGO SET OCT NOV DES ANY
Alt Camp VY Nulles 7,5 5,5 10,9 12,3 16,7 21,6 22,3 24,4 20,1 15,9 11,0 8,5 14,8
Alt Camp DQ Vila-rodona 7,9 5,6 11,0 12,0 16,6 21,6 22,0 24,3 19,9 15,8 11,0 8,6 14,7
Alt Empordà U1 Cabanes 8,2 6,5 11,7 12,6 17,5 22,0 23,1 24,4 20,4 16,6 11,8 8,3 15,3
Alt Empordà W1 Castelló d'Empúries 8,1 6,4 11,6 12,9 17,0 21,1 22,0 23,4 20,1 16,4 12,1 8,5 15,0
Alt Empordà VZ Espolla 9,0 6,7 12,4 12,7 17,8 22,0 23,3 24,8 20,9 16,7 12,0 8,9 15,6
Alt Empordà D6 Portbou 9,6 5,5 12,7 12,5 17,4 21,5 22,9 24,4 19,8 17,0 12,3 10,1 15,5
[......]
Headers are only at the top and all the columns line up. This one is TEMPERATURA MITJANA MENSUAL ( ºC ) - 2012.
I've been thinking of uploading more of the output to a file hosting site, but I don't know which would be a good one, suggestions?
Hope this helps you Tomas!
EDIT: Example of missing entries from AMPLITUD TÈRMICA MÀXIMA MENSUAL ( ºC ) - 2012:
Solsonès VP Pinós 1 3,1 26 16,9 13 16,7 15 16,6 17 19,2 11 19,6 24 20,4 17 19,1 01 17,5 16 16,5 06 13,1 08 13,9 24 20,4 17/07
Solsonès XT Solsona 22,2 25 22,2 09 20,1 16 18,6 06 15,3 07 18,2 23 22,2 09/08
Tarragonès VQ Constantí 1 6,4 19 21,9 23 19,7 11 12,9 07 17,4 23 17,2 21 15,1 18 14,2 18 18,0 15 15,1 02 14,9 07 16,0 10 21,9 23/02
Updated scripts for processing the input file:
#!/bin/perl
use strict;
use warnings;
use open qw(:std :utf8);
use utf8;
use charnames ':full';
my @column_lengths;
my $print_headers;
my $title = "";
my $fh;
while(<>) {
if ( !/Xarxa d’Estacions/
and !/Meteorològiques Automàtiques/
and !/Servei/
and !/^\s*\d+\s*$/
and !/^\s*$/ ) {
s/[\r\n]+//g;
s/ +\d+$//;
if ( /^\s*2/ ) { #title
s/^\s*2\s*//;
if ( $title ne $_ ) {
$title = $_;
$print_headers = 1;
}
} elsif ( /COMARCA/ ) { #column headers
my $comarca = (split(/(COMARCA *)/, $_))[1];
my $codi = (split(/(CODI *)/, $_))[1];
my $inomema = (split(/(i NOM EMA *) /, $_))[1];
my $the_rest = (split(/(i NOM EMA *) /, $_))[2];
my @rest = split(/( \w+ *)/, $the_rest);
undef @column_lengths;
push @column_lengths, length $comarca;
push @column_lengths, length $codi;
push @column_lengths, length $inomema;
for (@rest) {
if ( $_ ) {
push @column_lengths, length $_;
}
}
$column_lengths[-1] = "*";
if ( $print_headers ) {
$print_headers = 0;
write_string(join(";", unpack( "A" . join("A", @column_lengths), $_)) . "\n");
}
} else { #data
write_string(join(";", unpack( "A" . join("A", @column_lengths), $_)) . "\n");
}
}
}
sub write_string {
my $string = shift;
my $file_name = $title;
$file_name =~ s/[º]//g;
$file_name =~ s/[^\w ]//g;
$file_name =~ s/ +/ /g;
$file_name =~ s/È/E/g;
$file_name =~ s/À/A/g;
$file_name =~ s/Ó/O/g;
$file_name =~ s/Í/I/g;
$file_name =~ s/Ç/C/g;
open ($fh, '>>', ".\/output_folder\/${file_name}.txt") or die "Couldn't open: $!";
print $fh $string;
close ($fh);
}
This one combines lines with the d.i. on the next line.
#!/bin/perl -i
use strict;
use warnings;
my $last = <>;
while(<>) {
my @current_array = split(";", $_);
if ( /^;+[ \t]+.d\.i\./ ) {
my @last_array = split(";", $last);
my @combined_array;
#print "matches\n";
for my $element (@current_array) {
if ( $element =~ /d\.i\./ ) {
push @combined_array, $element;
shift @last_array;
} else {
push @combined_array, $last_array[0];
shift @last_array;
}
}
undef @current_array;
@current_array = @combined_array;
}
$last = join ";", @current_array;
print $last;
}
The output is in csv format with semicolon delimiters.
87
Here is an R solution, but it is not without its flaws.
# Read the lines of your file into R
x <- readLines("EMAtaules2012.txt")
# Make sure it shows up as UTF-8 to get proper accents and so on
Encoding(x) <- "UTF-8"
# Identify the lines where the data starts
Start <- grep("COMARCA", x)
# Grab the names of each table
ListNames <- gsub("\\s+", " ", x[Start-2])
# Figure out the number of rows of data per page
Runs <- rle(diff(cumsum(x != "")))
Nrows <- Runs$lengths[Runs$lengths > 4]+1
# Make our life easier by making this column name
# a single string
x <- gsub("i NOM EMA", "i_NOM_EMA", x)
# Since these are fixed width files, we need to figure
# out the widths of each column. This is the sum of
# the number of characters in the header row plus
# the number of spaces between each column name
Spaces <- gregexpr(x[Start], pattern="\\s+")
Spaces <- lapply(Spaces, function(x) c(attr(x, "match.length"), 0))
Chars <- lapply(strsplit(x[Start], "\\s+"), nchar)
Widths <- lapply(seq_along(Spaces),
function(x) rowSums(cbind(Spaces[[x]],
Chars[[x]])))
read.fwf to get the data in# Now, you can use `read.fwf` to read your data files in
temp <- lapply(seq_along(Start), function(fwf) {
A <- read.fwf(textConnection(x),
widths = c(Widths[[fwf]]),
header = FALSE,
skip = Start[fwf]+1,
n = Nrows[fwf]-2,
blank.lines.skip = TRUE,
strip.white = TRUE,
stringsAsFactors = FALSE)
# Add in the column names
names(A) <- scan(what = "character",
file = textConnection(x[Start[fwf]]),
quiet = TRUE)
A
})
# Assign the table names
names(temp) <- ListNames
# Some more cleanup. The original tables span multiple pages
# in the PDF, but we can `rbind` them together in R
Tables <- unique(ListNames)
final <- lapply(seq_along(Tables), function(final) {
A <- do.call(rbind, temp[names(temp) %in% Tables[final]])
rownames(A) <- NULL
A
})
# Add the names back in
names(final) <- Tables
# View the first few rows and columns of the first three tables
lapply(final[1:3], function(y) head(y[1:5], 3))
# $` TEMPERATURA MITJANA MENSUAL ( ºC ) - 2012`
# COMARCA CODI i_NOM_EMA GEN FEB
# 1 Alt Camp DQ Vila-rodona 7,9 5,6
# 2 Alt Empordà U1 Cabanes 8,2 6,5
# 3 Alt Empordà W1 Castelló d'Empúries 8,1 6,4
#
# $` TEMPERATURA MÀXIMA MITJANA MENSUAL ( ºC ) - 2012`
# COMARCA CODI i_NOM_EMA GEN FEB
# 1 Alt Camp DQ Vila-rodona 13,1 11,7
# 2 Alt Empordà U1 Cabanes 15,1 12,4
# 3 Alt Empordà W1 Castelló d'Empúries 14,4 11,7
#
# $` TEMPERATURA MÍNIMA MITJANA MENSUAL ( ºC ) - 2012`
# COMARCA CODI i_NOM_EMA GEN FEB
# 1 Alt Camp DQ Vila-rodona 3,8 0,5
# 2 Alt Empordà U1 Cabanes 2,4 0,9
# 3 Alt Empordà W1 Castelló d'Empúries 2,1 0,5
# Some tables, like those on page 76 (for the table "DIRECCIÓ DOMINANT DEL VENT"), had more columns than others.
# Did our script take care of that?
names(final$` DIRECCIÓ DOMINANT DEL VENT`)
# [1] "COMARCA" "CODI" "i_NOM_EMA" "vent" "GEN" "FEB"
# [7] "MAR" "ABR" "MAI" "JUN" "JUL" "AGO"
# [13] "SET" "OCT" "NOV" "DES" "ANY"
It sort of worked. But, your input file is not perfect, and that means that there will still be a lot of cleaning up to to. For instance, some columns in the PDF seem to have multiple values. Not sure how you would be able to do any analysis on those.
Hopefully, the comments in the above code help get you started on figuring out how to go about scraping the data in a better way.
Continuing after "Part 1" above, here's a solution that relies on (gasp) Excel. The basic idea is that Excel actually does a pretty decent job of detecting where the column breaks are if you import text as Fixed Width.
So, we use R to break up the text into separate pages, one file per page, only the data (not the column names or the row names, which are mostly the same across all datasets).
With that, here's the last R step:
# Output just the data
temp <- lapply(seq_along(Widths), function(y) {
DEL <- sum(Widths[[y]][1:3])-2
A <- substring(x[(Start[y]+1):(sum(Start[y], Nrows[y]))], DEL)
writeLines(A, paste("temp_", y, ".txt", collapse = ""))
A
})
Let's open file "temp_9.txt", which is one that has the missing columns:
^^ Make sure "Fixed Width" is selected -- It should be by default since the file has no delimiters.
^^ Excel shows you a preview of where it is going to make the columns.
^^ I've highlighted the "problem rows" for you to see how it worked out.
28
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
