二十五个 SQL 练习题
这些问题和示例解决方案将保持你的技能敏锐。
图片来源: Unsplash
介绍
结构化查询语言(SQL)用于检索和操作存储在关系数据库中的数据。精通 SQL 是许多技术工作的重要先决条件，需要一些实践。
为了补充网上可用的 SQL 培训资源(pg exercise、 LeetCode 、 HackerRank 、 Mode )，我整理了一个我最喜欢的问题列表，您可以手动解决这些问题，也可以用 PostgreSQL 实例来解决。
这些问题涵盖以下关键概念:
- 基本检索(选择，从)
- 创建和别名(使用，AS，GENERATE_SERIES)
- 过滤 (DISTINCT，WHERE，HAVING，AND，OR，IN，NOT IN)
- 聚合(分组依据，计数、总和、平均值)
- 连接(内连接、左连接、一个或多个等式上的全外连接、交叉连接、联合和联合所有)
- 条件语句**(CASE-WHEN-THEN-ELSE-END)**
- 窗口函数 (RANK，DENSE_RANK，ROW_NUMBER，SUM with PARTITION BY - ORDER BY)
- 格式化(限制、排序、整型、浮点型或日期型、串联、合并)
- 算术 运算和比较 (+，-，，/，//，^，<，>，=，！=)*
- 日期时间操作(提取(月/日/年))
你自己试试
你可以下载 PostgreSQL 和 PSequel (参见本教程的分步安装指南)，然后运行下面文本中灰色框中显示的查询，自己尝试一下。PSequel 只适用于 Mac——如果你用的是 PC，你可以试试这些 Windows 替代品中的一个。
使用 PSequel 和下面提供的输入表自己尝试这些查询。
下面显示的每个查询中的第一个文本块建立输入表，并遵循以下格式:
WITH input_table (column_1, column_2)
AS (VALUES
(1, 'A'), (2, 'B'))
您可以使用 PSequel(如上所示)查询输入表，并使用该模板为您自己的问题轻松构建新表。
基于 Web 的 SQL 培训资源在几个方面存在不足。例如，LeetCode 不支持使用窗口功能，并将其最有趣的问题隐藏在付费墙之后。此外，在浏览器中运行 SQL 查询可能会非常慢，因为数据集很大，对于非高级用户来说，检索速度通常会受到限制。另一方面，本地执行查询是即时的，并允许通过语法错误和中间表进行快速迭代。我发现这是一次更令人满意的学习经历。
下面列出的问题包括证实在 PostgreSQL 中有效的示例解决方案。请记住，通常有多种方法可以获得 SQL 问题的正确答案。我更喜欢使用公共表表达式(cte)而不是嵌套子查询——cte 可以更线性地说明数据争论的顺序。然而，这两种方法可以产生相同的解决方案。我也喜欢遵循将 SQL 操作符全部大写的惯例(SELECT、FROM、WHERE 等。)，小写的列名(user_id，date 等。)，以及简单的表别名(t1，t2 等。)可能的话。
下面显示的代码片段可以按原样在 PSequel 中运行，以产生显示的结果。注意 Postgres 的一个怪癖:分数必须乘以 1.0 才能从整数转换成浮点格式。这在 SQL 的其他实现中是不需要的，在访谈中也是不期望的。
欢迎在评论中留下你的备选答案！
问题
1.取消率
根据下表中的用户 id、操作和日期，编写一个查询来返回每个用户的发布率和取消率。
WITH users (user_id, action, date)
AS (VALUES
(1,'start', CAST('01-01-20' AS date)),
(1,'cancel', CAST('01-02-20' AS date)),
(2,'start', CAST('01-03-20' AS date)),
(2,'publish', CAST('01-04-20' AS date)),
(3,'start', CAST('01-05-20' AS date)),
(3,'cancel', CAST('01-06-20' AS date)),
(1,'start', CAST('01-07-20' AS date)),
(1,'publish', CAST('01-08-20' AS date))), *-- retrieve count of starts, cancels, and publishes for each user*t1 AS (
SELECT
user_id,
SUM(CASE WHEN action = 'start' THEN 1 ELSE 0 END) AS starts,
SUM(CASE WHEN action = 'cancel' THEN 1 ELSE 0 END) AS cancels,
SUM(CASE WHEN action = 'publish' THEN 1 ELSE 0 END) AS publishes
FROM users
GROUP BY 1
ORDER BY 1)*-- calculate publication, cancelation rate for each user by dividing by number of starts, casting as float by multiplying by 1.0 (default floor division is a quirk of some SQL tools, not always needed)*SELECT
user_id,
1.0*publishes/starts AS publish_rate,
1.0*cancels/starts AS cancel_rate
FROM t1
2.净值的变化
从下面两个用户之间的交易表中，编写一个查询来返回每个用户的净值变化，按净变化递减排序。
WITH transactions (sender, receiver, amount, transaction_date)
AS (VALUES
(5, 2, 10, CAST('2-12-20' AS date)),
(1, 3, 15, CAST('2-13-20' AS date)),
(2, 1, 20, CAST('2-13-20' AS date)),
(2, 3, 25, CAST('2-14-20' AS date)),
(3, 1, 20, CAST('2-15-20' AS date)),
(3, 2, 15, CAST('2-15-20' AS date)),
(1, 4, 5, CAST('2-16-20' AS date))), *-- sum amounts for each sender (debits) and receiver (credits)*debits AS (
SELECT
sender,
SUM(amount) AS debited
FROM transactions
GROUP BY 1 ),credits AS (
SELECT
receiver,
SUM(amount) AS credited
FROM transactions
GROUP BY 1 )*-- full (outer) join debits and credits tables on user id, taking net change as difference between credits and debits, coercing nulls to zeros with coalesce()*SELECT
COALESCE(sender, receiver) AS user,
COALESCE(credited, 0) - COALESCE(debited, 0) AS net_change
FROM debits d
FULL JOIN credits c
ON d.sender = c.receiver
ORDER BY 2 DESC
3.最常见的项目
从包含日期和订购项目列表的下表中，编写一个查询以返回在每个日期订购的最频繁的项目。在平局的情况下返回多个项目。
WITH items (date, item)
AS (VALUES
(CAST('01-01-20' AS date),'apple'),
(CAST('01-01-20' AS date),'apple'),
(CAST('01-01-20' AS date),'pear'),
(CAST('01-01-20' AS date),'pear'),
(CAST('01-02-20' AS date),'pear'),
(CAST('01-02-20' AS date),'pear'),
(CAST('01-02-20' AS date),'pear'),
(CAST('01-02-20' AS date),'orange')),*-- add an item count column to existing table, grouping by date and item columns*t1 AS (
SELECT
date,
item,
COUNT(*) AS item_count
FROM items
GROUP BY 1, 2
ORDER BY 1),*-- add a rank column in descending order, partitioning by date*t2 AS (
SELECT
*,
RANK() OVER (PARTITION BY date ORDER BY item_count DESC) AS date_rank
FROM t1)*-- return all dates and items where rank = 1*SELECT
date,
item
FROM t2
WHERE date_rank = 1
4.最新行动之间的时间差
根据下表中的用户操作，编写一个查询，按用户 ID 的升序为每个用户返回最后一个操作和倒数第二个操作之间经过的时间。
WITH users (user_id, action, action_date)
AS (VALUES
(1, 'start', CAST('2-12-20' AS date)),
(1, 'cancel', CAST('2-13-20' AS date)),
(2, 'start', CAST('2-11-20' AS date)),
(2, 'publish', CAST('2-14-20' AS date)),
(3, 'start', CAST('2-15-20' AS date)),
(3, 'cancel', CAST('2-15-20' AS date)),
(4, 'start', CAST('2-18-20' AS date)),
(1, 'publish', CAST('2-19-20' AS date))), *-- create a date rank column, partitioned by user ID, using the ROW_NUMBER() window function* t1 AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY action_date DESC) AS date_rank
FROM users ),*-- filter on date rank column to pull latest and next latest actions from this table*latest AS (
SELECT *
FROM t1
WHERE date_rank = 1 ),next_latest AS (
SELECT *
FROM t1
WHERE date_rank = 2 )*-- left join these two tables, subtracting latest from second latest to get time elapsed* SELECT
l1.user_id,
l1.action_date - l2.action_date AS days_elapsed
FROM latest l1
LEFT JOIN next_latest l2
ON l1.user_id = l2.user_id
ORDER BY 1
5.超级用户
一家公司将其超级用户定义为至少进行过两次交易的用户。从下表中编写一个查询，为每个用户返回他们成为超级用户的日期，首先按最早的超级用户排序。不是超级用户的用户也应该出现在表中。
WITH users (user_id, product_id, transaction_date)
AS (VALUES
(1, 101, CAST('2-12-20' AS date)),
(2, 105, CAST('2-13-20' AS date)),
(1, 111, CAST('2-14-20' AS date)),
(3, 121, CAST('2-15-20' AS date)),
(1, 101, CAST('2-16-20' AS date)),
(2, 105, CAST('2-17-20' AS date)),
(4, 101, CAST('2-16-20' AS date)),
(3, 105, CAST('2-15-20' AS date))), *-- create a transaction number column using ROW_NUMBER(), partitioning by user ID*t1 AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_date) AS transaction_number
FROM users),*-- filter resulting table on transaction_number = 2*t2 AS (
SELECT
user_id,
transaction_date
FROM t1
WHERE transaction_number = 2 ),*-- left join super users onto full user table, order by date* t3 AS (
SELECT DISTINCT user_id
FROM users )SELECT
t3.user_id,
transaction_date AS superuser_date
FROM t3
LEFT JOIN t2
ON t3.user_id = t2.user_id
ORDER BY 2
6.内容推荐(硬)
使用以下两个表，编写一个查询，根据社交媒体用户的朋友喜欢但尚未标记为喜欢的页面，向他们返回页面推荐。按用户 ID 升序排列结果。来源。
****
WITH friends (user_id, friend)
AS (VALUES
(1, 2), (1, 3), (1, 4), (2, 1), (3, 1), (3, 4), (4, 1), (4, 3)),likes (user_id, page_likes)
AS (VALUES
(1, 'A'), (1, 'B'), (1, 'C'), (2, 'A'), (3, 'B'), (3, 'C'), (4, 'B')), *-- inner join friends and page likes tables on user_id*t1 AS (
SELECT
l.user_id,
l.page_likes,
f.friend
FROM likes l
JOIN friends f
ON l.user_id = f.user_id ),*-- left join likes on this, requiring user = friend and user likes = friend likes* t2 AS (
SELECT
t1.user_id,
t1.page_likes,
t1.friend,
l.page_likes AS friend_likes
FROM t1
LEFT JOIN likes l
ON t1.friend = l.user_id
AND t1.page_likes = l.page_likes )*-- if a friend pair doesn’t share a common page like, friend likes column will be null - pull out these entries* SELECT DISTINCT
friend AS user_id,
page_likes AS recommended_page
FROM t2
WHERE friend_likes IS NULL
ORDER BY 1
7.移动和网络访问者
使用下面的两个表，返回只访问移动设备、只访问 web 以及两者都访问的用户的比例。
****
WITH mobile (user_id, page_url)
AS (VALUES
(1, 'A'), (2, 'B'), (3, 'C'), (4, 'A'), (9, 'B'), (2, 'C'), (10, 'B')),web (user_id, page_url)
AS (VALUES
(6, 'A'), (2, 'B'), (3, 'C'), (7, 'A'), (4, 'B'), (8, 'C'), (5, 'B')), *-- outer join mobile and web users on user ID*t1 AS (
SELECT DISTINCT
m.user_id AS mobile_user,
w.user_id AS web_user
FROM mobile m
FULL JOIN web w
ON m.user_id = w.user_id)*-- calculate fraction of mobile-only, web-only, and both as average of values (ones and zeros) specified in case statement condition*SELECT
AVG(CASE WHEN mobile_user IS NOT NULL AND web_user IS NULL THEN 1 ELSE 0 END) AS mobile_fraction,
AVG(CASE WHEN web_user IS NOT NULL AND mobile_user IS NULL THEN 1 ELSE 0 END) AS web_fraction,
AVG(CASE WHEN web_user IS NOT NULL AND mobile_user IS NOT NULL THEN 1 ELSE 0 END) AS both_fraction
FROM t1
8.按产品活动列出的升级率(硬)
给定以下两个表，返回在注册后的前 30 天内访问功能二(事件表中的类型:F2)并升级到高级版的用户比例(四舍五入到两位小数)。
****
WITH users (user_id, name, join_date)
AS (VALUES
(1, 'Jon', CAST('2-14-20' AS date)),
(2, 'Jane', CAST('2-14-20' AS date)),
(3, 'Jill', CAST('2-15-20' AS date)),
(4, 'Josh', CAST('2-15-20' AS date)),
(5, 'Jean', CAST('2-16-20' AS date)),
(6, 'Justin', CAST('2-17-20' AS date)),
(7, 'Jeremy', CAST('2-18-20' AS date))),events (user_id, type, access_date)
AS (VALUES
(1, 'F1', CAST('3-1-20' AS date)),
(2, 'F2', CAST('3-2-20' AS date)),
(2, 'P', CAST('3-12-20' AS date)),
(3, 'F2', CAST('3-15-20' AS date)),
(4, 'F2', CAST('3-15-20' AS date)),
(1, 'P', CAST('3-16-20' AS date)),
(3, 'P', CAST('3-22-20' AS date))), *-- get feature 2 users and their date of feature 2 access*t1 AS (
SELECT
user_id,
type,
access_date AS f2_date
FROM events
WHERE type = 'F2' ),*-- get premium users and their date of premium upgrade*t2 AS (
SELECT
user_id,
type,
access_date AS premium_date
FROM events
WHERE type = 'P' ),*-- for each feature 2 user, get time between joining and premium upgrade (or null if no upgrade) by inner joining full users table with feature 2 users on user ID and left joining premium users on user ID, then subtracting premium upgrade date from join date*t3 AS (
SELECT t2.premium_date - u.join_date AS upgrade_time
FROM users u
JOIN t1
ON u.user_id = t1.user_id
LEFT JOIN t2
ON u.user_id = t2.user_id )*-- calculate fraction of users with upgrade time less than 30 days as average of values (ones and zeros) specified in case statement condition, rounding to two decimal places*
SELECT
ROUND(AVG(CASE WHEN upgrade_time < 30 THEN 1 ELSE 0 END), 2) AS upgrade_rate
FROM t3
9.最友好的
给定下表，返回用户列表及其相应的朋友计数。按照朋友数量降序排列结果，如果出现平局，则按照用户 ID 升序排列。假设只显示唯一的友谊
(即【1，2】不会再次显示为【2，1】)。来自李码。
WITH friends (user1, user2)
AS (VALUES (1, 2), (1, 3), (1, 4), (2, 3)), *-- compile all user appearances into one column, preserving duplicate entries with UNION ALL* t1 AS (
SELECT user1 AS user_id
FROM friends
UNION ALL
SELECT user2 AS user_id
FROM friends)*-- grouping by user ID, count up all appearances of that user*SELECT
user_id,
COUNT(*) AS friend_count
FROM t1
GROUP BY 1
ORDER BY 2 DESC
10.项目汇总(硬)
“项目”表包含三列:“任务标识号”、“开始日期”和“结束日期”。表中每一行的结束日期和开始日期相差 1 天。如果任务结束日期是连续的，则它们是同一项目的一部分。项目不重叠。
编写一个查询来返回每个项目的开始和结束日期，以及完成项目所用的天数。按项目持续时间升序排序，如果出现平局，则按开始日期升序排序。来自黑客排名。
****
WITH projects (task_id, start_date, end_date)
AS (VALUES
(1, CAST('10-01-20' AS date), CAST('10-02-20' AS date)),
(2, CAST('10-02-20' AS date), CAST('10-03-20' AS date)),
(3, CAST('10-03-20' AS date), CAST('10-04-20' AS date)),
(4, CAST('10-13-20' AS date), CAST('10-14-20' AS date)),
(5, CAST('10-14-20' AS date), CAST('10-15-20' AS date)),
(6, CAST('10-28-20' AS date), CAST('10-29-20' AS date)),
(7, CAST('10-30-20' AS date), CAST('10-31-20' AS date))), *-- get start dates not present in end date column (these are “true” project start dates)* t1 AS (
SELECT start_date
FROM projects
WHERE start_date NOT IN (SELECT end_date FROM projects) ),*-- get end dates not present in start date column (these are “true” project end dates)* t2 AS (
SELECT end_date
FROM projects
WHERE end_date NOT IN (SELECT start_date FROM projects) ),*-- filter to plausible start-end pairs (start < end), then find correct end date for each start date (the minimum end date, since there are no overlapping projects)*t3 AS (
SELECT
start_date,
MIN(end_date) AS end_date
FROM t1, t2
WHERE start_date < end_date
GROUP BY 1 )SELECT
*,
end_date - start_date AS project_duration
FROM t3
ORDER BY 3, 1
11.生日出席率
给定下面的两个表，编写一个查询来返回学生的分数，四舍五入到两位小数，他们在生日那天上学
(出勤= 1)。来源。
****
WITH attendance (student_id, school_date, attendance)
AS (VALUES
(1, CAST('2020-04-03' AS date), 0),
(2, CAST('2020-04-03' AS date), 1),
(3, CAST('2020-04-03' AS date), 1),
(1, CAST('2020-04-04' AS date), 1),
(2, CAST('2020-04-04' AS date), 1),
(3, CAST('2020-04-04' AS date), 1),
(1, CAST('2020-04-05' AS date), 0),
(2, CAST('2020-04-05' AS date), 1),
(3, CAST('2020-04-05' AS date), 1),
(4, CAST('2020-04-05' AS date), 1)),students (student_id, school_id, grade_level, date_of_birth)
AS (VALUES
(1, 2, 5, CAST('2012-04-03' AS date)),
(2, 1, 4, CAST('2013-04-04' AS date)),
(3, 1, 3, CAST('2014-04-05' AS date)),
(4, 2, 4, CAST('2013-04-03' AS date))) -- join attendance and students table on student ID, and day and month of school day = day and month of birthday, taking average of attendance column values and roundingSELECT ROUND(AVG(attendance), 2) AS birthday_attendance
FROM attendance a
JOIN students s
ON a.student_id = s.student_id
AND EXTRACT(MONTH FROM school_date) = EXTRACT(MONTH FROM date_of_birth)
AND EXTRACT(DAY FROM school_date) = EXTRACT(DAY FROM date_of_birth)
12.黑客得分
给定以下两个表，编写一个查询来返回黑客 ID、姓名和总分(完成的每个挑战的最高分之和),按分数降序排序，在分数相等的情况下按黑客 ID 升序排序。不显示零分黑客的条目。来自 HackerRank 。
****
WITH hackers (hacker_id, name)
AS (VALUES
(1, 'John'),
(2, 'Jane'),
(3, 'Joe'),
(4, 'Jim')),submissions (submission_id, hacker_id, challenge_id, score)
AS (VALUES
(101, 1, 1, 10),
(102, 1, 1, 12),
(103, 2, 1, 11),
(104, 2, 1, 9),
(105, 2, 2, 13),
(106, 3, 1, 9),
(107, 3, 2, 12),
(108, 3, 2, 15),
(109, 4, 1, 0)), *-- from submissions table, get maximum score for each hacker-challenge pair*t1 AS (
SELECT
hacker_id,
challenge_id,
MAX(score) AS max_score
FROM submissions
GROUP BY 1, 2 )*-- inner join this with the hackers table, sum up all maximum scores, filter to exclude hackers with total score of zero, and order result by total score and hacker ID*SELECT
t1.hacker_id,
h.name,
SUM(t1.max_score) AS total_score
FROM t1
JOIN hackers h
ON t1.hacker_id = h.hacker_id
GROUP BY 1, 2
HAVING SUM(max_score) > 0
ORDER BY 3 DESC, 1
13.有等级无等级(硬)
编写一个查询，在不使用窗口函数的情况下对下表中的分数进行排序。如果两个分数相等，则两者应该具有相同的等级。平局之后，下面的排名应该是下一个连续的整数值。来自 LeetCode 。
WITH scores (id, score)
AS (VALUES
(1, 3.50),
(2, 3.65),
(3, 4.00),
(4, 3.85),
(5, 4.00),
(6, 3.65)) *-- self-join on inequality produces a table with one score and all scores as large as this joined to it, grouping by first id and score, and counting up all unique values of joined scores yields the equivalent of DENSE_RANK() [check join output to understand]*SELECT
s1.score,
COUNT(DISTINCT s2.score) AS score_rank
FROM scores s1
JOIN scores s2
ON s1.score <= s2.score
GROUP BY s1.id, s1.score
ORDER BY 1 DESC
14.累计薪金总额
下表保存了几名雇员的月薪信息。编写一个查询，获取一名雇员在 3 个月内(不包括最近一个月)每月的工资总额。结果应该按雇员 ID 和月份升序排序。来自 LeetCode 。
WITH employee (id, pay_month, salary)
AS (VALUES
(1, 1, 20),
(2, 1, 20),
(1, 2, 30),
(2, 2, 30),
(3, 2, 40),
(1, 3, 40),
(3, 3, 60),
(1, 4, 60),
(3, 4, 70)), *-- add column for descending month rank (latest month = 1) for each employee*t1 AS (
SELECT *,
RANK() OVER (PARTITION BY id ORDER BY pay_month DESC) AS month_rank
FROM employee )*-- filter to exclude latest month and months 5+, create cumulative salary sum using SUM() as window function, order by ID and month*SELECT
id,
pay_month,
salary,
SUM(salary) OVER (PARTITION BY id ORDER BY month_rank DESC) AS cumulative_sum
FROM t1
WHERE month_rank != 1
AND month_rank <= 4
ORDER BY 1, 2
15.团队排名
编写一个查询，在 matches 表中显示所有比赛之后，返回 teams 表中每个队的得分。计分如下:输了得零分，平了得一分，赢了得三分。结果应该包括球队名称和分数，并按分数递减排序。如果出现平局，按字母顺序排列球队名称。
****
WITH teams (team_id, team_name)
AS (VALUES
(1, 'New York'),
(2, 'Atlanta'),
(3, 'Chicago'),
(4, 'Toronto'),
(5, 'Los Angeles'),
(6, 'Seattle')),matches (match_id, host_team, guest_team, host_goals, guest_goals)
AS (VALUES
(1, 1, 2, 3, 0),
(2, 2, 3, 2, 4),
(3, 3, 4, 4, 3),
(4, 4, 5, 1, 1),
(5, 5, 6, 2, 1),
(6, 6, 1, 1, 2)), *-- add host points and guest points columns to matches table, using case-when-then to tally up points for wins, ties, and losses*t1 AS (
SELECT
*,
CASE WHEN host_goals > guest_goals THEN 3
WHEN host_goals = guest_goals THEN 1
ELSE 0 END AS host_points,
CASE WHEN host_goals < guest_goals THEN 3
WHEN host_goals = guest_goals THEN 1
ELSE 0 END AS guest_points
FROM matches )*-- join result onto teams table twice to add up for each team the points earned as host team and guest team, then order as requested*SELECT
t.team_name,
a.host_points + b.guest_points AS total_points
FROM teams t
JOIN t1 a
ON t.team_id = a.host_team
JOIN t1 b
ON t.team_id = b.guest_team
ORDER BY 2 DESC, 1
16.没有购买产品的顾客
根据下表，编写一个查询来显示购买了产品 A 和 B 但没有购买产品 C 的客户的 ID 和姓名，按客户 ID 升序排序。
****
WITH customers (id, name)
AS (VALUES
(1, 'Daniel'),
(2, 'Diana'),
(3, 'Elizabeth'),
(4, 'John')),orders (order_id, customer_id, product_name)
AS (VALUES
(1, 1, 'A'),
(2, 1, 'B'),
(3, 2, 'A'),
(4, 2, 'B'),
(5, 2, 'C'),
(6, 3, 'A'),
(7, 3, 'A'),
(8, 3, 'B'),
(9, 3, 'D')) *-- join customers and orders tables on customer ID, filtering to those who bought both products A and B, removing those who bought product C, returning ID and name columns ordered by ascending ID*SELECT DISTINCT
id,
name
FROM orders o
JOIN customers c
ON o.customer_id = c.id
WHERE customer_id IN (SELECT customer_id
FROM orders
WHERE product_name = 'A')
AND customer_id IN (SELECT customer_id
FROM orders
WHERE product_name = 'B')
AND customer_id NOT IN (SELECT customer_id
FROM orders
WHERE product_name = 'C')
ORDER BY 1
17.中纬度(硬)
编写一个查询来返回下表中每个州气象站的中值纬度，四舍五入到最接近的十分之一度。注意，SQL 中没有 MEDIAN()函数！来自黑客排名。
WITH stations (id, city, state, latitude, longitude)
AS (VALUES
(1, 'Asheville', 'North Carolina', 35.6, 82.6),
(2, 'Burlington', 'North Carolina', 36.1, 79.4),
(3, 'Chapel Hill', 'North Carolina', 35.9, 79.1),
(4, 'Davidson', 'North Carolina', 35.5, 80.8),
(5, 'Elizabeth City', 'North Carolina', 36.3, 76.3),
(6, 'Fargo', 'North Dakota', 46.9, 96.8),
(7, 'Grand Forks', 'North Dakota', 47.9, 97.0),
(8, 'Hettinger', 'North Dakota', 46.0, 102.6),
(9, 'Inkster', 'North Dakota', 48.2, 97.6)), *-- assign latitude-ordered row numbers for each state, and get total row count for each state*t1 AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY state ORDER BY latitude ASC) AS row_number_state,
count(*) OVER (PARTITION BY state) AS row_count
FROM stations )*-- filter to middle row (for odd total row number) or middle two rows (for even total row number), then get average value of those, grouping by state*SELECT
state,
AVG(latitude) AS median_latitude
FROM t1
WHERE row_number_state >= 1.0*row_count/2
AND row_number_state <= 1.0*row_count/2 + 1
GROUP BY 1
18.最大分隔城市
从问题 17 的同一个表中，编写一个查询，返回每个州中相距最远的一对城市，以及这两个城市之间的相应距离(以度为单位，四舍五入到小数点后两位)。来自黑客排名。
WITH stations (id, city, state, latitude, longitude)
AS (VALUES
(1, 'Asheville', 'North Carolina', 35.6, 82.6),
(2, 'Burlington', 'North Carolina', 36.1, 79.4),
(3, 'Chapel Hill', 'North Carolina', 35.9, 79.1),
(4, 'Davidson', 'North Carolina', 35.5, 80.8),
(5, 'Elizabeth City', 'North Carolina', 36.3, 76.3),
(6, 'Fargo', 'North Dakota', 46.9, 96.8),
(7, 'Grand Forks', 'North Dakota', 47.9, 97.0),
(8, 'Hettinger', 'North Dakota', 46.0, 102.6),
(9, 'Inkster', 'North Dakota', 48.2, 97.6)), *-- self-join on matching states and city < city (avoids identical and double-counted city pairs), pulling state, city pair, and latitude/longitude coordinates for each city*t1 AS (
SELECT
s1.state,
s1.city AS city1,
s2.city AS city2,
s1.latitude AS city1_lat,
s1.longitude AS city1_long,
s2.latitude AS city2_lat,
s2.longitude AS city2_long
FROM stations s1
JOIN stations s2
ON s1.state = s2.state
AND s1.city < s2.city ),*-- add a column displaying rounded Euclidean distance* t2 AS (
SELECT *,
ROUND(( (city1_lat - city2_lat)^2 + (city1_long - city2_long)^2 ) ^ 0.5, 2) AS distance
FROM t1 ),*-- rank each city pair by descending distance for each state*t3 AS (
SELECT *, RANK() OVER (PARTITION BY state ORDER BY distance DESC) AS dist_rank
FROM t2 )*-- return the city pair with maximium separation*SELECT
state,
city1,
city2,
distance
FROM t3
WHERE dist_rank = 1
19.周期
编写一个查询来返回每个月的平均周期时间。周期时间是一个用户加入和他们的被邀请者加入之间经过的时间。未经邀请而加入的用户在“邀请者”一栏中有一个零。
WITH users (user_id, join_date, invited_by)
AS (VALUES
(1, CAST('01-01-20' AS date), 0),
(2, CAST('01-10-20' AS date), 1),
(3, CAST('02-05-20' AS date), 2),
(4, CAST('02-12-20' AS date), 3),
(5, CAST('02-25-20' AS date), 2),
(6, CAST('03-01-20' AS date), 0),
(7, CAST('03-01-20' AS date), 4),
(8, CAST('03-04-20' AS date), 7)), *-- self-join on invited by = user ID, extract join month from inviter join date, and calculate cycle time as difference between join dates of inviter and invitee*t1 AS (
SELECT
CAST(EXTRACT(MONTH FROM u2.join_date) AS int) AS month,
u1.join_date - u2.join_date AS cycle_time
FROM users u1
JOIN users u2
ON u1.invited_by = u2.user_id )*-- group by join month, take average of cycle times within each month*SELECT
month,
AVG(cycle_time) AS cycle_time_month_avg
FROM t1
GROUP BY 1
ORDER BY 1
20.连续三次
出席表记录了每天举行活动时人群中的人数。编写一个查询来返回一个显示高出勤率时段的日期和访问者计数的表，高出勤率时段定义为三个连续的条目(不一定是连续的日期),访问者超过 100 人。来自 LeetCode 。
WITH attendance (event_date, visitors)
AS (VALUES
(CAST('01-01-20' AS date), 10),
(CAST('01-04-20' AS date), 109),
(CAST('01-05-20' AS date), 150),
(CAST('01-06-20' AS date), 99),
(CAST('01-07-20' AS date), 145),
(CAST('01-08-20' AS date), 1455),
(CAST('01-11-20' AS date), 199),
(CAST('01-12-20' AS date), 188)), *-- add row numbers to identify consecutive entries, since date column has some gaps*t1 AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY event_date) AS day_num
FROM attendance ),*-- filter this to exclude days with > 100 visitors*t2 AS (
SELECT *
FROM t1
WHERE visitors > 100 ),*-- self-join (inner) twice on offset = 1 day and offset = 2 days*t3 AS (
SELECT
a.day_num AS day1,
b.day_num AS day2,
c.day_num AS day3
FROM t2 a
JOIN t2 b
ON a.day_num = b.day_num - 1
JOIN t2 c
ON a.day_num = c.day_num - 2 )*-- pull date and visitor count for consecutive days surfaced in previous table*SELECT
event_date,
visitors
FROM t1
WHERE day_num IN (SELECT day1 FROM t3)
OR day_num IN (SELECT day2 FROM t3)
OR day_num IN (SELECT day3 FROM t3)
21.通常一起购买
使用下面两个表，编写一个查询来返回最常一起购买的前三对产品的名称和购买频率。两种产品的名称应该出现在一列中。来源。
****
WITH orders (order_id, customer_id, product_id)
AS (VALUES
(1, 1, 1),
(1, 1, 2),
(1, 1, 3),
(2, 2, 1),
(2, 2, 2),
(2, 2, 4),
(3, 1, 5)),products (id, name)
AS (VALUES
(1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D'),
(5, 'E')), *-- get unique product pairs from same order by self-joining orders table on order ID and product ID < product ID (avoids identical and double-counted product pairs)*t1 AS (
SELECT
o1.product_id AS prod_1,
o2.product_id AS prod_2
FROM orders o1
JOIN orders o2
ON o1.order_id = o2.order_id
AND o1.product_id < o2.product_id ),*-- join products table onto this to get product names, concatenate to get product pairs in one column*t2 AS (
SELECT CONCAT(p1.name, ' ', p2.name) AS product_pair
FROM t1
JOIN products p1
ON t1.prod_1 = p1.id
JOIN products p2
ON t1.prod_2 = p2.id )*-- grouping by product pair, return top 3 entries sorted by purchase frequency*SELECT *,
COUNT(*) AS purchase_freq
FROM t2
GROUP BY 1
ORDER BY 2 DESC
LIMIT 3
22.平均治疗效果(硬)
从总结研究结果的下表中，计算平均治疗效果以及 95%置信区间的上限和下限。将这些数字四舍五入到小数点后 3 位。
WITH study (participant_id, assignment, outcome)
AS (VALUES
(1, 0, 0),
(2, 1, 1),
(3, 0, 1),
(4, 1, 0),
(5, 0, 1),
(6, 1, 1),
(7, 0, 0),
(8, 1, 1),
(9, 1, 1)), -- get average outcomes, standard deviations, and group sizes for control and treatment groupscontrol AS (
SELECT
AVG(outcome) AS avg_outcome,
STDDEV(outcome) AS std_dev,
COUNT(*) AS group_size
FROM study
WHERE assignment = 0 ),treatment AS (
SELECT
AVG(outcome) AS avg_outcome,
STDDEV(outcome) AS std_dev,
COUNT(*) AS group_size
FROM study
WHERE assignment = 1 ),-- get average treatment effect sizeeffect_size AS (
SELECT t.avg_outcome - c.avg_outcome AS effect_size
FROM control c, treatment t ),-- construct 95% confidence interval using z* = 1.96 and magnitude of individual standard errors [ std dev / sqrt(sample size) ]conf_interval AS (
SELECT 1.96 * (t.std_dev^2 / t.group_size
+ c.std_dev^2 / c.group_size)^0.5 AS conf_int
FROM treatment t, control c )SELECT round(es.effect_size, 3) AS point_estimate,
round(es.effect_size - ci.conf_int, 3) AS lower_bound,
round(es.effect_size + ci.conf_int, 3) AS upper_bound
FROM effect_size es, conf_interval ci
23.滚动工资总额
下表显示了给定年份中前九个月员工的月薪。在此基础上，编写一个查询来返回一个表，该表按时间顺序显示上半年每个月雇员当月和接下来两个月的工资总额。
****
WITH salaries (month, salary)
AS (VALUES
(1, 2000),
(2, 3000),
(3, 5000),
(4, 4000),
(5, 2000),
(6, 1000),
(7, 2000),
(8, 4000),
(9, 5000)) -- self-join to match month n with months n, n+1, and n+2, then sum salary across those months, filter to first half of year, and sortSELECT
s1.month,
SUM(s2.salary) AS salary_3mos
FROM salaries s1
JOIN salaries s2
ON s1.month <= s2.month
AND s1.month > s2.month - 3
GROUP BY 1
HAVING s1.month < 7
ORDER BY 1
24.出租车取消率
从出租车服务的给定 trips 和 users 表中，编写一个查询，返回 10 月份前两天的取消率，四舍五入到两位小数，返回不涉及被禁乘客或司机的行程。来自 LeetCode 。
****
WITH trips (trip_id, rider_id, driver_id, status, request_date)
AS (VALUES
(1, 1, 10, 'completed', CAST('2020-10-01' AS date)),
(2, 2, 11, 'cancelled_by_driver', CAST('2020-10-01' AS date)),
(3, 3, 12, 'completed', CAST('2020-10-01' AS date)),
(4, 4, 10, 'cancelled_by_rider', CAST('2020-10-02' AS date)),
(5, 1, 11, 'completed', CAST('2020-10-02' AS date)),
(6, 2, 12, 'completed', CAST('2020-10-02' AS date)),
(7, 3, 11, 'completed', CAST('2020-10-03' AS date))),users (user_id, banned, type)
AS (VALUES
(1, 'no', 'rider'),
(2, 'yes', 'rider'),
(3, 'no', 'rider'),
(4, 'no', 'rider'),
(10, 'no', 'driver'),
(11, 'no', 'driver'),
(12, 'no', 'driver')) -- filter trips table to exclude banned riders and drivers, then calculate cancellation rate as 1 - fraction of trips completed, filtering to first two days of the month
SELECT
request_date,
1 - AVG(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS cancel_rate
FROM trips
WHERE rider_id NOT IN (SELECT user_id
FROM users
WHERE banned = 'yes' )
AND driver_id NOT IN (SELECT user_id
FROM users
WHERE banned = 'yes' )
GROUP BY 1
HAVING EXTRACT(DAY FROM request_date) <= 2
25.保留曲线(硬)
从下面的用户活动表中，编写一个查询来返回在加入后给定天数内保留(显示一些活动)的用户比例。按照惯例，用户在其加入日(第 0 天)被认为是活跃的。
WITH users (user_id, action_date, action)
AS (VALUES
(1, CAST('01-01-20' AS date), 'Join'),
(1, CAST('01-02-20' AS date), 'Access'),
(2, CAST('01-02-20' AS date), 'Join'),
(3, CAST('01-02-20' AS date), 'Join'),
(1, CAST('01-03-20' AS date), 'Access'),
(3, CAST('01-03-20' AS date), 'Access'),
(1, CAST('01-04-20' AS date), 'Access')), *-- get join dates for each user*join_dates AS (
SELECT
user_id,
action_date AS join_date
FROM users
WHERE action = 'Join' ),*-- create vector containing all dates in date range*date_vector AS (
SELECT CAST(GENERATE_SERIES(MIN(action_date), MAX(action_date),
'1 day'::interval) AS date) AS dates
FROM users ),*-- cross join to get all possible user-date combinations*all_users_dates AS (
SELECT DISTINCT
user_id,
d.dates
FROM users
CROSS JOIN date_vector d ),*-- left join users table onto all user-date combinations on matching user ID and date (null on days where user didn't engage), join onto this each user's signup date, exclude user-date combinations falling before user signup*t1 AS (
SELECT
a.dates - c.join_date AS day_no,
b.user_id
FROM all_users_dates a
LEFT JOIN users b
ON a.user_id = b.user_id
AND a.dates = b.action_date
JOIN join_dates c
ON a.user_id = c.user_id
WHERE a.dates - c.join_date >= 0 )*-- grouping by days since signup, count (non-null) user IDs as active users, total users, and the quotient as retention rate*SELECT
day_no,
COUNT(*) AS n_total,
COUNT(DISTINCT user_id) AS n_active,
ROUND(1.0*COUNT(DISTINCT user_id)/COUNT(*), 2) AS retention
FROM t1
GROUP BY 1
附录
一个常见问题:如果您在使用 CTE 时看到语法错误，请检查 cte 之间是否有逗号，并且在最后一个 CTE 后面没有逗号。
WITH input_table (column_1, column_2)
AS (VALUES
(1, 'A'), (2, 'B')), -- comma between CTEst1 AS (
SELECT *
FROM input_table
WHERE column_2 = 'A') -- no comma after last CTESELECT *
FROM t1
感谢本·拉卡尔和叶敏婷。
推特分析白俄罗斯当前的政治局势
在白俄罗斯没有办法进行独立的社会学研究，所以在分析政治偏好时，Twitter 和 Python 就派上了用场。
语境
亚历山大·卢卡申科担任白俄罗斯总统已经超过 26 年。他因侵犯人权、绑架政治对手并使其失踪以及滥用权力而被禁止进入欧盟和美国。
今年八月，当他面临“改选”时，社会动荡和抗议已经激增，一股前所未见的社会团结浪潮也随之而来。
反对派领导人和和平抗议者现在面临前所未有的镇压。活动家、政治家和记者在监狱里被绑架和折磨。
禁止进行和发表独立的社会学研究。
一些互联网调查显示，卢卡申科拥有不超过 3%的白俄罗斯民众的支持，而他公开声称这一数字约为 72-78%。我决定使用 Python 和 NLP 库来检查这些声明。
本次简短研究的目标:
–衡量 Twitter 用户对总统候选人的真实忠诚度。
–确定主要作者、标签和主题。
跟踪国际社会对当前事件的反应。
工具包:Python，Pandas，Numpy，Matplotlib，Seaborn，SpaCy，Scikit-learn，Vader，LDA。
检索 Twitter 数据
从 6 月初开始，我搜集了超过 24 万条与白俄罗斯和总统候选人相关的推文。我获得了 49 种不同语言的推文，平均每天有 3 条 4K 推文。
由于官方 Twitter API 不允许获取超过 1 周的推文，所以我使用 GetOldTweets3 来解析历史数据(我强烈建议查看@noahberhe 的代码，它修复了许多错误)。如果有兴趣，你可以在这里下载一个原始数据集。
下面的代码用俄语(红色)、英语(蓝色)和其他语言(黄色)创建了从 6 月 1 日到 7 月 26 日的三个每日推文图。
#Importing some basic librariesimport pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
from datetime import date, timedelta, datetime# Converting the created_at column to np.datetime objectdf['date'] = pd.to_datetime(df['date'])#Creating separate dataframes in Russian, English and other languagesdf_ru =df[df.language == 'ru']
df_en =df[df.language == 'en']
df_else =df[(df['language'] != 'ru') & (df['language'] != 'en')]#Plotting tweets dynamics in different languagesdf_ru.resample('D', on='date').text.count().plot(color='r',figsize=(16,6), label = 'russian')
df_en.resample('D', on='date').text.count().plot(color='b',figsize=(16,6), label = 'english')
df_else.resample('D', on='date').text.count().plot(color='y',figsize=(16,6),label = 'other languages')
plt.ylabel('number of daily tweets')
plt.legend()
最受欢迎的推文
下面是打印最受欢迎的 tweet 的代码示例。
fav_max = np.max(df_ru['favorites'])
fav = df_ru[df_ru.favorites == fav_max].index[0]print("Tweet with most likes: \n{}".format(df_ru['text'][fav]))print("Number of likes: {}".format(fav_max))
print("Username: {}".format(df_ru['username'][fav]))
print ("Translation: {}".format(translator.translate(df_ru['text'][fav]).text))
在指定时间段内最受欢迎的俄语推文:
下面翻译
简要介绍一下今天白俄罗斯的情况:
候选人 Babariko 收集了 425，000 个签名，但他们在去选举委员会的路上拘留了他和他的儿子，不允许律师进入，房子被搜查。
候选人 Tikhanovskaya 被威胁要绑架她的孩子。警方拒绝调查。"
最受欢迎的英语推文:
用俄语发推文的前 25 名用户
下面是绘制前 25 名最活跃用户的直方图代码示例。
users_count = df_ru['username'].value_counts()
users_count = users_count[:25,]
plt.figure(figsize=(16,8))
sns.barplot(users_count.index, users_count.values, alpha=0.8)
plt.title('Top 25 users')
plt.ylabel('Number of Occurrences', fontsize=12)
plt.xlabel('Users', fontsize=12)
plt.xticks(rotation=65, horizontalalignment='right')
plt.show()
用英语发推文的前 25 名用户
热门标签
#白俄罗斯，#白俄罗斯，#明斯克，#特比，#布雷斯特比，#新闻，#选举，#卢卡申科，#特比邦，#乔维德 19，#冠状病毒，#乔维德，#乔维德 19，#fcdm1927，#人权，#人权暴力，#OSCE，#明斯克集团，# 2020 年幸存者，#军事
衡量对总统候选人的忠诚度
然后，我使用库 SpaCy 分离出与卢卡申科及其主要对手 Babariko、Tsepkalo 和 Tikhanovskaya 有关的最近推文(2020 年 7 月 18 日至 26 日)。为了使用情感分析和实体提取库，我必须将推文翻译成英语。我用了谷歌翻译 API。下面是一个简单的方法:
#Importing librariesimport json
from apiclient.discovery import build
import os
import time
pd.options.mode.chained_assignment = None
import requests
import socket
from googleapiclient import discovery#Creating GoogleAPI function def translation_googleAPI(query, target_language):socket.setdefaulttimeout(600) # set timeout to 10 minutes
service = discovery.build('translate','v2',developerKey='YOURKEY')
collection = service.translations()
request = collection.list(q=query, target=target_language)
response = request.execute()
response_json = json.dumps(response)
translation = ((response['translations'][0])['translatedText'])
print (translation)
print('....')
return translation#Separating tweets related to a particular candidatedf_lukashenko = df[df.topical_persons == 'lukashenko']#Lambda function to create a new column with translationdf_lukashenko['translated'] = df_lukashenko['text'].apply(lambda x: translation_googleAPI(x,'en'))
让我们探索一下每个候选人的数据集。
卢卡申科
8023 条推文
在此期间，卢卡申科在推特上被提及的次数比任何其他候选人都多至少 8 次。7 月 20 日的高峰与宣布候选人登记有关。
下面是使用 VADER 识别推文情绪的代码:
#Let's import the library and create a functionfrom vaderSentiment.vaderSentiment import SentimentIntensityAnalyzerdef vader_scorer(df):
analyzer = SentimentIntensityAnalyzer()
vader_scores = df.loc[:,'translated'].map(analyzer.polarity_scores)dvec = DictVectorizer()
vader_scores = dvec.fit_transform(vader_scores)
vader_scores = pd.DataFrame(vader_scores.toarray(),columns=dvec.feature_names_)
return vader_scores#Adding scores to our datasetvader_scores = vader_scorer(df_lukashenko)
df_lukashenko = pd.concat([df_lukashenko,vader_scores], axis=1)
df_lukashenko = df_lukashenko.drop_duplicates(subset='tweet_id', ignore_index=True)# Creating a new column called 'sentiment_class'
sentimentclass_list = []for i in range(0, len(df_lukashenko)):
# current 'compound' score:
curr_compound = df_lukashenko.iloc[i,:]['compound']
if (curr_compound <= 1.0 and curr_compound >= 0.55):
sentimentclass_list.append(5)
elif (curr_compound < 0.55 and curr_compound >= 0.10):
sentimentclass_list.append(4)
elif (curr_compound < 0.10 and curr_compound > -0.10):
sentimentclass_list.append(3)
elif (curr_compound <= -0.10 and curr_compound > -0.55):
sentimentclass_list.append(2)
elif (curr_compound <= -0.55 and curr_compound >= -1.00):
sentimentclass_list.append(1)# Adding the new column 'sentiment_class' to the dataframedf_lukashenko['sentiment_class'] = sentimentclass_list#Plotting the distribution of sentiment_classimport seaborn as sns
plt.figure(figsize = (6,4))
sns.set_palette('coolwarm')
sns.countplot(df_luk['sentiment_class'])
plt.title('Countplot of sentiment class')
plt.xlabel('Sentiment class: from negative to positive')
plt.ylabel('No. of tweets')
plt.show()
提到卢卡申科的推特的情绪分布均匀。如果我们看看最负面的推文，它们通常是在谴责政权的暴力。如果我们看看积极的推文，它主要与团结白俄罗斯人民反对独裁主义有关。
下面是一个负面推文的例子:
下面翻译
“卢卡申科不再隐藏他的法西斯本质、仇恨和对人民的蔑视，正在为他的终身权力与人民进行战争。失去权力后，法西斯分子卢卡申科将被定罪。法西斯分子卢卡申科因谋杀、恐怖主义和贪污而受到的唯一惩罚是被枪毙”。
下面是一条被 VADER 定性为正面的推文:
提到卢卡申科的最受欢迎的推文:
下面翻译
“支持无畏的白俄罗斯妇女斯韦特兰娜·蒂哈诺夫斯卡娅的最大规模集会现在正在戈梅尔举行，她向残酷和卑鄙的独裁者卢卡申科发出了挑战。人山人海——灯笼的海洋。他们的光芒照亮了我们通往自由的道路！白俄罗斯万岁！”
以下是卢卡申科数据集的代码和单词 cloud 月 18-16 日，翻译成英文):
#Transforming translated tweets to the plain textdf_lukashenko.translated.to_csv('lukashenko_tweets.txt', header=None, index=None, sep=' ', mode='a')# Importing packagesimport matplotlib.pyplot as plt
%matplotlib inline# Defining a function to plot word clouddef plot_cloud(wordcloud):
# Set figure size
plt.figure(figsize=(40, 30))
# Display image
plt.imshow(wordcloud)
# No axis details
plt.axis("off");# Extracting the plain text contenttext = open("lukashenko_tweets.txt", 'r', encoding='utf-8').read()
# Clean text
text = re.sub(r'==.*?==+', '', text)
text = text.replace('\n', '')# Importing packagefrom wordcloud import WordCloud, STOPWORDS
stop_words = ["https","html","youtu", "via", "d0","d1", "will", "quot","amp","alexander","ru", "lukashenka", "lukashenko", "belarus", "youtube", "video"] + list(STOPWORDS)# Generating word cloudwordcloud = WordCloud(width = 3000, height = 2000, random_state=1, background_color='white', colormap='Set2', collocations=False, stopwords = stop_words).generate(text)# Plotplot_cloud(wordcloud)
采普卡洛
1024 条推文
7 月 24 日的高峰与 Valeriy Tsepkalo 在收到内部消息后带着两个小孩逃离该国有关，他将被逮捕，孩子们将被送往孤儿院。
Valeriy Tsepkalo 离开后，他的妻子 Veronika Tsepkalo 接管了政治活动，与其他反对派领导人共同努力。因此，当前数据集的情感主要与 Veronika 相关。
VADER 将带有负面情绪的推文归类为:
下面翻译
“威胁不在候选人身上。威胁在于人民本身。厌倦了生活在屈辱和恐惧中的人。在前往第一次集会的路上，Babariko、Tsepkalo 和 Tikhanovskaya 联合总部的代表讲述了他们计划如何在选举中进一步斗争”。
正面推文的例子:
下面翻译
“三个女孩完美地互补。科列斯尼科娃——最善良的动物，带着富有感染力的微笑和真正的希望之光。tikhanovskaya——激发胜利信念的意志力。tsepkalo——最聪明和理性思考的领导者”。
提到 Tsepkalo 的最受欢迎的推文:
下面翻译
“继 Tikhanovskaya 之后，Tsepkalo 也被迫带着孩子出国。仿佛来自战区。多么令人沮丧。(他们还试图把孩子从被投进监狱的 2010 年候选人 Sannikov 身边带走)”。
Tsepkalo 数据集的词云:
巴巴里科
684 条推文
从 18 年 7 月到 26 年 7 月，与巴巴里科相关的推文
最近，有两个明显的高峰:
7 月 19 日，中央选举委员会表示，巴巴里科没有登记为总统候选人。7 月 24 日，一项新的指控(行贿)被曝光。
提到 Babariko 的推文的情绪主要是负面的，尽管这种负面情绪来自于人们对不公平的指控和拘留感到愤怒的事实。
下面是一条带有负面情绪的推文:
下面翻译
“卢卡申科说，他被捕的竞争对手巴巴里科“失声痛哭”，“开始就 Belgazprombank 案作证”；我可以想象那个人在那里是如何被折磨的”。
这是这段时间最正面的推文(关于 Babariko 总部负责人 Maria Kolesnikova):
下面翻译
“聪明、美丽、有才华——不可思议的玛丽亚·科列斯尼科娃！！！维克多有着惊人的团结最优秀者的能力，巴巴里科总部的团队就是最好的证明。玛丽亚，我个人非常感谢你为我们国家的美好未来所做的一切！”
这是提到巴巴里科的最受欢迎的推文:
下面翻译
“维克多和爱德华·巴巴里科被捕已经一个月了。我们非常想念你！我们怀念维克多·德米特里耶维奇(Viktor Dmitrievich)愉快的声音，他向来到办公室的每一个人打招呼。通过他在会议上的笑话和亲密的溪流，通过他在会议室里的咖啡味道——一切”。
下面是巴巴里科的词云:
季哈诺夫斯卡娅
1015 条推文
联合总部(从左至右):韦罗妮卡·采普卡罗、斯维特拉娜·季哈诺夫斯卡娅和玛丽亚·科列斯尼科娃(维克多·巴巴里科总部负责人)。图:卡纳普列夫+莱迪克
Svetlana Tikhanovskaya 是一位 37 岁的家庭主妇，有两个小孩，当她的丈夫 Sergey Tikhanovskiy 在授权的 piquet 期间受到挑衅而被监禁时，她决定注册成为总统候选人。Tikhanovskaya 是仍留在白俄罗斯的大规模支持的候选人，没有坐牢。
采普卡罗、巴巴里科和季哈诺夫斯卡娅总部联合起来反对卢卡申科。
Tikhanovskaya 的受欢迎程度在她注册成为候选人后急剧上升，并一直保持稳定。
联合总部的支持率很高。白俄罗斯在其现代史上从未见过对反对派候选人的如此支持。不管负面背景如何，推文的整体情绪相当积极。
带有负面情绪的推文示例:
下面翻译
“总统候选人斯维特拉娜·季哈诺夫斯卡娅因受到威胁带着她的孩子离开了这个国家。6 月中旬，Tikhanovskaya 说，一个不知名的人威胁说，如果她不停止总统竞选，就把她送进监狱，并带走她的孩子”。
正面推文示例:
最受欢迎的推文来自账户 sadmikalai ，讽刺地代表卢卡申科 15 岁的儿子发推文:
下面翻译
“爸爸非常喜欢季哈诺夫斯卡娅所说的话，他甚至决定为她投票”。
下面是 Tikhanovskaya 的词云:
国际社会对当前事件的反应
使用 SpaCy，我分离了提到美国和欧盟的推文(从 6 月 1 日到 7 月 26 日的推文)。显然有更多与美国相关的推文，尽管其中大多数与政治不太相关。我没有注意到高级官员对白俄罗斯局势表达了激进的立场。虽然欧盟的推特更少，但立场更具决定性。
例如，转发量最大的帖子是唐纳德·图斯克强烈谴责白俄罗斯当局的行为。
我注意到美国相关数据集中有一个不寻常的话题(Elon Musk — Space — Space Suite)。我决定更深入地研究它与白俄罗斯和白俄罗斯选举有什么关系。我们开始吧:
简要结论
- 卢卡申科在推特上被提及的次数比最接近的竞争对手至少多 8 次。负面情绪是激进的(呼吁暴力)，而大多数正面推文是讽刺性的或假阳性的。
- Tsepkalo 和 Babariko 数据集中的负面情绪主要与被捕和受到威胁有关。
- Svetlana Tikhanovskaya 比任何其他候选人获得了更多积极的推文。她的人气增长极快，并保持稳定。
- 推特上没有国际社会对白俄罗斯局势的决定性反应。唐纳德·图斯克是为数不多的积极表达担忧的外国政治家之一。
- 白俄罗斯人正在大量写信给埃隆·马斯克，要求尽快将卢卡申科送入太空，并提供卫星互联网 Starlink。人们有理由担心互联网会在选举日被封锁。
后续步骤
一系列受控行为实验表明，当人们对某个问题进行道德化时，他们更有可能支持针对该问题的暴力抗议( Nature ，2016)。
目前，我正在研究一个应用程序，通过识别推文中道德和伦理的百分比，并将其与 Twitter 位置数据进行匹配，来实时预测暴力抗议。如果你愿意合作，请随时联系我。
南方公园第十六季第十三集
更多代码在 Github 上。
这里你可以下载数据集。
请继续关注并实时跟踪这场革命。
领英脸书 推特
Twitter 分析:“WeRateDogs”
一个数据争论和分析博客
来源:WeRateDogs 推特
一个好的数据管理者知道如何集成来自多个数据源的信息，解决常见的转换问题，以及解决数据清理和质量问题。
来自 Udacity 的数据分析师 Nanodegree 项目向我介绍了当今业界正在使用的各种数据争论方法。在其中一个争论不休的项目中，我有机会经历了整个数据分析过程——收集数据、清理数据、分析数据，最后将数据可视化。
该数据集是 Twitter 用户 @dog_rates 的推文档案，也称为 WeRateDogs。WeRateDogs 是一个推特账户，用幽默的评论给人们的狗打分。
来源:WeRateDogs 推特
这个项目的大部分焦点都集中在数据争论上。那么到底什么是数据角力呢？
数据争论指的是将可用的原始数据清理、重组和丰富成更有用的格式的过程。
我在这个项目中使用了各种 python 库，下面是我开始使用的。
**import** **numpy** **as** **np**
**import** **pandas** **as** **pd**
**import** **matplotlib.pyplot** **as** **plt**
%**matplotlib** inline
收集数据:
这个项目的数据有三种不同的格式:
**1。Twitter 存档文件——WeRateDogs:**WeRateDogs 下载了他们的 Twitter 存档文件，并专门分享给本项目使用。这个档案包含基本的 tweet 数据(tweet ID、时间戳、文本等。)他们在 2017 年 8 月 1 日发布的所有 5000 多条推文。
这是由 Udacity 以编程方式提取的，并作为 csv 文件提供给用户使用。
pd.read_csv('twitter-archive-enhanced.csv')
**2。图像预测文件:**tweet 图像预测，即根据神经网络在每个 tweet 中出现什么品种的狗，存储在该文件中。它以 tsv 格式托管在 Udacity 的服务器上，必须使用 Url 以编程方式下载。
Python 的 request 库用于从 web 上收集这些数据。 请求 是 python 中一个多功能的 HTTP 库，具有各种应用。它的应用之一是使用文件 URL 从 web 下载或打开文件。
**3。Twitter API — JSON 文件:**通过使用 WeRateDogs Twitter 档案中的 tweet IDs，我使用 Python 的 tweepy 库查询了 Twitter API 中每条 tweet 的 JSON 数据。
Tweepy 是一个开源的 Python 包，给你一个非常方便的方法来用 Python 访问 Twitter API。你可以在 twitter 和使用 Python 访问 Twitter API 的 中找到关于 设置应用的更多细节。
此外，每条 tweet 的全部 JSON 数据都存储在一个名为 tweet_json.txt 的文件中。
每个 tweet 的 JSON 数据都被写到自己的行中，然后。txt 文件被逐行读入熊猫数据帧。
评估数据
收集数据后，这三个表被保存，并以可视化和编程的方式进行评估。在这两次评估中，我在所有三个数据框架中寻找不干净的数据，即整洁性和质量问题。
质量: *低质量数据通常被称为脏数据。脏数据的内容有问题。*数据质量维度是完整性、有效性、准确性和一致性。
整洁: *凌乱的数据通常被称为“凌乱”的数据。杂乱的数据在结构上有问题。*整理数据是哪里:
1。每个变量形成一列。
2。每个观察值形成一行。
3。每种类型的观测单位形成一张表。
视觉评估为我提供了一些问题，如三个数据集中的非描述性列标题和重复列。
程序性评估实际上给了我大部分质量问题，例如三个数据集中出现的不正确的数据类型和重复数据。然后，我在 python 笔记本中记录了我的评估要点。
清理数据
清洁意味着根据我们所做的评估来提高质量和整洁度。
提高质量并不意味着改变数据，让它说一些不同的东西——这是数据欺诈。质量改进是指不准确时纠正，不相关时删除，缺失时更换。
类似地，改善整洁意味着转换数据集，使得每个变量是一列，每个观察是一行，每种类型的观察单元是一个表。
我遵循程序化的数据清理流程，即 定义、编码和测试。 我将我在评估步骤中的观察转换成定义的问题，将这些定义翻译成复杂的代码来修复这些问题，然后测试三个数据集以确保操作有效。
存储数据
清理完数据后，我发现不需要三个数据集。所有的数据都可以很容易地转换成一个文件。因此，我将三个数据帧连接在一个公共属性 twitter_id 上，以创建 twitter_archive_master.csv。
分析数据
使用这些新清理的 WeRateDogs Twitter 数据，创建了有趣和可信的分析和可视化，以传达调查结果。
WeRateDogs 是一个 Twitter 账户，用幽默的评论给人们的狗打分。这些评级的分母几乎都是 10。但是分子数几乎总是大于 10。11/10、12/10、13/10 等。为什么？因为“他们是好狗布伦特”。WeRateDogs 认为几乎所有的狗都应该得到 10 分或者更多。每条推文的转发次数和收藏(即“喜欢”)次数是另一个有趣的参数。
来源:推特
那么，如何处理这些评级呢？对于狗，最常见的问题是哪只狗最受欢迎？我们能得到转发、收藏和收视率之间的任何关系吗？我已经分析了这些数据来得到这些问题的答案。
matplotlib 和 seaborn 等 Python 库为我的分析提供了一些很酷的可视化工具。
最受欢迎的狗品种
根据第一次预测分析我的数据，我得到了十大最受欢迎的狗品种。
作者图片
金毛猎犬是最受欢迎的狗。这些狗有最多的推文，这使它们在我们最受欢迎的名单上。
我们的数据中有狗的 jpg_url。Pythons 的 io 模块用于管理与文件相关的输入输出操作。我使用 Pillow，友好的 PIL fork-Python 图像库，得到了我们最受欢迎的狗金毛猎犬的图像，它在统计中名列榜首。
下面是结果。
作者图片
转发、收藏和评级
令人惊讶的是，许多推文被转发。一些推文有近 40000 次转发。
我试图通过关联图(python 中最酷的图之一)来找出转发、评级和收藏之间是否有任何关系。我用 seaborn 库生成了一个热图。
作者图片
转发量和收视率之间没有明确的关系。但是收藏和转发之间有很强的正相关性，即转发次数和收藏次数之间的线性关系。下面的散点图也证实了这一点。
作者图片
因此，这里的见解是转发通常是最受欢迎的，但我们不能说所有的转发都是最受欢迎的，反之亦然。
在我的分析中，我发现给定推文的收藏数和转发数之间有很强的线性关系。金毛寻回犬是最受欢迎的犬种，它与拉布拉多寻回犬、彭布罗克犬、吉娃娃犬和哈巴狗一起构成了数据中最常见的五个犬种。
结论
可以分析该数据集中的许多其他参数，以获得不同的洞察力和可视化效果。因为这个项目的主要焦点是数据争论，所以在这个部分花了更多的时间。
为了 Udacity ，我不得不提交两份报告以及Python 笔记本。第一份报告是内部文档 the wrangle_report ，它简要描述了争论的结果；第二份报告是外部文档 the act_report ，它传达见解并显示从争论的数据中产生的可视化结果。
数据鼓吹者非常了解他们的数据，并且总是在寻找丰富数据的方法。我已经用令人惊奇的 Python 库做了同样的事情。这个项目是我最有趣的数据体验之一，以至于我决定写我的第一篇博客。您可以在我的 Github 上查看项目和我的报告。
感谢您的阅读！
使用 R 进行 Twitter 数据获取和预处理的深度教程
在 Unsplash 上由 Carlos Muza 拍摄的照片
实践教程
如何通过数据洞察印尼大选
作为微博和社交网络服务的 Twitter
Twitter 是一种社交媒体，用于传播特定主题的信息或观点，可以是社会中发生的社会或政治现象。Twitter 用户可以使用 280 个字符、照片、声音、视频、链接等来发布他们的观点。
在我看来，Twitter 最有趣的元素之一是一个 趋势话题 。它指的是在特定领域中经常讨论的词或话题，在同一日期和时间内，它的音量最高。
这一分析是在印度尼西亚公共选举期间进行的，当时总统候选人的竞选团队通过社交媒体进行了大量的政治活动，其中之一就是 Twitter。这就像是热门话题的在线战争。每个团队都宣传自己的总统候选人，甚至使用了黑人竞选。作为一名数据科学家，这就像是可以分析的数据宝藏，所以我们通过分析找出一种模式。但是，我们如何获得 Twitter 数据呢？下一步是什么？这将在本故事中涉及。尽情享受吧！
如何使用 API 从 Twitter 获取数据
好吧，一般来说，Twitter API 和网络抓取可以轻松获取数据。Twitter API 可以从我们的 Twitter 帐户中提取数据——特定日期和时间的推文、我们帐户的关注者数量等。
API 代表应用程序编程接口是连接两个应用程序之间数据的工具，正式格式为 JSON。
这个端点允许我们发出一个特定的请求，例如，某个时间段的 tweets，并将其返回给我们的应用程序。在这种情况下，R 的行为类似于应用程序。为了开始使用 Twitter API，我们需要一个 Twitter 帐户，它将使我们能够访问 API 密钥(消费者密钥)。所以获取 Twitter API 有几个步骤。
- 创建一个 Twitter 帐户。请前往 Twitter 平台 注册一个免费账户。它允许我们访问开发者网站。
用于登录和注册的 Twitter 页面(图片由作者提供)
- 前往 Twitter 开发者网站，创建一个新的应用程序。开发商所在地是 这里是
未创建任何应用程序的开发人员网站(图片由作者提供)
- 您需要说明使用 Twitter 开发者工具的一个主要原因。是为了个人项目、教育、研究还是其他？你所需要的是回答和填写某些问题，并同意条款和政策
- 该过程可能需要一些时间。我们应该能够在*“Keys and Access Tokens”*选项卡下找到所有 Twitter API 密钥细节的所有内容
Twitter API 已经在开发者网站上生成(图片由作者提供)
如何使用 R 连接 Twitter API
现在，我们将通过 API 连接我们的应用程序®和 Twitter。在我们深入讨论这个之前，请确保我们已经在 r 中安装了**twitterR**和**tm**库。
# Install libraries
install.packages('twitteR') # Library for Twitter API
install.packages('tm') # Library for text mining
需求完成后，我们开始使用 API 将应用程序与 Twitter 连接起来。
# Import the library
library(twitteR)
library(tm)
# Crawling data from Twitter
api_key = 'xxxxxxxxxxxxxxxxxxxxxxxx'
api_secret = 'xxxxxxxxxxxxxxxxxxxxxxxx'
access_token = 'xxxxxxxxxxxxxxxxxxxxxxxx'
access_token_secret = 'xxxxxxxxxxxxxxxxxxxxxxxx'
# Connect with Twitter API
setup_twitter_oauth(api_key, api_secret, access_token, access_token_secret)
# Search any tweets
data_twitter = searchTwitter(searchString = 'bromo OR ijen',
since = '2020-02-26',
until = '2020-02-28',
n = 100,
lang = 'id')
# Convert data to dataframe
data_twitter_df = twListToDF(data_twitter)
View(data_twitter_df)
使用**searchTwitter**功能，我们可以搜索某个时间段的任何推文，推文数量，以及语言。你可以在文件上找到任何参数。请记住，使用**twitterR**库搜索推文有一些规则。
- 查询
**"politics"**搜一个字政治 - 查询
**"indonesia politics"**来搜索词语印尼和政治(忽略顺序) - 查询
**"\"indonesia politics\""**以搜索短语印度尼西亚政治 - 查询
**"indonesia OR politics"**以搜索一个词印度尼西亚或政治或两者 - 查询
**"indonesia -politics"**搜索印尼无政治一词 - 查询
**"#politics"**搜索标签政治 - 查询
**"@politics"**来搜索提及政治
如何进行数据预处理
最后，我们把手伸向这个故事的主题！但是，在我们进行数据预处理之前，我们必须知道在文本预处理中有三个主要任务要做:
**case-folding**是指将数据转换成小写格式的过程**filtering**表示从数据中移除未使用的字符，如数字、标点符号、标签、提及、URL、HTML 或 CSS 元素**tokenization**从文本数据中生成一组单词(称为标记)的过程**normalization**分为两个任务，即**lemmatization**和**stemming****stopwords removal**指的是提取最常见的字典单词的过程——称为停用词(英语、印度尼西亚语或其他语言，具体取决于我们的主要分析)
为了简化我们的任务，我们想创建一个包含一些文本预处理功能的 R 文件。所以，我们只需要在主 R 文件中需要它的时候调用或者导入它。为此，创建一个名为**cleansing.R**的 R 文件，包含下面的脚本。
# Import libraries
library(tm)
library(xml2)
library(stringr)
library(dplyr)
library(katadasaR) # Indonesia corpus for text mining
# URL removal
removeURL <- function(x){
gsub("http[^[:space:]]*", "", x)
}
# Mention removal
removeMention <- function(x){
gsub("@\\w+", "", x)
}
# Hashtag removal
removeHashtag <- function(x){
gsub("#\\S+", "", x)
}
# Carriage removal
removeCarriage <- function(x){
gsub("[\r\n]", "", x)
}
# Emoticon removal
removeEmoticon <- function(x){
gsub("[^\x01-\x7F]", "", x)
}
# Retweet removal
removeRT <- function(x){
gsub("(rt|via)((?:\\b\\W*@\\w+)+)", "", x)
}
# Invoice removal
removeInvoice <- function(x){
gsub("inv/[0-9]+/+[xvi]+/[xvi]+/[0-9]+", "", x, ignore.case = T)
}
# HTML removal
unescapeHTML <- function(str) {
xml2::xml_text(xml2::read_html(paste0("<x>", str, "</x>")))
}
# Function to make a whitespace
toSpace <- content_transformer(function(x, pattern){
gsub(pattern, " ", x)
})
# Spell Normalization Function
spell.correction = content_transformer(function(x, dict){
words = sapply(unlist(str_split(x, "\\s+")),function(x){
if(is.na(spell.lex[match(x, dict$slang),"formal"])){
x = x
} else{
x = spell.lex[match(x, dict$slang),"formal"]
}
})
x = paste(words, collapse = " ")
})
# Stemming Words
stemming = function(x){
paste(sapply(unlist(str_split(x,'\\s+')),katadasar),collapse = " ")
}
开始了。我们将运行 R 的主脚本来进行文本预处理。同样，这个分析是用印度尼西亚语进行的。因此，对于标准化和停用词删除，它将不同于英语，但我们只需要调整数据。
原始数据样本(数据预处理前)(图片由作者提供)
首先，我们只是执行案例折叠和过滤任务。我们走吧！
# Import libraries
library(tm)
# Import external function
source(file = 'path-to/cleansing.R')
# Read the data
data_tweet = data_twitter_df$text
data_tweet = unique(data_tweet)
# Work with corpus
tweet_corpus = VCorpus(VectorSource(data_tweet))
# Case folding
tweet_corpus = tm_map(tweet_corpus,content_transformer(tolower))
# Retweet removal
tweet.corpus = tm_map(tweet.corpus,content_transformer(removeRT))
# Hashtag removal
tweet_corpus = tm_map(tweet_corpus,content_transformer(removeHashtag))
# URL removal
tweet_corpus = tm_map(tweet_corpus,content_transformer(removeURL))
# HTML removal
tweet_corpus = tm_map(tweet_corpus,content_transformer(unescapeHTML))
# Mention removal
tweet_corpus = tm_map(tweet_corpus,content_transformer(removeMention))
# Carriage removal
tweet_corpus = tm_map(tweet_corpus,content_transformer(removeCarriage))
# Emoticon removal
tweet_corpus = tm_map(tweet_corpus,content_transformer(removeEmoticon))
# Invoice removal
tweet_corpus = tm_map(tweet_corpus,content_transformer(removeInvoice))
# Remove additional symbols to white space
# punctuation
tweet_corpus = tm_map(tweet_corpus,toSpace,"[[:punct:]]")
# numbers
tweet_corpus = tm_map(tweet_corpus,toSpace,"[[:digit:]]")
# Eliminate extra white spaces
tweet_corpus = tm_map(tweet_corpus,stripWhitespace)
运行上面的脚本后，我们很容易看到案例折叠和过滤过程前后的差异。
折叠后的数据(图片由作者提供)
过滤过程后的数据(图片由作者提供)
接下来，我们在检查站见面。在进行文本规范化和停用词移除之前，我们需要下载两种数据作为我们的词典:
**colloquial-indonesian-lexicon.txt**包含一对印尼语中的非正式和正式词汇。在这里下载**stopwords-id.txt**包含印度尼西亚语中最常见的单词，这些单词在文本挖掘中没有足够的重要意义。下载 这里
# Continued from the previous script
# Normalization - lemmatization
spell_lex = read.csv(file = '../colloquial-indonesian-lexicon.txt',
header = TRUE,
sep = ',',
stringsAsFactors = FALSE)
tweet_corpus = tm_map(tweet_corpus,spell.correction,spell_lex)
# Normalization - stemming
tweet_corpus = tm_map(tweet_corpus,content_transformer(stemming))
# Stopwords removal
df_tweet = data.frame(text = sapply(tweet_corpus,as.character),
stringsAsFactors = FALSE)
rm_stopword = VCorpus(VectorSource(df_tweet$text))
# Using edited stopword list
stopwords_id = readLines('path-to/stopwords-id.txt')
rm_stopword = tm_map(rm_stopword,removeWords,stopwords_id)
# Save the data
df_clean = data.frame(text = sapply(rm_stopword,as.character),
stringsAsFactors = FALSE)
View(df_clean)
我们数据的最新版本如下:
归一化和停用词删除后的数据(图片由作者提供)
结论
数据预处理是文本挖掘中至关重要的任务。它决定了整个分析的输出，就像 GIGO(垃圾输入垃圾输出)一样。社交媒体数据太乱了，所以为了让我们的分析更可靠，分析师必须正确地进行数据预处理。请记住，预处理没有系统的顺序，停用词删除可以是第一个要做的任务，并且这个过程可以根据我们的需要重复进行。
请前往我的 GitHub repo 查看完成的代码。
这个故事和前面的 Twitter 数据可视化使用 R 的故事有关。让我们编码，分析，重复！
参考
[1]囟门梭菌。 如何获取、使用、&受益于 Twitter 的 API (2020)、https://blog.hubspot.com/。
[2] N .塞蒂亚布迪。 rtweet:抓取数据 Twitter 孟古那坎 R (2019)，https://www . nurandi . id/blog/rtweet-Crawling-Data-Twitter-孟古那坎-r/ 。
Twitter 数据挖掘——测量用户的影响力
追随者和影响力；科学见解
当有人说一个人在 twitter 上有影响力时，首先想到的是*‘他/她可能有很多粉丝’；*这可能对每个人都不一样，但我确实有过这样的想法，我也遇到过几个这样想的人。
这些影响者到底是谁？这个网站给出了详细的定义，对于这个项目，我们将考虑使用 twitter 的社交媒体影响者。
社交媒体中的影响者是那些因其在特定主题上的知识和专长而建立了声誉的人。他们在自己喜欢的社交媒体渠道上定期发布关于这个话题的帖子，并产生了大量热情参与的人，这些人密切关注他们的观点。
作为在 10 学院受训的一个好处，我开始意识到有很多追随者并不等于有影响力。这个项目展示了我测量用户在 twitter 上的影响力的科学方法，采用的指标包括:popularity_score 和 reach_score，同时考虑到 【跟随者谬误】 如本文所讨论的。
数据收集和数据争论
网页抓取
我采取的第一步是使用 *BeautifulSoup(一个 python 库)*从下面的网站获取一些非洲有影响力的人和政府官员的 twitter 账号。这些网站使用关注者计数和其他指标对有影响力的人和政府官员进行排序。
影响者
#importing libraries
import requests
from bs4 import BeautifulSoup#getting the page
page_inf = requests.get("https://africafreak.com/100-most-influential-twitter-users-in-africa")soup = BeautifulSoup(page_inf.content, 'html.parser')
inf_list = soup.find_all('h2')
政府官员
#getting the page
page_gov = requests.get("[https://africafreak.com/100-most-influential-twitter-users-in-africa](https://www.atlanticcouncil.org/blogs/africasource/african-leaders-respond-to-coronavirus-on-twitter/#east-africa)")soup = BeautifulSoup(page_gov.content, 'html.parser')
gov_list = soup.find_all(class='wp-block-embed__wrapper')
BeautifulSoup 总是以列表形式返回对象，并且可能包含不需要的额外数据，所以我必须做一些清理工作来获取这些影响者和政府官员的 twitter 句柄，将其转换为 Pandas DataFrame，并将其保存为 csv 文件。
#import the necessary libraries
import re
import pandas as pd#cleaning the data
inf_list = re.findall(r"@[\w]*", inf_list)
gov_list = re.findall(r"@[\w]*", gov_list)#converting to dataframe and saving as csv file
inf_df = pd.DataFrame(inf_list, columns=['Handles'])
inf_df.to_csv('influencers.csv')gov_df = pd.DataFrame(gov_list, columns=['Handles'])
gov_df.to_csv('officials.csv')
Twitter API
下一步是通过使用开发人员的帐户凭证访问 Twitter API 来收集关于这些影响者和政府官员的信息。
import tweepy#Variables that contains the user credentials to access Twitter API
consumer_key = 'XXXXXXXXXX'
consumer_secret = 'XXXXXXXXX'
access_token = 'XXXXXXXXX'
access_token_secret = 'XXXXXXXXXXX'#This handles Twitter authentication and the connection to Twitter Streaming API
auth = OAuthHandler(consumer_key, consumer_secret)
auth.set_access_token(access_token, access_token_secret)
api = tweepy.API(auth)
以下是收集的用户信息列表:
- 网名
- 追随者计数(追随者数量)
- 朋友计数(关注人数)
- 状态计数(推文数量)
- 最喜欢的计数(喜欢的数量)
- 转发计数(转发次数)
收集这些信息的方法如下所示
#getting the followers count, following, number of tweets, and screen name
followers_count = {}
following_count ={}
num_of_tweets = {}
screen_name = {}for count in range(len(inf_list)):
try:
user = api.get_user(inf_list[count])
followers_count[inf_list[count]]= user.followers_count
following_count[inf_list[count]] = user.friends_count
num_of_tweets[inf_list[count]] = user.statuses_count
screen_name[inf_list[count]] = user.screen_name
except Exception as e:
pass
详细代码可以在这个 GitHub 库中找到。下面是一个为非洲影响者获取的数据示例。
作者图片
作者图片
数据分析
在收集的数据中，为每个用户计算了以下指标:
- population _ score = Retweets+赞
- Reach_score = Followers —关注
#calculating the popularity score
new_inf_df['Popularity_score'] = new_inf_df['Retweet_count'] + new_inf_df['Favorite_count']#calculating the reach score
new_inf_df['Reach_score'] = new_inf_df['Follower_count'] - new_inf_df['Following_count']
结果—数据可视化
在计算了每个用户的指标之后，我利用 seaborn(一个 python 库)通过按照如图所示计算的每个指标对数据帧进行排序来可视化结果。
##data visualization - government officials#importing library for data visualization
import seaborn as sns#bar plot - reach score
plt.figure(figsize=(20,10)) #customizing the size of the plot
sns.set(style="darkgrid") #customizing the style of the plot#visualizing the data using bar plot
ax = sns.barplot(x='Screen_name', y='Reach_score', palette="Greens_d",
data=new_gov_df.sort_values(by='Reach_score', ascending=False)[0:10]
)#getting the values of the data
for p in ax.patches:
ax.annotate(format(p.get_height()), (p.get_x() + p.get_width() / 2.,
p.get_height()), ha = 'center', va = 'center',
xytext = (0, 10), textcoords = 'offset points')#setting the parameters for the title, x and y labels of the plot
ax.set_title("Reach Score for Top African Government Officials", size=40, weight='bold')
ax.set_xlabel("Twitter Screen Names", size=20, weight='bold')
ax.set_ylabel("Reach Score(Followers-Following)", size=20, weight='bold')#changing the rotation of the x axis tick labels
for item in ax.get_xticklabels():
item.set_rotation(45)
plt.savefig('reach.png') #saving the figure
plt.show()
详细代码可以在这个 GitHub 库中找到。
人气评分
作者图片
作者图片
到达分数
作者图片
作者图片
观察和见解
从上面显示的可视化分析结果可以看出，前 10 名非洲影响者和政府官员的受欢迎程度得分和影响力得分有所不同。
例 1:
世卫组织(政府官员)
- 得分= 8，114，444
- 人气评分= 275，723
新闻 24 (影响者)
- 得分= 3，579，984
- 人气评分= 39，718
查看上面的示例 1，我们可以看到到达分数和流行分数之间的巨大差距。这些用户有很多追随者；然而，与他们的追随者相比，参与他们推文的人数并不多。
例 2:
鲍里斯约翰逊(政府官员)
- 得分= 2，914，656
- 人气评分= 4，930，514
MbuyiseniNdozi (影响者)
- 得分=不在前 10 名
- 人气分= 248228
在示例 2 中，我们看到了这样一种情况，用户的可及性得分低于他们的受欢迎度得分。这里看到的影响者的影响力得分甚至不在前 10 名之列，但受欢迎程度得分在图表上排名第二。
结论
该项目旨在纠正拥有大量追随者就等同于成为有影响力的人的观念，从分析中获得的结果支持了这一目标。流行度分数显示了参与用户推文的人数，与到达分数相比，它被证明是更好的影响力衡量标准。
深造
除了通过人气得分和到达得分来衡量用户的影响力，还可以考虑相关性得分(用户提及+评论)和他们在推文中使用的标签。
关于这个项目的详细信息可以在这个 GitHub 资源库中找到。
感谢您的阅读。
参考
使用气流的 Twitter 调度程序
鸣谢:https://dribbble . com/shots/5370290-Website-for-bZx-Metaphor-Design
为什么你甚至需要一个 Twitter 调度程序？
所有的 Twitter 名人或你在 Twitter 上认识的任何企业，可能都在使用某种调度程序来准时发布他们的推文。
如果你有一堆各种主题的推文草稿，并且想自动发布它们而不用担心时间问题。这个日程表会让你的生活更轻松。
因此在本文中，您将使用 Apache Airflow 构建一个 Twitter 调度器。
最后，你会有一个运行中的管道，你的推文会在预定的时间段发布。此外，如果您想亲自体验运行在 Docker 上的 Apache Airflow，那么这可能是一个不错的起点。
先说这个 bot 话题。😄
气流到底是什么？
Apache Airflow 是一个工作流调度器，本质上是一个 python 框架，允许运行 Python 可以执行的任何类型的任务。例如发送电子邮件、运行 Spark 作业、Hadoop 任务以及在 Postgres 数据库上运行 SQL 查询等
设置计划程序的先决条件
在 docker 上运行 Airflow 是运行应用程序的最佳方式，它消除了各种环境设置错误。我假设你对 docker 的概念比较熟悉，并且你的机器上已经安装了 Docker。在互联网上找到的最好的气流 docker 图像之一是 puckel/docker-airflow ，你可以克隆这个 repo 并开始构建你的数据管道，但如果你想完成这个项目，那么我会建议你克隆这个 repoVJ GPT/Twitter-pipeline，在这里我对之前的 repo 的 docker 文件和 docker-compose 文件进行了更改，以符合这个项目。这个 repo 还包括 Twitter 调度 DAG，这是你对这个项目的所有要求。
如果你还没有克隆回购协议，👇
在这里，我们将建立一个 Twitter 调度数据管道，这个想法是收集数百条推文在一个文件中…
github.com](https://github.com/vjgpt/twitter-pipeline)
但是在你开始这条管道之前，你需要做两件事:
- 创建一个 Twitter 开发者 API 账户。(申请访问— Twitter 开发者| Twitter 开发者)
- 启用 Google Drive v3 备份你所有的数据。( Python 快速入门| Google Drive API | Google 开发者)
创建完 Twitter 开发者账户后，确保保存了所需的密钥和凭证，并将其放在**topic_tweet.py**文件中
consumer_key = '' # Add your API key here
consumer_secret = '' # Add your API secret key here
access_token = '' # Add your Access Token key here
access_token_secret = '' # Add your Access Token secret key here
要设置 Google Drive API，您需要在本地机器上创建一个 python 环境，并遵循上面的链接。在你允许你的应用后，你会得到两个文件**credentials.json**和**token.pickle**。复制这两个文件，放入 repo *twitter-pipeline/dags/daglibs* 文件夹路径。
├── dags
│ ├── daglibs
│ │ ├── **credentials.json**
│ │ ├── etl_job.py
│ │ ├── **token.pickle**
│ │ ├── topic_tweet.py
│ │ └── upload.py
│ └── post_tweet.py
├── data
让我们开始码头集装箱🚢
我们将在本地执行器模式下运行 Airflow，这意味着合成文件不会构建您的图像，在继续之前，您可以自己在本地构建它。
cd twitter-pipelinedocker build -t aflatest .
下面是docker-compose-LocalExecutor.yml的文件。
你可以在上面的 compose 文件中看到 volume 部分，它基本上在本地机器和 docker 容器之间创建了一个桥梁。它有助于在本地卷和 Docker 路径之间持久化数据。您将 dag 文件添加到本地机器，它将反映在您的 docker 容器中，并最终反映在 Airflow UI 中。
现在您已经准备好启动这些容器并运行 Apache Airflow，确保您位于twitter-pipeline repo 的主路径中。
docker-compose -f docker-compose-LocalExecutor.yml up -d
docker-compose up -d 在后台启动你的容器(即分离模式)
您可以使用拆除合成设置
docker-compose -f docker-compose-LocalExecutor.yml down
您可以使用以下命令检查在后台模式下运行的服务的日志
docker-compose -f docker-compose-LocalExecutor.yml logs
点击 的网页界面 http://localhost:8080
您应该可以看到您放在./dags目录中的任何 Dag，尽管有时它们可能需要一分钟才能显示出来。
一旦 DAG 出现，您对 python 文件所做的任何更改将在下次触发 DAG 时立即生效。
PS:打开 dag 激活它。
Twitter 调度程序管道
好了，现在你的气流在你的码头集装箱上流动，让我们了解一下这条管道是如何工作的。
在data/tweets文件夹中有一些文件，用户把他/她所有起草的推文放在那里。我们把所有的推文都放到一个 CSV 文件中，这是所有推文文件的合并。参照这个 CSV 文件，我们发布推文并做出相应的更改。最后，所有的 CSV 文件和文本文件都备份在 google drive 中，以防发生致命的事情。
这整个过程可以在预定的 DAG 运行时发送你起草的推文。
图片来源:【https://dribbble.com/shots/3166602-Twitter
post_tweet.py
这是主 DAG 文件，显示在你的气流用户界面中，它包含你在这个 python 文件中定义的任务。
# DAG is scheduled to run every 8 hoursdag = DAG('PostTweet',schedule_interval=timedelta(hours=8), default_args=default_args)
该 DAG 计划每 8 小时运行一次，您可以根据需要更改运行时间。
etljob.py
这是大部分数据管道发生的地方。它把你从不同文件中起草的推文合并成一个文件。
该文件中的每个函数都被视为 DAG 中的一个任务。
topic _ tweet.py
在这里，我们连接到 Twitter API 并发布推文。这个文件需要在 Twitter 开发人员 API 控制台登录时生成的所有密钥和秘密。
上传. py
此文件将所有与此管道相关的数据备份到您的 Google Drive 帐户。
现在，让我们测试这条管道。
在data/tweets文件夹中的 txt 文件中添加一些 tweet，你可以添加多个 tweet 到那个文件中，或者创建一个新文件并在其中添加 tweet。然后触发管道，你会发现你的推文发布在你的 Twitter 账户上，所有文件都备份在 Google Drive 上。
干杯！！！🥂
结束了！！
这只是您刚刚制作的一个演示管道，但是您可以使用同一组基础设施，通过几个用例来制作更多这样的管道。
如果你有兴趣更深入地研究这个话题，那么试着创建一个比我们现在做的更好的可扩展架构。在这个架构中，我们使用了 LocalExecutor，并且有一个不可伸缩的 singletons 调度程序。尝试使用 CeleryExecutor 模式，在这种模式下，工作从任务队列中分发出去，所有任务都是分布式的。
哦，等等…如果这对你有所帮助，请支持我的工作。
这是这个项目的回购👇
此时您不能执行该操作。您已使用另一个标签页或窗口登录。您已在另一个选项卡中注销，或者…
github.com](https://github.com/vjgpt/twitter-pipeline)
初学者使用 ggplot2 的 Twitter 数据可视化介绍
动手教程
如何使用 R (ggplot2)交互式地可视化您的 Twitter 数据
概观
F 对于这个项目，我们使用的是 2019 年 5 月 28 日—29 日抓取的 Twitter 的原始数据。此外，数据为 CSV 格式(逗号分隔)，可在此处 下载 。它涉及两个主题，一个是佐科·维多多的数据，包含关键字*【佐科·维多多】，另一个是 Prabowo Subianto 的数据，包含关键字【Prabowo subi anto】*。这些包括几个变量和信息，以便确定用户情绪。实际上，数据有 16 个变量或属性和 1000 多个观察值(两种数据都有)。表 1 列出了一些变量。
导入模块
数据的变量(图片由作者提供)
数据可视化
数据探索旨在从 Twitter 数据中获取任何信息和洞察力。应该指出的是，数据进行了文本预处理。对被认为很有兴趣讨论的变量进行探索。例如，变量**created** 。
为 tweet 活动创建条形图(佐科·维多多和普拉博沃·苏比安托)
图 1 佐科·维多多(左)和普拉博沃·苏比安托(右)的推文总数直方图(图片由作者提供)
根据图 1，我们可以得出结论，通过数据抓取(对于关键字*“佐科·维多多”和“普拉博沃·苏比安托”)的推文数量并不相似，即使是在同一天。例如图 1(左)，直观地表示仅在 WIB 2019 年 5 月 28 日 03:00-17:00 期间获得了带有关键词“佐科·维多多】*的推文。而在图 1(右)上，我们得出结论，在 2019 年 5 月 28 日至 29 日 WIB 12:00-23:59(2019 年 5 月 28 日)和 WIB 00:00-15:00(2019 年 5 月 29 日)期间获得的带有关键字 “Prabowo Subianto” 的推文。
为 tweet 活动创建条形图(Prabowo Subianto)
图 Prabowo Subianto(左)2019 年 5 月 28 日和(右)2019 年 5 月 29 日的总推文直方图(图片由作者提供)
基于图 2，我们得到了使用关键字*“佐科·维多多”和“Prabowo subi anto”发推文的用户之间的显著差异。带有关键词【佐科·维多多】的推文往往会在某个时间(07:00-09:00 WIB)激烈地谈论佐科·维多多，其中 08:00 WIB 的推文数量最多。它有 348 条推文。而带有关键字“Prabowo Subianto”的推文倾向于在 2019 年 5 月 28 日至 29 日期间不断谈论 Prabowo subi anto。2019 年 5 月 28 日至 29 日上传的带有关键词“Prabowo subi anto”*的平均每小时推文数为 36 条。
创建情绪得分的密度图(佐科·维多多和普拉博沃·苏比安托)
图 3 情绪得分密度图(左)佐科·维多多和(右)普拉博沃·苏比安托(图片由作者提供)
图 3 是 2019 年 5 月 28 日—29 日，以*【佐科·维多多】**【Prabowo subi anto】*为关键词的多条推文的柱状图。根据图 3(左)，可以得出结论，正在谈论 Prabowo Subianto 的用户每小时上传的平均推文数少于每小时 30 条推文的两天推文总数。Twitter 用户在 WIB 时间 19:00–23:59 不太热衷于谈论 Prabowo Subianto。是印尼的休息时间造成的。然而，由于居住在国外的用户或仍然活跃的用户，带有其主题的推文总是在午夜更新。然后，用户在 WIB 时间 04:00 开始活动，在 WIB 时间 07:00 达到高峰，然后下降，直到 WIB 时间 12:00 再次上升。
创建一个情绪得分条形图(佐科·维多多和普拉博沃 Subianto)
图 4 情绪得分柱状图(左)佐科·维多多和(右)普拉博沃·苏比安托(图片由作者提供)
图 4 是包含关键词*【佐科·维多多】**【Prabowo subi anto】的情感得分的密度图。推文的得分是由组成推文的词根的平均得分获得的。因此，它的分数是为每个词根给出的，值在-10 到 10 之间。如果分数越小，那么对推文中的词的负面情绪就越多，反之亦然。根据图 4(左)，可以得出结论，包含关键词“佐科·维多多”的推文的负面情绪范围为-10 到-1，中间值为-4。这也适用于积极的情绪(当然是积极的分数)。根据图 4(左)中的密度图，可以发现积极情绪的得分具有相当小的方差。因此，我们得出结论，对包含关键词“佐科·维多多”*的推文的积极情绪并不太多样化。
图 4(右)显示了包含关键字*“Prabowo subi anto”的情感得分的密度图。它与图 4(左)不同，因为图 4(右)中的负面情绪范围从-8 到-1。暗示推文没有太多负面情绪(推文有负面情绪，但不够高)。此外，负面情绪得分的分布在 4 和 1 的范围内有两个峰值。然而，积极情绪的范围是从 1 到 10。与图 4(左)相反，图 4(右)中的积极情绪具有很高的方差，并且在 3 和 10 的范围内具有两个峰值。这表明包含关键字“Prabowo subi anto”*的推文具有高正面情绪。
为情感类创建一个饼图(Jowo Widodo 和 Prabowo Subianto)
图 5 情感类饼图(左)佐科·维多多和(右)普拉博沃·苏比安托(图片由作者提供)
图 5 是已被分类为负面、中性和正面情绪的推文的情绪得分汇总。负面情绪是得分小于零的情绪。中性是得分等于零的情绪。反之，积极情绪的得分大于零。根据图 5，可以得出带有关键字*【佐科·维多多】的推文的负面情绪百分比低于带有关键字【Prabowo subi anto】的推文。它有 6.3%的百分比差异。还发现，与具有关键字“Prabowo subi anto”的推文相比，包含关键字“佐科·维多多”的推文具有更大百分比的中性情绪和积极情绪。通过对饼状图的研究发现，与关键字为“Prabowo subi anto”的推文相比，关键字为“佐科·维多多”的推文往往有更大比例的正面情绪。但通过密度图发现，正面和负面情绪得分的分布显示，与“佐科·维多多”相比，包含关键词“Prabowo subi anto】*的推文往往具有更大的情绪得分。它必须进行进一步的分析。
图 6 推文中的 Wordcloud(左)佐科·维多多和(右)普拉博沃·苏比安托(图片由作者提供)
图 6 显示了用户在 2019 年 5 月 28 日-29 日经常上传的推文中的术语或单词(关键词*“佐科·维多多”和“普拉博沃·苏比安托”)。通过这种 WordCloud 可视化，可以发现针对关键词进行讨论的热门话题。对于包含关键词【佐科·维多多】的推文，发现词条【tuang】【petisi】【negara】【aman】**【NUS antara】是每条推文中出现次数最多的前五个词条。然而，包含关键字“佐科·维多多”的推文发现，术语“普拉博沃”、“Subianto”、“kriminalisasi”、“selamat”、“T13”和*“迪拜”是每条推文中出现次数最多的前五个术语。这间接显示了使用关键字“Prabowo subi anto”上传的推文的模式:几乎可以肯定，每条上传的推文都直接包含了名字“Prabowo subi anto”，而不是通过提及(@)。这是因为，在文本预处理中，提示音(@)已被删除。
请前往我的 GitHub repo 查看完成的代码。
参考
[1] K .博劳，c .，j .冯，r .沈 【微博语言学习:利用推特培养交际和文化能力 (2009)，网络学习的进步——ICWL 2009，第八届国际会议，德国亚琛，2009 年 8 月 19 日至 21 日。
Twitter JSON 数据处理
使用 python 库清理和润色用于社交媒体分析的推文数据帧。
witter 允许使用用于访问 Twitter API 的 Python 库 tweepy 来收集 tweepy。在这里，我不打算给出如何收集 tweet 的教程，因为已经有一些好的评论了(看看下面的参考资料)，而是给你一个完整的例子，说明如何处理 tweet 对象，以便建立一个干净的数据框架，我们可以在这个框架上进行社交媒体分析。
TL；TR: 在这个过程中，我们将展平 Twitter JSON，在几个选项(主推、转发、引用等)中选择文本对象。)，清理它们(删除非字母字符)，翻译非英语推文，计算文本的情感，以及关联给定用户定义的位置或自动地理定位的位置。
要使用的库: 熊猫国家转换器GeoPyspaCyGoogle transNLTK。
每个 tweet 对象 以 JSON 格式出现，混合了“根级”属性和子对象(用 *{}* 符号表示)。Twitter 开发者页面给出了以下例子:
{
"created_at": "Wed Oct 10 20:19:24 +0000 2018",
"id": 1050118621198921728,
"id_str": "1050118621198921728",
"text": "To make room for more expression, we will now count all emojis as equal—including those with gender and skin t… https://t.co/MkGjXf9aXm",
"user": {},
"entities": {}
}
当然，这只是组成每条推文的庞大字典中的一小部分。另一个流行的例子是这个 Twitter 状态对象图。
对于大多数类型的分析，我们肯定需要属性，如 tweet 文本、用户屏幕名称或 tweet 位置。不幸的是，正如您所看到的，这些属性没有一个清晰的格式，相反，它们分布在 JSON 的各个层次上——例如，tweet 位置坐标位于
tweet_object['place']['bounding_box']['coordinates']
正是由于这一事实，收集的推文需要一个大的清理和转换过程，这就是这篇文章的目的。
推特数据
我最近进行了一个语言本地化项目，我需要在 Twitter 上做一个社交媒体分析。为此，我在几天的时间里收集了 52830 条包含以下关键词的推文: ‘#FIFA20’ ，’ #FIFA21’ ， ‘FIFA20’ ， ‘FIFA21’ ， ‘FIFA 20’ ， ‘FIFA 21’ 和**’ # easporter 然后，为了对它们进行正确的分析，我必须事先清理每个 tweet 对象，这样我才能得出有意义的结论。**
由于该项目的性质，我主要感兴趣的是关于推文位置的数据(国家和坐标)，英文版本文本的情感，以及推文使用的语言。加工步骤的目标是完善和发现这些属性。您可以在以下存储库中找到该项目的详细信息:
这是一个端到端的项目，我们的目标是执行国际足联视频游戏的语言本地化，只有公众…
github.com](https://github.com/hectoramirez/Language-localization_FIFA)
让我们用这个数据集来举例说明 tweets 处理的步骤！
处理 JSON
正如我们看到的，在包含文本数据的 Twitter JSON 中有多个字段。在典型的 tweet 中，有 tweet 文本、用户描述和用户位置。在超过 140 个字符的 tweet 中，还有扩展 tweet 子 JSON。在引用的推文中，有原始推文和引用推文的评论。
为了大规模分析 tweet，我们需要将 tweet JSON 扁平化为一个层次。这将允许我们以数据帧格式存储推文。为此，我们将定义函数flatten_tweets()，该函数将接受几个关于文本和位置的字段(该字段存储在place中)。看一看:
现在，您可能想要研究所有的文本字段(主字段、转发字段或引用字段)，但是，为了简单起见，这里我只保留一个文本字段。为此，我们现在定义一个函数select_text(tweets)来选择主要文本，无论该推文是主要推文还是转发推文，我们决定删除引用的文本，因为它通常是重复的，可能没有信息。
我们现在构建数据框。请注意，我们选择了与社交媒体分析相关的主要列(字段)。这包括 tweet 语言、lang和由用户手动设置的user-location。我们还保留了place中的country、country_code和coordinates字段。当推文被地理标记时，这些字段就会出现，并且通常包含在不到 10%的推文中。以下代码块构建了数据帧:
**import** **pandas** **as** **pd**
*# flatten tweets*
tweets = flatten_tweets(tweets_data)
*# select text*
tweets = select_text(tweets)
columns = ['text', 'lang', 'user-location', 'place-country',
'place-country_code', 'location-coordinates',
'user-screen_name']
*# Create a DataFrame from `tweets`*
df_tweets = pd.DataFrame(tweets, columns=columns)*# replaces NaNs by Nones*
df_tweets.where(pd.notnull(df_tweets), **None**, inplace=**True**)
数据帧的头部看起来像这样:
df_tweets.head()
df_tweets.info()
请注意，几乎只有一半的推文包含手动设置的用户位置字段，甚至 1%的推文都没有地理标记，即，它们没有提供位置字段。这凸显了收集尽可能多的推文的重要性！
在下文中，我们感兴趣的是清理和抛光每个 dataframe 列。
语言
在流程的这一部分，我们将用语言标准名称替换lang中的语言代码。如文档中所述:
如果存在，[
lang]表示与机器检测到的 Tweet 文本语言相对应的 BCP 47 语言标识符，如果没有检测到语言，则为*und*。
我们使用这个库中的辅助languages.json文件来执行这个步骤。该文件将语言代码映射到语言标准名称。下面的代码将完成这个任务:
**with** open('languages.json', 'r', encoding='utf-8') **as** json_file:
languages_dict = json.load(json_file)names = []
**for** idx, row **in** df_tweets.iterrows():
lang = row['lang']
**if** lang == 'und':
names.append(**None**)
**elif** lang == 'in':
name = languages_dict['id']['name']
names.append(name)
**elif** lang == 'iw':
name = languages_dict['he']['name']
names.append(name)
**else**:
name = languages_dict[lang]['name']
names.append(name)
df_tweets['language'] = names
df_tweets.drop(['lang'], axis=1, inplace=**True**)
位置
现在我们开始处理位置。我们将首先处理place字段，然后处理user-location字段。
地方
很明显,place对象中的数据比user-location更可靠。因此，虽然它构成了我们推文的 0.91%，但我们会照顾它。首先，place-country_code中的国家代码以 ISO 2 形式出现，为此我们将使用国家转换器将其转换为 ISO 3 形式。然后，我们将执行同样的操作，将place-country名称改为标准的简称。这是有利的，因为，例如， Plotly 地图使用 ISO 3 代码来定位国家。
用户位置
在这里，我们将手动设置的user-locations翻译成国家名称和代码——这涉及到对用户的信任。我们使用 GeoPy 库来识别一个位置(可能是一个地址)并为其分配一个国家。同样，我们使用country_converter来查找 ISO 3 表格中的国家代码。
提醒一句 : GeoPy 连接到一个 API，不幸的是，每次调用几乎要花一秒钟。这使得计算~ 50 K tweets 的过程相当慢。
注意: tqdm 是一个 python 库，对 pandas 有很好的实现，在代码运行时输出进度条。这会让你的生活更轻松！
最后，我们将place-country和user-country列减少为一列，当前者存在时保留前者，否则保留后者。我们对代码列进行同样的操作:
countries, codes = [], []
**for** idx, row **in** df_tweets.iterrows():
**if** row['place-country_code'] **is** **None**:
country = row['user-country']
code = row['user-country_code']
countries.append(country)
codes.append(code)
**else** :
countries.append(row['place-country'])
codes.append(row['place-country_code'])
df_tweets['location'] = countries
df_tweets['location_code'] = codes
*# drop old columns*
df_tweets.drop(columns=['place-country', 'place-country_code',
'user-country', 'user-country_code'], inplace=**True**)
此时，我们的数据集如下所示:
df_tweets.head()
文本清理
现在是处理推文文本的时候了。这将涉及删除非字母字符和翻译非英语推文。然而，我们将保留这两个选项，并实际使用带有表情符号和其他字符的文本进行分析，因为我们的情感分析器可以很好地处理它们。
要删除非字母字符，我们使用 spaCy ，因为它非常简单，我们不需要指定正则表达式。请记住，下面的代码块删除了带有撇号的表情符号和单词，如“我是”、“你们都是”、“不要”等。
**import** **spacy**
nlp = spacy.load('en_core_web_sm')
**def** cleaner(string):
*# Generate list of tokens*
doc = nlp(string)
lemmas = [token.lemma_ **for** token **in** doc] *# Remove tokens that are not alphabetic*
a_lemmas = [lemma **for** lemma **in** lemmas **if** lemma.isalpha()
**or** lemma == '-PRON-'] *# Print string after text cleaning*
**return** ' '.join(a_lemmas)
df_tweets['text_cleaned'] = \
df_tweets['text'].progress_apply(cleaner)
翻译
为了翻译非英语推文，我们使用 googletrans ，作为 GeoPy，它连接到它的 API，然而它要快得多。
**另一个警告:**存在一个讨论过的记录不良的错误，例如，这里:https://stack overflow . com/questions/49497391/Google trans-API-error-expecting-value-line-1-column-1-char-0，它会断开您的连接并阻止您的 IP。为了避免这个错误，我使用np.array_split()将数据帧分成几个块，在一个循环中一次处理一个块。通过这样做，错误不会发生，但是我仍然将每个块的翻译保存到一个csv中，这样如果在任何迭代中出错，我可以只重新计算一个块。我每次都会实例化Translator()。
最后，我们将原始的、未经处理的英文文本添加到text_english:
*# replaces NaNs by Nones*
df_english.where(pd.notnull(df_english), **None**, inplace=**True**)
*# add original English tweets to text_english by replacing Nones*
texts = []
**for** idx, row **in** df_english.iterrows():
**if** row['text_english'] **is** **None**:
text = row['text']
texts.append(text)
**else** :
texts.append(row['text_english'])
df_english['text_english'] = texts
此时，数据帧看起来像这样:
df_english.head()
情感分析
我们最终计算每条推文的情感。为此，我们使用nltk.sentiment.vader库中 NLTK 的SentimentIntensityAnalyzer对象。
VADER (Valence Aware 字典和情感推理器)是一个基于词典和规则的情感分析工具，专门针对社交媒体中表达的情感。T22【参。】
这个库使用起来非常简单，如你所见:
请注意，polarity_score输出文本为负、中性或正的概率以及一个复合分数。然后，我们提取后者并将分数附加到数据帧中。
结束
为了便于展示，我们对各列进行了重新排序。
*cols_order = ['text', 'language', 'location', 'location_code',
'location-coordinates', 'sentiment', 'text_english',
'text_cleaned', 'user-screen_name']df_final = df_sentiment[cols_order]*
最终数据集应该如下所示:
df_final.head()
附加:一个简单的分析
为了举例说明可以用这个数据集做什么，让我们按国家建立一个平均推文情绪得分的可视化:
请注意，我们使用了一个国家/语言数据框架，它可以在这个库中找到。上面的代码输出以下 Plotly 地图:
这张世界地图看起来不太乐观😕
这篇文章中使用的全部代码可以在我的知识库中找到:
* [## hectoramirez/语言-本地化 _FIFA
EA Sports 的 FIFA 本地化端到端研究。通过以下方式为 hectoramirez/Language-localization _ FIFA 的发展做出贡献…
github.com](https://github.com/hectoramirez/Language-localization_FIFA/blob/master/Tweets%20processing%20and%20sentiment.py)
关于作者
我最近获得了物理学博士学位，目前正在进入数据科学领域。**非常感谢对这篇文章的任何评论和/或建议。**另外，看看我的其他故事:
加载数据，用散景制作出色的可视化效果，将它们放在 GitHub Pages 网站上，让气流自动流动…
towardsdatascience.com](/your-live-covid-19-tracker-with-airflow-and-github-pages-658c3e048304)
最后，请随时在 LinkedIn 与我联系:
[## héctor ramírez-西班牙巴伦西亚地区|职业简介| LinkedIn
我最近获得了物理学博士学位，专攻实验数据分析和数学建模。我领导了…
www.linkedin.com](https://www.linkedin.com/in/harr/)
参考
Datacamp 用 Python 分析社交媒体数据:
登录 DataCamp 帐户
learn.datacamp.com](https://learn.datacamp.com/courses/analyzing-social-media-data-in-python) [## 我的第一个 Twitter 应用
如何使用 Python 和 Tweepy 创建自己的数据集
towardsdatascience.com](/my-first-twitter-app-1115a327349e) [## 适合初学者的 Tweepy
使用 Twitter 的 API 建立你自己的数据集
towardsdatascience.com](/tweepy-for-beginners-24baf21f2c25) [## 如何使用 Tweepy 访问 Twitter 的 API
使用易于使用的 Python 库获得大型 Twitter 数据集的分步指南(包含代码和技巧)
towardsdatascience.com](/how-to-access-twitters-api-using-tweepy-5a13a206683b) [## 用 Tweepy Python 抓取推文
这是一个使用 Python 库 Tweepy 抓取 Twitter tweets 的逐步指南。
medium.com](https://medium.com/@leowgriffin/scraping-tweets-with-tweepy-python-59413046e788)*
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
