Создание графика, который группирует данные по дате и категории

Question

Я смотрю на файл CSV от Twilio, показывающий исходящие текстовые сообщения. У меня есть три службы обмена сообщениями, каждая с отдельным номером телефона. Каждая строка в отчете представляет сообщение и имеет метку времени и исходный номер.

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

Пример данных:

FromNumber  SentDate
2345678910  2018-01-01
5678910112  2018-01-01
9876543210  2018-01-02
5678910112  2018-01-02
5678910112  2018-01-02
2345678910  2018-01-02
9876543210  2018-01-02
9876543210  2018-01-03
2345678910  2018-01-03
9876543210  2018-01-03
5678910112  2018-01-03

Желаемый результат:

Я использую Excel 2016, если это имеет значение.

Answer 1

Excel будет намного проще, чем Calc для этого.

1. Преобразуйте ваши данные в таблицу данных Excel Insert > Table (не требуется, но это облегчает ведение данных и долгосрочное обновление).
2. Выбрав таблицу данных, вставьте сводную таблицу Insert > Pivot Table .
3. Настройте сводную таблицу:
   Rows = SentDate
   Столбцы = FromNumber
   Значения = Количество FromNumber (может по умолчанию на сумму, просто изменить через диалоговое окно Настройки поля значения).
4. Вставьте вашу диаграмму Insert > Charts > 2d Column Chart . По умолчанию это создаст сводную диаграмму, а не обычную диаграмму.
5. Формат по вкусу.

Answer 2

Я использую LibreOffice Calc, поэтому меню и варианты настроек немного отличаются, но это будет достаточно похоже, чтобы вы могли найти настройки в Excel. Прошло много времени с тех пор, как я занялся составлением графиков, поэтому, возможно, есть более прямые пути к этому решению, но я просто выбил быстрое и грязное решение.

Я использовал сводную таблицу для агрегирования данных:

(Отмените выбор итогов, перетащите FromNumber и SendDate в указанном порядке в поля строк, перетащите SendDate в поля данных, измените Sum на Count.) Это производит эту сводную таблицу:

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

В данных образца нет значения января для номера 987. Чтобы 987 баров не появлялись в неправильном месте, я просто создал дубликат таблицы и вставил нулевую запись для января:

Чтобы дублировать сводную таблицу, я просто заполнил первую ячейку =A2 и перетащил ее для заполнения. Затем добавлена фиктивная январская запись.

Выберите новую таблицу, вставьте диаграмму и выберите параметр диаграммы столбца. Диаграмма нуждается в некоторой корректировке на других вкладках настройки.

Под диапазоном данных укажите ряд данных в строках:

Ряд данных нуждается в некоторой очистке. Для каждого из телефонных номеров выберите Y-Values . В разделе « Range for Y Values выберите соответствующий диапазон значений. В разделе Categories выберите весь диапазон значений SendDate (весь столбец таблицы, исключая заголовок). После того, как вы укажете диапазон категорий для первого номера телефона, который должен применяться и к другим номерам, просто подтвердите.

Удалите все серии, помеченные как Row n , оставив только номера телефонов.

Это даст основной график:

Вы можете настроить местоположение легенды, интервалы оси Y, метки диаграммы и оси и другое форматирование.