reference.md 10 KB
От Колянка я получил базу в таком виде. Не самый удобный формат для работы (mysql, в постгрес без бубна не импортируется, в общем полный набор), к тому же он рак и забыл вынести из json половину важных полей, так что придётся немного поколдовать. Нам нужно три csv файла: один ключевой, без всякого юзеринпута, чтобы минимизировать возможные ошибки, и два вспомогательных, чтобы потом быстро найти человекочитаемую информацию, зная только id.
Это всё можно (и нужно) делать рекурсивным скриптом, но я не долго думая набросал самый гениальный в своей жизни (нет) sql-запрос:
SELECT type,
authorid,
commentid,
entry,
mid(fulljson,locate("\"date\":",fulljson)+7,locate("}",fulljson,locate("\"date\":",fulljson)) -(locate("\"date\":",fulljson)+7)) AS "timestamp",
mid(fulljson,locate("\"in_reply_to_comment_id\":",fulljson)+25,locate(",",fulljson,locate("\"in_reply_to_comment_id\":",fulljson))-(locate("\"in_reply_to_comment_id\":",fulljson)+25)) AS "in_reply_to",
mid(fulljson,locate("\"summ\":",fulljson)+7,locate(",",fulljson,locate("\"summ\":",fulljson)) -(locate("\"summ\":",fulljson)+7)) AS "rating"
FROM topcomments
INTO outfile 'out.csv' fields enclosed BY '"' terminated BY ';' escaped BY '\\' lines terminated BY '\n';
SELECT DISTINCT authorid,
author
FROM topcomments
INTO outfile 'author_helper.csv' fields enclosed BY '"' terminated BY ';' escaped BY '\\' lines terminated BY '\n';
SELECT commentid,
text
FROM topcomments
INTO outfile 'comments_helper.csv' fields enclosed BY '"' terminated BY ';' escaped BY '\\' lines terminated BY '\n';
И получил нужные файлы: 1,2,3; Если у кого возникли любые проблемы со скачиванием, пишите на noperope@cock.li – перезалью.
Дальше эти датасеты можно импортировать в любое удобное приложение для анализа данных, будь то GNU Octave, Matlab, Scilab или что-нибудь другое на ваш выбор. Я использовал R, поэтому все сниппеты будут для него.
Количество комментов:
nrow(out) // 1020787
Даём имена столбцам:
colnames(out) <- c("чототам","чототам2",...) // ну вы понели
Количество комментов с рейтингом (можно заменить на любое имя столбца), удовлетворяющим критерию:
nrow(na.omit(out[out$rating>0,])) // рейтинг больше нуля
nrow(na.omit(out[out$rating<0,])) // рейтинг меньше нуля
nrow(na.omit(out[out$rating==0,])) // рейтинг равен нулю
nrow(out[out$rating=="N/A",]) // количество N/A-строк
Сортировка, где 10 – количество возвращаемых результатов:
head(out[order(out$rating),],10) // по возрастанию
head(out[order(-out$rating),],10) // по убыванию
Суммирование значений в столбце:
sum(out$rating,na.rm = TRUE) // обычное
sum(abs(out$rating),na.rm=TRUE) // по модулю
Десять лучших и худших комментов определённого пользователя:
user <- out[out$author_id=="47839",] // для удбоства, и чтобы не заставлять машину лишний раз пересчитывать всё, создаём датафрейм со всеми комментами юзера
head(user[order(user$rating),],10) // и как обычно сортируем по критерию
head(user[order(-user$rating),],10) // в обратном порядке
Топ юзеров по количеству комментариев:
appearances <- table(out$author_id) // создаём новый датафрейм типа table, который умеет автоматически считать уники
appearances <- as.data.frame(appearances) // Приводим к стандартному виду
head(appearances[order(-appearances)]) // сортируем как обычно
appearances[appearances$Var1==39816,] // Находим себя
Таким же образом можно находить всё, что требует подсчёта количества вхождений:
agg2 <- table(out$article_id) // группировка статей по количеству комментариев
agg2 <- as.data.frame(agg2) // например
Топ юзеров по количеству лайков. Если всё остальное можно, исхитрившись, посчитать и с помощью обычного sql-запроса, то здесь уже не обойтись без чёрной магии R:
agg <- aggregate(out$rating,by=list(out$author_id),FUN=sum,na.rm=TRUE) // аггрегируем РЕЙТИНГ по ПОЛЬЗОВАТЕЛЯМ (не наоборот!)
head(agg[order(-agg$x),],10) // сортируем. Вуаля.
Строим график активности:
hist(out$timestamp,freq = TRUE,breaks = 500) // лёгкий путь
ggplot(out,aes(out$timestamp)) +
geom_histogram(binwidth = 500) +
scale_x_continuous(
breaks = c(1325376000,1356998400,1388534400,1420070400,1451606400,1483228800),
labels = c("2012","2013","2014","2015","2016","2017")) // сложный путь, нужна библиотека ggplot2
Находим экстремумы
agg2 <- table(cut(out$timestamp, breaks = seq(1311744972,1493280558,by=86400))) // разбиваем датасет на "уровни", по 86400 секунд каждый
agg2 <- as.data.frame(agg2) // считаем вхождения строк в каждый уровень
Теперь нам хотелось бы найти самое часто употребимое слово на теже. Здесь мы уже будем иметь дело с очень большими массивами данных, поэтому запросы могут идти долго (до минуты на моей машине). Не паникуйте, "он не завис, он просто думает".
words <- strsplit(comments_helper$X2, " ") // режем комменты на слова по пробелу
words2 <- unlist(words) // считаем слова
words <- table(unlist(words)) // считаем вхождения
words <- as.data.frame(words)
words <- words[nchar(as.character(words$Var1))>2,] // фильтруем короткие и пустые строки
Дальше на нас вываливается гора союзов и предлогов. С этим ничего не сделать, если только у вас нет под рукой профессиональных пакетов для семантического анализа, поэтому просто пробежимся глазами по списку и выделим интересное.
Строим график распределения и проверяем его на нормальность:
ggplot(out,aes(out$rating)) + geom_histogram(binwidth=1) + xlim (-20,+50) // сырой график
ggplot(out,aes(out$rating)) + geom_histogram(binwidth=1) + xlim(-20,+50) + ylim(0,93078) // график без нулей и единиц
sd(out$rating,na.rm = TRUE) // находим стандартное отклонение
qqnorm(out$X7) // строим qqplot сравнения нашего распределения с гипотетическим нормальным
ks.test(x=out$rating,y='pnorm',alternative = 'two.sided') // проверяем распределение на нормальность с помощью теста Колмогорова
По просьбам трудящихся, сводная таблица пользователей по показателю количество лайков/количество комментов:
agg <- aggregate(out$rating,by=list(out$author_id),FUN=sum,na.rm=TRUE) // считаем лайки
agg2 <- as.data.frame(table(out$author_id)) // считаем комменты
colnames(agg) <- c("author_id","likes")
colnames(agg2) <- c("author_id","comments") // называем колонки
total <- merge(agg,agg2,by="author_id") // сводим
total$efficiency <- total$likes/total$comments // считаем "эффективность"
total <- merge(total,author_helper,by="author_id") // можно добавить имена буквами для удобства
total_filtered <- total[total$comments>100,] // фильтруем комменты с меньше 100 комментариев
По просьбам турдящихся [2]. Строим график активности по времени постинга:
out$hours <- format(as.POSIXct(out$timestamp,origin="1970-01-01"),"%H") // выделяем в отдельный столбец час суток, в который был написан комментарий
ggplot(out,aes(as.numeric(out$hours))) + geom_histogram(binwidth = 1) // строим график. Готово.