Ctrl + ↑ Позднее

Конвертация огромных таблиц из экселя в csv

У меня был стомегабайтный документ экселя. Нужно было находить в нём строки, а маковский эксель думал над каждым кликом по 5 секунд. Я попробовал Numbers, он работает быстрее, но теряет данные: в таблице 65535 строк вместо 910070.

Решил сделать из таблицы текстовый файл и работать с ним в виме. Вернулся в эксель и экспортировал файл в csv, вышло так:

Все данные слиплись в одну строку. Можно вылечить автозаменой, но это будет долго, и как-то слишком много мороки.

В итоге экспортировал скриптом на питоне:

#! /usr/bin/env python

import xlrd
import csv

book = xlrd.open_workbook('postings.xlsx')

# Assuming the fist sheet is of interest 
sheet = book.sheet_by_index(0)

# Many options here to control how quotes are handled, etc.
csvWriter = csv.writer(open('postings.csv', 'w'), delimiter='|') 

for i in range(sheet.nrows):
    csvWriter.writerow(sheet.row_values(i))

Перевёрстка графика полива

В Золотой осени висит такой график полива, я не удержался и сделал свою версию.

Автор показал данные в таблице-календаре. Такой формат удобен рабочему насосной станции, но не дачнику. Рабочий хочет знать, в какой день и в какое время включить насос или повернуть вентиль, а дачнику достаточно видеть, когда включат воду на его участке.

Первым делом вынес за скобки повторяющиеся Южные и Северные, убрал таблицу:

Докрутил, скомпоновал в горизонтальный формат А4:

Северные сверху, Южные снизу, Восточные справа, Западные слева :-)

Визуализация о британском искусстве

Галерея Тейт — самое крупное собрание британского искусства. Она открылась в 1500 году и сейчас хранит 30 000 художественных работ: рисунки, картины, скульптуры, фотографии, инсталляции. Данные об её экспонатах лежат на гитхабе.

Florian Kräutli визуализировал их вот так:

Горизонтальное положение кружка показывает дату рождения художника, размер — число опубликованных работ.
К уже знакомому графику добавился второй, на котором каждый кружок — покупка произведения музеем. Линии соединяют художников с их произведениями.

Первый график смотрится клёво. Мне не нравится, что вертикальное положение кружков ничего не значит. Цвет не используется, можно было им показать, например, технику.

На втором графике каша: непонятно, какой художник что нарисовал. Главная проблема в соединительных отрезках: информацию содержат только их начала и концы. Но линии сливаются в пятна и разобраться во взаимосвязях невозможно:

В каком году галерея приобрела больше всего работ этого художника?

Я поменял формат графика и добавил полезной информации: показал сколько лет каждая картина ждала попадания в музей:

Каждая полоска — картина. Левый конец показывает год создания, правый — приобритения музеем. Чем длинее полоска, тем дольше работа ждала своего часа. Картины одного художника стоят рядом, раскрашены одинаково, отсортированы по году создания.

Казалось бы, вертикальная ось по-прежнему ничего не значит. Однако, наклон левого края показывает, в какие годы художник нарисовал больше работ, достойных музея:

Первые пять лет Энди Уорхол творит с постоянной скоростью. В 1965 году затишье. Последние несколько лет скорость растёт.

По правому краю видно, что музей получил большую часть работ в 2008 и 2009 годах.

Последние две картины галерея приобела в 2016. Вот одна из них:

Алекс Кац, Полнолуние, 1988

Как и автор исходной визуализации, я убрал из данных сверхпродуктивного Уильяма Тёрнера. С ним было бы так:

Уильям Тёрнер нарисовал большую часть галереи Тейт

Мейковер мандей

В проекте Мейковер мандей дизайнеры и аналитики переделывают визуализации интернет-изданий. Каждый понедельник им дают какую-нибудь статью с визуализацией и исходные данные, надо что-нибудь из них узнать и собрать свою версию за час.

На этой неделе — статья The National Parks Have Never Been More Popular. В ней Эндрю Флауэрс показывает рост посещаемости национальных парков Америки:

Нравится, что нет легенды, подписи прямо на графиках.

Не нравится:

  • Линейные диаграммы обманывают: кажется, что значения между точками имеют смысл. Верхний график говорит, что первого июля двухтысячного года в парках побывало 430 миллионов человек. На самом деле эта цифра за год.
  • Рейтинг на втором графике ничего не говорит о посещаемости. Если парк поднялся на одно место, посещаемость могла вырасти на одного человека, или на миллион, или уменьшиться в два раза.

