Return to site

使用Google表格刮取和清理您的数据:仔细观察

您是否曾经想要自动从网页中提取数据 - 例如建立Twitter受众 - 并想要一种神奇地让网页上的所有Twitter句柄出现在您的Google表格中,但不知道如何?如果学习Python不是你的一杯茶,使用Google表格中的一些公式将允许您轻松快速地从URL中抓取数据,如果您手动执行此操作,则可能需要数小时。

对于Windows用户,Niels Bosma 用于Excel的令人惊叹的SEO插件是一个可用于此目的的选项,但如果您在Mac上分析数据,那么Google表格中的公式教程将帮助您的生活更轻松,插件在Mac上不起作用。

在Google表格中,我希望使用3种公式来节省大量时间和顶空。这些是:

  1. IMPORTXML
  2. QUERY
  3. REGEXEXTRACT

只需这3个公式,您就应该能够抓住并清理您可能遇到的任何目的所需的数据 - 无论是策划Twitter受众,分析链接还是您能想到的任何其他内容。这些公式的优点在于它们的多功能性,因此它们的用例实际上是无限的。通过理解这背后的概念,可以根据个别用例替换变量。但是,刮取,清理和显示数据的基本过程将保持不变。

应该注意的是,抓取有局限性,有些网站(如谷歌)并不真的希望任何人抓取他们的内容。这篇文章的目的纯粹是为了帮助你聪明的Moz读者比你想象的更快,更容易地提取和排序数据。

让我们在Twitter上找到一些有趣的人,我们应该遵循(或目标。这真的很重要吗?)。在Twitter上搜索有趣人物的主题,我发现自己登陆以下页面:

答对了。直接复制和粘贴到Google Doc将是一场灾难; 在页面上有太多其他内容。这就是IMPORTXML的用武之地。

第一步是打开Goog​​le表格并将所需的URL输入到单元格中。它可以是任何单元格,但在下面的示例中,我将URL放入单元格A1中。

就在我们开始进行抓取之前,我们需要确切地弄清楚我们计划抓取哪些数据。在这种情况下,它碰巧是Twitter句柄,所以这就是我们要做的事情。

首先,右键单击我们的目标(Twitter手柄),然后单击“检查元素”。

进入“Inspect Element”后,我们想知道我们的目标在页面上的位置。

因为我们想要Twitter句柄而不是URL,所以我们将关注<a> </a>标签中的元素/修饰符/标识符“target”而不是“href”。我们也注意到<a> </a>标签是<h3> </ h3>标签的“子”。这些值意味着什么是另一篇文章的主题,但我们需要记住的是,对于这个特定的URL,这是我们需要提取的所需信息的存在。它几乎肯定会生活在不同的区域,在任何其他给定的URL上都有不同的修饰符; 这只是我们所在网站所特有的信息。

让我们看看可怕的东西(也许?):如何编写公式。

