Mathematica в действии, блог Осипова Романа

Импорт данных из MS Excel и их обработка на примере анализа количества участников сообщества “Wolfram Mathematica® || Русскоязычная поддержка” ВКонтакте

Нажмите, чтобы скачать данный пост в виде ноутбука Mathematica...

Нажмите, чтобы скачать файл MS Excel, содержащий данные...

Общее количество использованных в посте встроенных функций или символов: 70

Список имен используемых встроенных функций и символов в порядке их появления в коде:
Short | Import | Part ([[…]]) | Rest | Span (;;) | All | List ({...}) | ReplaceAll (/.) | Rule (->, ->) | Pattern (:) | Blank (_) | If | Equal (==) | Times (*, ×) | Split | Function (&) | Slot (#) | BlankSequence (__) | Plus (+) | Set (=) | Transpose | CompoundExpression (;) | Accumulate | Reverse | Prepend | Map (/@) | DateList | DatePlus | DateListPlot | Joined | True | AxesOrigin | ImageSize | Frame | False | PlotStyle | AbsoluteThickness | DateTicksFormat | GridLines | Automatic | Range | GridLinesStyle | Dotted | Dashed | Thick | TicksStyle | Directive | Bold | ColorFunction | Which | LessEqual (<=, ≤) | Blue | Red | Darker | Green | ColorFunctionScaling | AxesStyle | PlotMarkers | Style | White | AbsoluteTime | Evaluate | Fit | SetDelayed (:=) | Round | RuleDelayed (:>, :->) | Show | Black | DateString | Solve

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

Расскажу об этой задаче на примере документов формата .xls, которые могут сгенерировать для своих нужд администраторы сообществ ВКонтакте.

Получение файла с набором данных

У администратора сообщества есть доступ к статистике сообщества, нажав на соответствующую кнопку, перейдем на соответствующую страницу.

Нажав на этой странице на кнопку выгрузки статистики, получим окно для настройки параметров выгружаемых данных.

Для моего поста возьму данные о количестве участников сообщества в период времени с 1 сентября по 19 ноября 2012 г.

После этого, в указанной директории появится файл MS Excel, содержащий необходимые данные:

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

Причем, что самое интересное, совершенно нет никакой информации о некотором “начальном числе” участников сообщества, или конечном, т. е. о значении этого числа 1 сентября или 19 ноября, а значит это число нужно брать самостоятельно, оно равно в нашем случае 768:

Импорт данных в Mathematica

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

ВНИМАНИЕ! Получить путь к файлу проще всего просто выделив его и нажав затем CTRL+C, после этого поставив курсор в документе Mathematica в нужном месте и нажав CTRL+V вы получите готовый путь к файлу в виде строки, аналогичной той, что вы видите ниже в теле функции Import.

Импортируем данные из файла .xls (здесь применена функция Short, для того, чтобы показать сокращенную версию результата, который был бы получен. Здесь объектом <<98>> (функция  Skeleton) обозначена скрытая последовательность из 98 объектов.)

In[1]:=

Short[Import["I:\\Wolfram\\MathematicaInAction\\post_2\\vkontakte_group1172233_2012-09-01_2012-11-19.xls"], 3]

Out[1]//Short=

Из структуры полученных данных следует, что нам следует взять первый элемент из полученных после экспорта данных, т. к. сейчас они имеют структуру { { {...}, {...}, ...} } (она такова, потому что после импорта в каждый из “больших” списков представляет собой отдельный лист импортируемого документа Excel, а в нашем файле он всего один, поэтому список данных, соответствующий этому листу вложен во внешний список, который соответствует списку всех листов книги Excel):

In[2]:=

Short @ (Import["I:\\Wolfram\\MathematicaInAction\\post_2\\vkontakte_group1172233_2012-09-01_2012-11-19.xls"][[1]])

Out[2]//Short=

Также нам не понадобится первый элемент

{Дата, Критерий, Парам. №1, Парам. №2, Значение}

из полученного набора данных.

С помощью функции Rest возьмем все элементы списка кроме первого (аналогичная функция, убирающая последний элемент — Most):

In[3]:=

Short @ (Rest[Import["I:\\Wolfram\\MathematicaInAction\\post_2\\vkontakte_group1172233_2012-09-01_2012-11-19.xls"][[1]]])

Out[3]//Short=

Уберем из каждого списка

{18.11.2012,members,Новые участники,,3.}

ненужные 2-й и 4-й элементы, т. е., иначе говоря, возьмем только 1-й, 3-й и 5-й. Сделать это можно с помощью синтаксиса:

список[[;;, {1, 3, 5}]]

Здесь используется функция Part, которая гораздо чаще употребляется в виде [[...]]. Внутри квадратных скобок указывается какие элементы и с каких уровней (уровни отделяются запятой) брать. В нашем случае конструкция  ;; (вместо этого объекта можно использовать символ All) задает все списки первого уровня, а после запятой указано какие элементы брать в каждом из этих списков. В итоге получаем список нужных данных:

In[4]:=

Short @ (Rest[Import["I:\\Wolfram\\MathematicaInAction\\post_2\\vkontakte_group1172233_2012-09-01_2012-11-19.xls"][[1]]][[;;, {1, 3, 5}]])

Out[4]//Short=

Теперь изменим знаки последних цифр стоящих на 3-м месте в каждом списке в зависимости от второго элемента. Это можно сделать с помощью правила замены:

In[5]:=

Short @ Rest[Import["I:\\Wolfram\\MathematicaInAction\\post_2\\vkontakte_group1172233_2012-09-01_2012-11-19.xls"][[1]]][[;;, {1, 3, 5}]]/.{x_, y_, z_} -> {x, If[y == "Новые участники", z, -z]}

Out[5]//Short=

Теперь узнаем на какое число изменилось количество участников сообщества за конкретный день, вместо существующих на данный момент двух цифр, будь то:

{{“18.11.2012”, 3.}, {“18.11.2012”, -1.}, ....}

Для начала разобьем с помощью функции Split наш список на пары одинаковых дат:

In[6]:=

Short @ Split[Rest[Import["I:\\Wolfram\\MathematicaInAction\\post_2\\vkontakte_group1172233_2012-09-01_2012-11-19.xls"][[1]]][[;;, {1, 3, 5}]]/.{x_, y_, z_} -> {x, If[y == "Новые участники", z, -z]}, #1[[1]] == #2[[1]] &]

Out[6]//Short=

Теперь преобразуем полученный набор списков с помощью двух правил замены:

Получим:

In[7]:=

Short @ Split[Rest[Import["I:\\Wolfram\\MathematicaInAction\\post_2\\vkontakte_group1172233_2012-09-01_2012-11-19.xls"][[1]]][[;;, {1, 3, 5}]]/.{x_, y_, z_} -> {x, If[y == "Новые участники", z, -z]}, #1[[1]] == #2[[1]] &]/.{{{x__}} -> {x}, {{x_, y_}, {x_, z_}} -> {x, y + z}}

Out[7]//Short=

Разобьем этот список в два списка - дат и значений “прироста” численности сообщества:

In[8]:=

{days, increments} = Transpose[Split[Rest[Import["I:\\Wolfram\\MathematicaInAction\\post_2\\vkontakte_group1172233_2012-09-01_2012-11-19.xls"][[1]]][[;;, {1, 3, 5}]]/.{x_, y_, z_} -> {x, If[y == "Новые участники", z, -z]}, #1[[1]] == #2[[1]] &]/.{{{x__}} -> {x}, {{x_, y_}, {x_, z_}} -> {x, y + z}}]

Out[8]=

Зная, что в сообществе было 768 человек 19.11.2012 г., перейдем от списка значений “прироста” численности сообщества по дням increments к списку значений количества участников сообщества.

In[9]:=

Out[10]=

Здесь была использована функция Accumulate, принцип работы которой проще всего понять исходя из равентсва:

Accumulate[{a, b, c, d}] == {a, a + b, a + b + c, a + b + c + d}

Итак, мы получили список numberOfParticipants, содержащий значения количества участников сообщества.

Преобразуем списки days и numberOfParticipants снова в список точек вида {дата, количество}:

In[11]:=

Short[preData = Transpose[{days, numberOfParticipants}]]

Out[11]//Short=

Добавим к полученному списку в начало точку, соответствующую дню 19.11.2012. Осуществить это можно несколькими способами, один из них - использовать функцию  Append. При этом саму дату 19.11.2012 получим с помощью функции DatePlus. Также каждую дату переведем в формат, с которым просто работать в Mathematica с помощью функции DateList (дата в виде списка {год, месяц, день, час, минута, секунда}). При этом четко укажем то, как трактовать наши строки вида "11.11.2012" (так как могут возникнуть понятные разногласия трактовки чисел дня и месяца в такой записи). А также изменим порядок следования элементов в списке с помощью функции Reverse, для того чтобы от элемента к элементу дата возрастала.

In[12]:=

Short[endData = Reverse @ Prepend[{DateList[{#[[1]], {"Day", ".", "Month", ".", "Year"}}], #[[2]]} &/@preData, {DateList[DatePlus[preData[[1, 1]], 1]], total}]]

Out[12]//Short=

Обработка импортированных данных

Построение графика зависимости количества участников сообщества от времени

Теперь мы получили набор данных, которому присвоено имя endData.

Построим с помощью функции DateListPlot график зависимости количества участников сообщества “Wolfram Mathematica® || Русскоязычная поддержка” от времени.

In[13]:=

DateListPlot[endData]

Out[13]=

Теперь мы можем настроить полученный график. Для этого применим набор опций:

TicksStyle -> Directive[18, Bold]

Получим:

In[14]:=

graphic = DateListPlot[endData, Joined->True, AxesOrigin-> {"August 28, 2012", 580}, ImageSize->600 , Frame->False, PlotStyle->AbsoluteThickness[6], DateTicksFormat-> {"MonthNameShort", "\n", "YearShort"}, GridLines-> {Automatic, Range[200, 800, 100]}, GridLinesStyle-> {{Dotted}, {Dashed, Thick}}, TicksStyle->Directive[18, Bold], ColorFunction->Function[{x, y}, Which[y≤600, Blue, y≤700, Red, y≤800, Darker @ Green]], ColorFunctionScaling->False, AxesStyle->Thick, PlotMarkers->Style[●, White, 6]]

Out[14]=

Поиск зависимости количества участников сообщества от времени

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

Для начала переведем даты из формата {год, месяц, день, час, минута, секунда} в абсолютное значение времени с помощью функции AbsoluteTime, которая выдает количество секунд, прошедших с 1 января 1900 г.

In[15]:=

Short[endAbsoluteData = {AbsoluteTime[#[[1]]], #[[2]]} &/@endData]

Out[15]//Short=

Эта функция позволяет работать с датой как с обычным числом (это крайне удобно, например, если нужно найти зависимость какого-то параметра от времени), что гораздо удобнее чем с датой в виде списка {год, месяц, день, час, минута, секунда}. Это полностью аналогично представлению угла в радианах, а не в градусах.

Теперь с помощью функции Fit найдем линейную зависимость, описывающую наши данные и представим ее в виде чистой функции Function для удобства работы в дальнейшем (функция Evaluate применяется в теле чистой функции для того чтобы вычислить ее тело прежде чем собственно задать функцию):

In[16]:=

model = Function[{x}, Evaluate @ Fit[endAbsoluteData, {1, x}, x]]

Out[16]=

Теперь построим функцию, которая бы вычисляла значение количества участников сообщества, предсказанное моделью по дате, заданной в виде строки или списка (причем будем округлять до целого предсказанное значение с помощью функции Round, чтобы не получить конструкцию вроде “3/2 землекопа”):

In[17]:=

modelF[date_] := Round[model[AbsoluteTime[date]]]

Найдем набор точек, вычисленных согласно нашей модели:

In[18]:=

endModelData = (endData/.{x_, y_} :> {x, modelF[x]}) ;

Теперь мы можем построить наши “экспериментальные” данные и модельную зависимость на одном графике.

In[19]:=

Show @ {graphic, DateListPlot[endModelData, Joined->True, AxesOrigin-> {"August 28, 2012", 580}, ImageSize->600 , Frame->False, PlotStyle-> {AbsoluteThickness[3], Black}, DateTicksFormat-> {"MonthNameShort", "\n", "YearShort"}, GridLines-> {Automatic, Range[200, 800, 100]}, GridLinesStyle-> {{Dotted}, {Dashed, Thick}}, TicksStyle->Directive[18, Bold], ColorFunctionScaling->False, AxesStyle->Thick]}

Out[19]=

Использование найденной зависимости

Найдем тот момент времени, когда количество членов в сообществе составило бы 1000 человек, если динамика развития сообщества останется прежней. Это можно сделать с помощью функций Solve (поиск решения уравнения model[x]==1000) и  DateString (перевод полученного решения - абсолютного момента времени - в стандартное представление):

In[20]:=

DateString[x/.Solve[model[x] == 1000, x][[1]]]

Out[20]=

Блог принадлежит “Русскоязычной поддержке Wolfram Mathematica"©
При любом использовании материалов блога, ссылка на блог обязательна.
Создано с помощью Wolfram Mathematica 8