Скачал исходные данные и визуализировал их в Табло. Инструмент знаю плохо, поэтому за красотой не гнался. Цель — показать, за счёт каких парков выросла общая посещаемость.

Не до конца разобрался в терминах. Кажется, recreation visitors — с палатками, non-recreation — без.

Общая посещаемость:

Посещаемость отдельных парков:

Обрезал высокое изображение, полная версия на Табло

Стало видно, посещаемость растёт у разных парков понемножку.

Ещё вылезли артефакты. George Washington MEM P в 1993 году оккупировали «беспалаточные» туристы. А «палаточных» как будто перестали пускать вовсе:

National Capital Parks Combined в 1996 тоже странно ведут себя. Название намекает, что это не парк а сумма посещаемостей других парков.

2017   Makeover

Отзыв о курсе Ильи Бирмана

Илья Бирман ведёт курс «Пользовательский интерфейс и представление информации». Первая ступень школы стажёров включает его видео, но тут больше практики и можно приставать к Илье с вопросами.

Курс строится вокруг принципов проектирования интерфейса. Поначалу меня смущало, что они не укладываются в систему вроде таблицы Менделеева. Потом я понял, как это работает: Илья в отличие от меня делает всего одну версию макета и применяет к ней один принцип за другим. Становится всё кайфовее и кайфовее, это похоже на метод градиентного спуска. Очерёдность принципов не важна.

После курса я попробовал работать в таком «последовательном» режиме: версии макета больше не разрастаются фрактальным деревом, процесс стал линейным и приятным.

Я удивлён количеству хоткеев, которыми пользуется Илья. Например, он в фотошопе одним аккордом делает жирной растровую надпись на белом фоне:

m → Выделение → ⌘j → Alt+Shift+m → v → Стрелка вправо → ⌘e

У меня пропало ощущение супербыстрой работы на компе: по сравнению с Ильёй я тормоз.

Лайфхаки и подходы

  • Улучшать текущий макет наименьшим трудом, а не думать, как сделать его идеальным.
  • Перед улучшением интерфейса подумать, какая в нём главная проблема. Решить её. Повторить.
  • Чтобы быстрее разработать несколько экранов новой системы, начинаем с самого сложного из них. Потом на остальных используем готовые элементы. © Денис Клевакин, участник курса.
  • Общее важнее частного. Исключения не должны мешать правилам.
  • Новый макет можно планировать не от данных, а от вёрстки. Мол, в углу какой-то большой чёрной штуки не хватает. Если для неё нет данных — найти. © Михаил Нозик, на живых советах после курса.

Тезисы россыпью

  • Нули после запятой могут быть полезны. Например, их число может показать точность прибора: мол, гарантируем, что там нули.
  • Если в ячейке нет данных — это тоже полезная информация. Отсутствие данных обозначается серым тире.
  • «Итого» лучше выделять не жирностью а выравниванием, это экономит data ink:
  • Не показывать одно и то же несколькими способами, например, жирностью, выравниванием, цветом.
  • Когда приложение предлагает сохранить платёж как шаблон, оно нарушает принцип бесценности пользовательских данных. Не надо спрашивать, надо сохранять всё в лог, чтобы можно было потом повторить любой платёж.
  • Выбор до интерфейса — я выбираю тип билета или бензина, подходя к нужному терминалу.
  • В листалках надо делить элементы на страницы не по 20 штук, а осмысленно. Например, по датам.
  • В маке клёво поставить действия на активные углы:
  • и включить хлебные крошки в файндере:
  • Выделение в фотошопе и системную маковскую рамку скриншота можно перетаскивать с зажатым пробелом:

Практика

Надо было улучшить таблицу, график и форму, по полчаса на макет. Впервые дизайнил в таком цейтноте, приходилось включать мозг и оценивать, что важно, а что нет.

Таблица:

График:

Задание на день: интерфейс платёжного терминала. По горизонтали основной сценарий, рядом — вариации:


Не успел доделать нижний красный экран, Илья над ним долго издевался, пририсовал троллфейс в фотошопе.

Некоторые плагины для работы с текстом

Посмотрел описания нескольких скетч-плагинов и оценил их полезность для себя.

Полезные

  • Dynamic button — пригодится для кнопок и текста с подложками. Хотя, он не работает с символами, кажется.
  • Dynamic symbol button — как раз для символов и нужен. Мы его, оказывается, используем О_о
  • Jw Sketch Plugins — уже пользуюсь. Разбивает и склеивает строки текста