我将公式放在单元格A3中,我有红色箭头。从突出显示的矩形中可以看出,我写了= IMPORTXML(A1,“// h3 //一个[@target ='_ blank']”),它产生了一个精彩的,有组织的列表,列出了所有顶级Twitter句柄。这页纸。瞧。很酷,对吗?

这样做时要记住的是,值是通过公式创建的,因此尝试定期复制和粘贴它们会变得混乱; 您需要复制并粘贴为值。

现在,让我们打破疯狂。

与表格中的任何其他功能一样,您需要以等号开头,因此我们从= IMPORTXML开始。接下来,我们找到具有目标URL的单元格(在本例中为单元格A1),然后添加逗号。开始查询时始终需要双引号,后跟两个正斜杠(“//”)。接下来,选择要刮削的元素(在本例中为h3标签)。我们不希望全部h3元素中的信息,只是<a> </a>标记的特定部分 - 特别是我们找到Twitter句柄的“目标”部分。为了捕获这部分,我们添加// a [@target = '_blank'],它只指定<a> </a>标记的target ='_ blank'部分。将它们放在一起,公式= IMPORTXML(A1,“// h3 // a [@ target =' _blank']“)可以翻译为”从单元格A1中的URL,选择带有<h3>的数据

在这种特殊情况下,Twitter句柄是唯一可以基于我们的公式以及它最初是如何在HTML中编写的元素,但有时情况并非如此。如果我们正在寻找旅行博客并遇到如下所示的网站,我们所需的Twitter处理文本在文本段落中,该怎么办?

看一下Inspect Element按钮,我们看到以下信息:

在顶部矩形中是div和我们需要的类,在第二个矩形中是我们需要的另一半信息:<p>标记。html中使用<p>标记来指定给定段落的位置。我们正在寻找的Twitter句柄位于文本段落中,因此我们需要选择<p>标签作为要刮的元素。

再次,我们将URL输入到单元格中(任何空单元格都可以工作)并写出新公式= IMPORTXML(A1,“// div [@ class ='span8 column_container'] // p”)。而不是像前面的例子中那样选择所有的h3元素,这次我们在div元素中找到了具有“span8 column_container”类的所有<p>标签。我们在div元素中寻找具有“span8 column_container”类的<p>标签的原因是因为页面上还有其他<p>标签包含我们可能不需要的信息。所有Twitter句柄都包含在特定分类div中的<p>标记中,因此通过选择它,我们将选择最合适的数据。

但是,结果并不完美,看起来像这样:

结果不太理想,但可管理 - 我们最终只想要Twitter处理,但提供了大量其他文本。在绿色矩形中突出显示的结果更接近我想要的结果,但不在我需要的列中(在屏幕截图的视图中还有另一个页面,但大多数是我需要它们的地方)。为了确保我们以适当的格式获取所有数据,我们可以复制和粘贴列A-C中的所有内容的值,这将删除由公式填充的值,并将其替换为可以操作的硬值。完成后,我们可以将外围值(B列中的一个和C列中的一个)剪切并粘贴到A列中的相应单元格中。

我们所有的数据现在都在A栏; 但是,某些单元格包含不包含Twitter句柄的信息。我们将通过运行= QUERY函数并将包含“@”的单元格与不包含“@”的单元格分开来解决此问题。在一个单独的单元格中(我使用了单元格C4),我们将输入= query(A4:A36,或“选择A,其中A包含'@'”)并按Enter键。繁荣。从这里开始,我们将只有包含Twitter句柄的单元格,这是一个巨大的改进,而不是包含带有和不带Twitter句柄的单元格的混合结果。为了解释,我们的公式可以翻译为“从阵列A4:A36中,当该单元格包含'@'时选择A列中的单元格。”这是非常不言自明的,但仍然是一个非常强大的梦幻公式。下图显示了这个样子:

请记住,我们刚刚提取的结果将包含我们需要删除的单元格中的多余信息。要做到这一点,我们需要运行= REGEXEXTRACT公式,这几乎可以消除对= RIGHT,= LEFT,= MID,= FIND和= LEN公式或这些公式的任何混合的任何需求。虽然有用,但这些函数可能会有点复杂,需要协同工作才能产生与= REGEXEXTRACT相同的结果。可以在此处找到具有视觉效果的这些公式的更详细说明。

我们将对运行= QUERY公式生成的结果运行公式。使用= REGEXEXTRACT,我们将在查询列中选择顶部单元格(在本例中为C4),然后选择以“@”开头的所有内容,这是我们正在寻找的内容的开始。我们所需的公式看起来像= REGEXEXTRACT(C4,“\ @。*”)。反斜杠表示转义后续字符,而。*表示选择后面的所有内容。因此,该公式可以翻译为“对于单元格C4,从”@“开始提取所有内容。

要获得所有其他值,我们需要做的就是单击并抓住单元格E4的右下角并将其向下拖动,直到我们的数组在单元格C28处结束。向下拖动E4的角将其中的公式应用于拖动中包含的单元格。我们想要包括E28,因为相应的单元格C28是我们应用公式的数组中的最后一个单元格。这样做将提供如下结果:

虽然输出很好而且干净,但E列中的数据是按公式创建的,不能轻易操作。我们需要在此列中复制和粘贴值,以获得我们需要的所有内容并能够操作数据。

如果您想要使用Google表格并制作自己的副本,可以在此处找到原文。

希望这有助于为您如何轻松地从网页上清除和清理数据提供一些指导和见解。如果您有兴趣了解更多信息,请参阅以下资源列表:

  1. Xpath Data Scraping教程视频(适用于PC用户)
  2. 适用于Google文档的ImportXML指南
  3. 内容营销人员的数据抓取指南
  4. 如何充分利用正则表达式

想要抓取更多用例,提示和注意事项吗?我采访了以下专家,了解他们对网络抓取世界的见解:

  1. Dave Sottimano,Define Media Group,Inc。战略副总裁
  2. Seer Gingrich,Seer Interactive高级SEO经理
  3. 丹·巴特勒,SEO主管,Builtvisible
  4. Tom Critchlow,tomcritchlow.com
  5. Ian Lurie,Portent,Inc。首席执行官兼创始人
  6. iPullRank创始人Mike King
问题1:描述自动抓取“挽救你的生命”的时间。

“在hreflang首次发布时,有很多实现和配置问题。虽然孤立的测试非常有用,但是SERP的自动化扫描帮助我实现了某些国际配置的影响,并为客户做出了重要决策。“ - Dave Sottimano

“我们想要一种可视化论坛数据的方法,以查看客户的受众最常谈论的问题类型,以便能够从该数据中创建内容策略。我们抓住了Reddit和各种论坛,抓住了诸如帖子标题,观点,回复数量甚至帖子内容等数据。我们能够汇总所有数据,将最热门的问题放在一起,并在帖子标题和可能是内容主要目标的评论中可视化关键词。我们在Seer经常使用抓取的另一种方法是进行关键字研究。能够查看更大的种子关键字集提供了巨大的优势和时间节省。此外,能够轻松提取搜索结果以通知您的关键字研究非常重要,如果没有抓取,就无法完成。“- 查德·金里奇

“我会说刮痧会定期挽救我的生命,但有一个特别突出的情况是客户要求Schema.org以6种不同语言为其60家酒店中的每一家提供加价。直截了当的请求,或者我认为 - 他们有非常有限的开发资源来实现自己,以及一个老化的CMS,它不提供简单下载数据库的功能,以便可以追加标记。在Google表格中点击ImportXML,我可以抓取任何东西(标题,源图像,描述,地址,地理坐标等),并结合一系列连接能够编译数据,所以所需要的只是上传相应页面的代码。“ - Dan Butler

“当我临时刮痧拯救了我的培根时,我已经失去了很多次。获取一堆页面并将其元描述提取到Excel中有一些低压力时间是有用的,但最近我最喜欢的一个例子是我的一个客户正在与Facebook进行谈判以包含在F8中。我们正在处理数据以进入主题演讲,并需要以合理的规模(几千个URL)分析URL的一些社交媒体数据。这是客户系统中某处作为SQL查询存在的数据类型,但我们没有时间让开发团队为我们提供数据。为了获得Facebook的快速转变,花20分钟自己获取和分析数据是非常自由的。“ - Tom Critchlow

“我们发现一个客户端同时将所有主页链接指向了一个暂存子域,并且他们在执行它们大约一个小时之后就在他们的主页上添加了一个元机器人noindex / nofollow。我们看到了爬行的结果,并想:“呃,这不可能是正确的。” 我们假设我们的爬虫坏了。不。这是我们希望的最佳时机。但它使客户免于可能花费数万美元的重大失误。另一次我们不得不从拥有静态站点的客户端进行大规模内容迁移。客户端实际上开始剪切和粘贴数千页。我们将它们全部写入数据库,解析它们并自动完成整个过程。“ - Ian Lurie

“一般来说,我讨厌任何需要复制和粘贴的任务,因为无论你何时这样做,计算机都可以为你做这件事。对我来说最突出的那一刻是我第一次在Razorfish开始时他们给了我一个从Majestic出口分割300万个链接的任务。我写了一个PHP脚本,每个链接收集30个数据点。这是在CognitiveSEO甚至LinkDetective等任何工具存在之前。非常安全地说,这使我无法将电脑从建筑物的顶部扔掉。“ - 迈克金

问题2:您首选的工具/方法是什么?

“取决于工作的规模和类型。对于快速的东西,它通常是谷歌文档(ImportXML,或者我将编写自定义函数),并且在规模上我非常喜欢Scraping Hub。随着搜索引擎优化任务越来越接近数据分析(科学),我认为我更有可能依赖大数据分析平台提供的网络导入模块,如RapidMiner或Knime进行任何抓取。“ - Dave Sottimano

“ 刚开始,Outwit是一个很棒的工具。它本质上是一个浏览器,可以让您使用源代码轻松构建scraper。...我已经开始使用Ruby来获得更多的控制和可扩展性。我之所以选择Ruby是因为前端/后端组件,但Python也是一个很好的选择,绝对是刮擦的标准(Google使用它)。我认为,当你对刮擦感兴趣时,你不可避免地会学习编码,因为你几乎总是需要一些你不能轻易从简单工具中获得的东西。我喜欢的其他工具是刮板Chrome插件,用于快速单页擦除,Scrapebox,RegExr和Text2re,用于构建和测试正则表达式。当然还有Excel的Excel工具。“ - Chad Gingrich

“我喜欢像Screaming Frog和URL Profiler这样的工具,但发现通过这种方法背后的简单电子表格的强大功能可以节省时间来管理输出,执行一系列连续查找,并将其转换为持续维护的动态报告。Google表格还可以让您创建自定义脚本,因此您可以连接到多个API,甚至可以刮取和转换JSON输出。嘿,它也是免费的!“ - Dan Butler

“就我个人而言,Google Docs是迄今为止用途最广泛,最强大,最快速的方法。我开始使用ImportXML,并在逐步使用Google Scripts之前使用它,以及更强大,更强大和用户驱动的用途。偶尔,我已经使用Python来构建自己的刮刀,但到目前为止,我从未真正证明这是有效利用我的时间 - 虽然这很有趣。“ - Tom Critchlow

“我们内部拥有自己的工具集。它基于Python和Cython构建,并且具有非常强大的正则表达式引擎,因此我们可以提取任何我们想要的东西。当我们需要它们做一些非常独特的事情时,我们也会编写自定义工具,比如分析图像类型/压缩。对于真正的,非常大的网站 - 数百万页 - 我们可能会使用DeepCrawl。但是我们内部的工具集可以在99%的时间内完成这一工作,并为我们提供了很大的灵活性。“ - Ian Lurie

“虽然我现在知道有很多WYSIWYG工具,但我仍然更喜欢编写脚本。这样我就能得到我想要的东西,而且它的格式正是我想要的。“ - 迈克金

问题3:网络抓取需要注意的常见缺陷是什么?

“数据不好。这个范围从隐藏字符和编码问题到糟糕的HTML,有时你只是被一些聪明的系统管理员喂垃圾。作为一般规则,我宁愿为API买单而不是刮掉。“ - Dave Sottimano

“仅仅因为你可以刮掉某些东西并不意味着你应该这样做,有时候太多的数据会混淆最终的目标。我想概述一下我要刮掉什么以及为什么我需要它/我将在抓取一个数据之前对这些数据做些什么。在前面使用脑力,让刮痧自动完成其余部分,你会在更好的地方走出另一边。“ - Chad Gingrich

“如果您正在设置动态报告或构建自己的工具,请确保您运行更改检测,以便在X%的目标HTML发生更改时收到警报,这可能会使您的Xpath无效。另一方面,通过公共HTTP获取请求或XHR严重解析私有API凭证/身份验证是多么疯狂,如果站点不希望在公共域中访问它们,则需要开始锁定这些内容。“ - Dan男管家

“计算机最常见的陷阱是他们只做你告诉他们的事情 - 这听起来很明显,但这是一个很好的提醒,当你感到沮丧时,你通常只能责怪自己。哦 - 不要忘记每隔一段时间检查一次你的经常性任务。“ - Tom Critchlow

“减慢你的爬行速度很重要我甚至不是在谈论谷歌的刮擦。我在谈论爬行其他人的网站。我一直惊讶于大多数网站技术堆栈的优化程度。如果你开始每秒打一页,你实际上可能会减慢或崩溃一个价值数百万美元的网站。我们曾经以每秒一页的抓取次数杀死了一个客户的网站 - 他们是财富1000强企业。这太荒谬了,但它比你想象的更频繁。此外,如果您没有设计爬虫以检测并避免蜘蛛陷阱,您最终可能会抓取250,000页完全重复的垃圾。这是浪费服务器资源。找到无限扩展的网址或其他问题后,请让您的抓取工具继续运行。“ - Ian Lurie

“我遇到的最大陷阱是许多网站都在使用JavaScript呈现内容,而标准的基于文本的抓取工具并不总能削减它。通常情况下,我正在使用无头浏览器。我最喜欢的PhantomJS抽象是NightmareJS,因为它快速而简单,所以我使用它。另一件事是,有时人们的代码是如此糟糕,以至于没有结构,所以你最终会抓住所有内容并需要对其进行排序。“ - Mike King

All Posts
×

Almost done…

We just sent you an email. Please click the link in the email to confirm your subscription!

OK