Бесполезные

  • Comma — мелкие улучшения в типографике: двойные пробелы убрать, пробелы после запятых поставить...
  • Efficiency — тут русский типограф, мощнее, чем у «Коммы». Есть хоткеи на трекинг, лидинг и спейсинг. Ещё рандомное смещение и изменение размеров, может пригодиться для визуализации.
  • Sketch text tools — загоняет текст в колонки, добавляет подложку из объекта, выравнивает по базовой линии и не только, но это через неудобное окошко: хоткеи не навесить.
  • Adjust button shape — неудобно копирует функционал символ-баттона.
  • Paster — Вставляет текст из буфера в текстовые элементы, построчно. Прикольная идея, но не придумал, где он может понадобиться. Кажется, быстрее редактировать в самом скетче. Хотя, иногда текст генерится автоматически, например, даты в екселе. Тогда в 100 раз быстрее будет. UPD: оказалось, текст разбивает не по строкам а по словам. Завёл баг на гитхабе :—(
2017   скетч

Ускорение работы в скетче: текст → строки

Плагин Jw sketch plugins делает каждую строчку текстового блока отдельным объектом. Обратно тоже склеивает. Очень удобно при работе с таблицами, когда надо поменять строки местами. Ещё клёво начинать работу над макетом, когда есть список элементов и ещё не ясно, как их расположить:

2017   скетч

Ускорение работы в скетче: горячие клавиши

Чтобы работать быстрее, надо назначать горячие клавиши частым действиям. Эта возможность встроена в макось. Можно ускорить и стандартные функции скетча и плагины:

Когда горячих клавиш много, управлять ими в настройках неудобно. Зато работа в скетче спорится

Часто непонятно, будет ли плагин полезным. Поэтому новые планины вызываю через ранер, а когда пойму, что айс, придумываю горячую клавишу.

2017   скетч
2017   скетч

Визуализация народного рейтинга банков

Лаборатория данных опубликовала тестовое задание, и я за него взялся.

На сайте Банков.ру есть таблица с народным рейтингом. Она ежедневно обновлялась 11 лет, но информация глубоко закопана: никто не станет прощёлкивать все страницы. Я хочу сделать эти данные видимыми, чтобы они помогли увидеть интересные истории и закономерности.

Проект сделаю для физических лиц.

Черновая визуализация

Первым делом строю график в гугльдоке:

В исходной таблице мало данных, но уже видны закономерности

Авангард давно в лидерах. У некоторых банков оценки выше, но существуют они недолго.

Сбор данных

В рейтинге Банков.ру нет данных об оценках разных продуктов, поэтому Лаборатория наполнила таблицу случайными значениями:

=RANDBETWEEN($B2*0,7*100;IF(($B2*1,3)>5;500;$B2*1,3*100))/100

Искать логику и закономерности в таких данных как минимум странно :-) Попробую найти недостающую информацию.

Спрашиваю техподдержку Банков.ру, почему переключатель продуктов не меняет рейтинг. Отвечают:

Добрый день! Эти переключатели добавляют в рейтинг столбец с информацией о количестве отзывов по данному виду продуктов. Рейтинг один и не пересчитывается от продукта к продукту, однако в планах на будущее этот функционал есть.

Нет так нет.

Лаборатория приготовила данные с точностью до года, а на Банках.ру их можно смотреть по дням. Хочется скачать более полную информацию и сравнить.

Ищу данные на Банках.ру. Смотрю, что дольше грузится при запросе:

Самая длинная зелёная полосочка — самый тяжёлый файл. Он-то нам и нужен

Открываю самый большой файл — ура, это и есть наш JSON. В нём данные о выбранной лиге в выбранный день.

Ограничусь «высшей лигой», то есть пятьюдесятью банками с наибольшим количеством зачтённых отзывов за последние 365 дней. Перебираю все недели с 2005 по 2017 гг., для каждой скачиваю `.json`.

./date-iterator.sh 01.01.2005 01.01.2017 | xargs -I '$' wget 'www.banki.ru/services/responses/?mode=first&date=$&json=1'

date-iterator.sh:

#!/bin/bash
currentDateTs=$(date -j -f "%d.%m.%Y" $1 "+%s")
endDateTs=$(date -j -f "%d.%m.%Y" $2 "+%s")
offset=86400*7

while [ "$currentDateTs" -le "$endDateTs" ]
do
  date=$(date -j -f "%s" $currentDateTs "+%d.%m.%Y")
  echo $date
  currentDateTs=$(($currentDateTs+$offset))
done

Склеиваю в один файл:

cat index* >> banki.json 

В виме удаляю лишнее. Делаю уменьшенную версию файла для быстрых тестов. Для этого оставляю только каждую сотую строку:

awk 'NR == 1 || NR % 100 == 0' banki.json > banki_s2.json

Готово!

Поиск историй и закономерностей

Придумываю, какие данные выбрать и как их показать.

Из подкаста Дата Сториз узнал про Табло, пробую визуализировать в нём.

Вот, например, взаимозависимость количества оценок по разным продуктам. Каждая точка — банк в момент времени. Видно, что по перекредитованию отзывов меньше, чем по другим продуктам.

Взаимозависимость количества оценок по разным продуктам

Хочется ещё проверить зависимость рейтинга и средней оценки от времени. Делаю черновую диаграмму из червяков. Видна линейная зависимость между величинами, но есть и отклонения. Например, рейтинг Совкомбанка в 2015 году растёт, хотя средняя оценка не меняется:

У Совкомбанка в 2015 году растёт рейтинг, но не средняя оценка

Это возможно, потому что рейтинг учитывает устаревание отзывов. Возможно, Совкомбанк в 2015 г. получил много свежих двоек и троек, которые не повлияли на среднюю оценку, но подняли рейтинг.

История не вырисовывается. Возвращаюсь к графику зависимости средней оценки от времени.

В графиках дыры: вертикальные белые полосы и разрывы отдельных графиков

В графиках дыры двух типов. Есть белые полосы, в эти периоды нет данных ни об одном банке. Даже на Банках.ру эти даты не работают:

Ещё есть пробелы в данных отдельных банков, например, Банка «Санкт-Петербург» (выделен чёрным). Эти дыры появляются, появляются, когда банк вылетает из высшей лиги. Информация о нём есть, но в других лигах.

Скачиваю оставшиеся лиги и «заклеиваю» разрывы.

Каждая лига грузится 10 минут, поэтому скачиваю параллельно

Из других лиг пришли новые банки, отфильтровываю их джаваскриптом.

Графики оценок

Хочу показать, как менялись оценки продуктов с течением времени. Для этого надо знать, как они вычисляются. Например, если общая оценка — среднее значение оценок по разным продуктам, то можно показать, как она из них складывается:

Вверху графики всех банков. Выбрали один — видим внизу оценки продуктов

В методике расчёта рейтинга о продуктах не говорится. Иду на сайт и пишу тестовый отзыв.

При написании отзыва на Банках.ру люди выбирают от 0 до 12 продуктов

Можно выбрать оценку от 1 до 5 и любое количество оцениваемых продуктов. Значит, оценки продуктов нельзя складывать между собой, стримграф будет бессмысленным. Нарисую линиями:

Показываю оценки продуктов линями, а не стримграфом

Динамика отзывов

Средняя оценка не поможет выбрать банк, если неизвестно, сколько человек проголосовало. Наплывы отзывов могут говорить косяках в работе банка. Строю графики количества отзывов:

Аномалия Ханты-Мансийского банка: под новый год он получил 2000 отзывов

Сразу вижу аномалии. Тач Банк в первую же неделю собрал 300 негативных отзывов. А Ханты-Мансийский банк Открытие под Новый год получил 2000 отзывов, что уменьшило его оценку на четверть балла.

У некоторых банков число отзывов иногда уменьшается. Или глюк, или читерство, или функционал, про который я не знаю. Спрашиваю у поддержки: «Я запутался и не могу разобраться в логике народного рейтинга. Буду благодарен, если поможете: что должно произойти, чтобы у банка уменьшилось количество полученных отзывов? Как, например, у ОТП Банка 13 июля 2016 г». Отвечают:

Летом 2016 года были разъединены ветки ОТП Банка и Тач Банка. В связи с этим отзывы по Тач Банку мигрировали из ветки ОТП.

О, как! Не задумывался о таком. Значит, и резкие приросты отзывов — не обязательно читерство. Здорово бы показать на графике, как банки разъединяются и сливаются, но в эту итерацию не успею.

Скачки графиков после «белых дыр» говорят о том, что сайт Банки.ру в это время работал и люди писали отзывы.

Если у банка мало отзывов, каждый новый резко меняет среднюю оценку, график ступенчатый. С ростом числа рецензий скачки исчезают и график становится плавным.

Выбор банковских продуктов

Мне, как представителю целевой аудитории, интересно знать оценки банков по вкладам. Спрашиваю у знакомого экономиста, какие продукты будет интересно сравнить. Получаю ответ:

Физлица оценивают? Тогда нужно будет выбрать те показатели, которые им интересны: кредиты, дебетовые карты и вклады. Активы обычному потребителю ничего не скажут

Класс, сравним по трём продуктам.

Улучшение графиков

Сотня графиков — слишком много. Они скачут и путаются, выходит неразбериха. Надо показать только некоторые, но тогда пропадёт полнота картины. Проблему решает «скользящее окно»: видны все графики сразу, жирным выделены лишь те, превьюшки которых видны на экране. Показал паре испытуемых, они
разобрались с этим нестандартным элементом управления без подсказок.

Клик по графику слева прокручивает правую часть экрана так, чтобы превьюшка банка попала в зону видимости.

Интерактивная визуализация народных оценок банков

Над проектом работал в гите, прохожусь по коммитам и собираю анимированную картинку меняющегося интерфейса:

Таймлапс изменений интерфейса

Выводы

Сама по себе средняя оценка не может быть критерием при выборе банка. У Земского Банка твёрдая пятёрка, но всего три отзыва. Если выбирать между Тинькофф Банком и Модульбанком, то лучше остановиться на Тинькоффе, хоть и оценка у него ниже. Оценить популярность банка можно по форме графика: если отзывов мало, линия будет ступенчатой:

У Почты Банка в начале 2013 года отзывов мало, график ступенчатый. В 2015 году их становится больше и ступеньки сглаживаются.

Важно знать не только среднюю оценку, но и количество оценок и их актуальность. Эту проблему решает рейтинг.

Рейтинг учитывает устаревание оценок, поэтому график плавнее, распределение ближе к гауссову

Такое ощущение, что алгоритм расчёта рейтинга со временем меняется. Со временем он «подтягивает» все банки к некоторому значению, но значение это от года к году разное. Хочется перепроверить.

Сумма средних оценок всех банков со временем уменьшается. Может быть, банки стали хуже, или люди критичнее, или Банки.ру изменили интерфейс написания отзыва. А ещё, возможно, банки сами ставят себе хорошие оценки. С ростом популярности Банков.ру сфабрикованные отзывы тонут в настоящих и оценки опускаются.

Динамика отзывов обо всех банках

Больше всего отзывов пишут перед новым годом. Наверное, все снимают деньги, сталкиваются с очередями и уставшими сотрудниками банков. А новогодние праздники — время затишья. У Тинькофф Банка новый год меньше влияет на наплывы отзывов. Предположу, что он справляется с нагрузкой лучше других.

ВТБ Банк Москвы получал не больше десяти отзывов в неделю. 28 мая 2016 года получил четыре тысячи

Резкие скачки количества отзывов говорят о перерасчёте голосов при слиянии или разделении банков.

Планы на будущее

Первым делом — исправить ошибки:

  • неправильные оценки по продуктам. Они должны показывать состояние банка на текущий день.
  • Нужные графики не выделяются, пока не поскроллишь.

Вторым делом — доработать интерфейс:

  • добавить возможность сравнить банки. Нажимаю на банк — он залипает и остаётся чёрным, даже когда я убираю с него мышку.
  • Расширить зону клика у графиков. По тонкой линии сложно попасть мышкой, надо добавить более широкую прозрачную подложку.
  • Сделать макет адаптивным, сейчас только на макбуке хорошо смотрится.
  • Показать крутилку, пока данные грузятся.
  • Добавить социальные кнопки.
  • Показать бегунки. Двигаю мышку по большому графику, на ней двигается бегунок с датой, оценкой и числом отзывов. В превьюшках при этом синхронно двигаются маленькие бегунки и обновляются оценки:
Бегунки подскажут, что толщина линии означает количество отзывов

Идеи на будущее:

  • заменить случайные значения оценок продуктов на настоящие. Я их нашёл, скачал, но ещё не распарсил.
  • Сделать рейтинги по продуктам, добавить переключатель для выбора нужного продукта.
  • Показать, сколько и каких оценок банк получил на каждой неделе. Для этого раскрасить червяков: красный пиксель — отзыв с одним баллом, зелёный — с пятью.
  • Показать разъединения и поглощения банков. Автоматизировать поиск таких банков сравнением приростов рейтинга.
  • Проверить правильность рейтинга Банков.ру, пересчитав оценки банков в рейтинг по их формуле. Сравнить графики.
  • Из текстов отзывов сделать динамическое облако тегов. Навёл мышку на банк и читаешь, что про него чаще пишут. Например, «очереди», «нет банкоматов» и пр.
  • Написать серверное приложение, которое будет регулярно скачивать свежие данные с Банков.ру.

Ещё можно сделать визуализацию, где атомы — отзывы, а не банки. Можно будет сравнить карты отзывов для разных банков и продуктов, могут обнаружиться любопытные истории и закономерности.

Демка на banki.dianov.org

2017  
Ctrl + ↓ Ранее