1
$\begingroup$

I have a dataset of about 1k samples and I want to apply some unsuspervised techniques in order to clustering and visualization of these data.

The data can be interpreted as a table of a spreadsheet and unfortunately it doensn't have a very defined pattern of structure. The number of table lines varies, but not the columns.

The data is structured like this:

sample 1:

{
  "table1": {
    "column1": [
      "-",
      "-",
      "-"
    ],
    "column2": [
      "2017-04-16 10:00",
      "2017-04-16 10:00",
      "2017-04-16 10:00"
    ],
    "column3": [
      "-",
      "-",
      "-"
    ],
    "column4": [
      "name X",
      "name Y",
      "name Z"
    ],
    "column5": [
      "0",
      "0",
      "0"
    ],
  }
}


sample 2:

{
  "table1": {
    "column1": [
      "-",
      "-",
      "-",
      "-",
      "-",
      "-",
      "-",
      "-"
    ],
    "column2": [
      "2017-04-10 22:00",
      "2017-04-10 22:00",
      "2017-04-10 22:00",
      "2017-04-10 22:00",
      "2017-04-10 22:00",
      "2017-04-10 22:00",
      "2017-04-10 22:00",
      "2017-04-10 22:00"
    ],
    "column3": [
      "-",
      "-",
      "-",
      "-",
      "-",
      "-",
      "-",
      "-"
    ],
    "column4": [
      "name A",
      "name Z",
      "name B",
      "name X",
      "name C",
      "name D",
      "name E",
      "name F"
    ],
    "coumn5": [
      "",
      "",
      "3",
      "1",
      "0",
      "3",
      "0",
      "0"
    ]
  }
}

These samples comes from alarms generated by a systems that collects informations from a lot of nodes (these nodes are named as "name A", "name B"...). My objective is to transform these data into a matrix (n_samples x n_features) to apply clustering and visualization algorithms.

How can I work with these data for unsupervised training? Is padding a way forward for this problem? If so, how can I apply the padding on this case?

$\endgroup$
0

1 Answer 1

1
$\begingroup$

Whether or not padding is ppropriate really depends on the entire structure of your dataset, how relevant the different variables/columns are and also the type of model you want to run at the end.

Padding would be used, whereby you would have to fix the length of each sample (either to the length of the longest sample, or to a fixed length - longer samples would be trimmed or filtered somehow to fit into that length). Variables that are strings can be padded with empty strings, variables with number can be padded with zeros. There are however many other ways to pad, e.g. using the average of a numerical variable, or even model-based padding, padding with values that "make most sense" for filling gaps in that specific sample. Getting deep into it like that might more generally be called imputation, instead of padding - it is common in time series data, where gaps aren't always at one end of a sample.

Below I outline one approach to padding or standardizing the length of each sample. It it not specifically padding.

As you did not mention a programming language, I will give and code snippet in Python, but the same is easily achievable in other languages such as R and Julia.

The Approach

Based on the two examples you provide, it seems each example would be a calendar day, on which there are a variable number of observations.

There are also columns that are strings, and others are strings of numbers (e.g. column 5 in sample 2).

In time-series analysis in general, it is desirable to have a continuous frequency of data. That means have one day give one input. So my approach would be to make your data into a form that resembles a single input for each of the variables (i.e. columns) of each sample.

This is general approach, and you will have to try things out or do more research on how this would look in reality for your specific data at large.

Timestamps

I would use these as a kind of index, like the index in a Pandas DataFrame. One row = one timestamp. Multiple variables are then different columns.

Dealing with strings

I will assume that your dataset has a finite number of possible strings in each column. For example, that column 4 (holding names), will always hold a name from a given set. One could perform set(table2['column 4']) to see which values there are (removing duplicates). Or even:

# Gather a single list containing all strings in column 4 of all samples
all_names = []
[]    # list comprehension to loop

# Check how many strings there are
from collections import Counter
counter = Counter(table2['column4'])
print(len(counter))                 # see how many unique values exist
print(counter)                      # see how many times each string appears
print(counter.most_common(5))       # see the most common 5 strings

Now assuming this shows there is a finite number (more than likely the case), You could look into using a sparse representation of each sample (that means for each day). For example, if all the words in the entire dataset were: ['hi', 'hello', 'wasup', 'yo', 'bonjour'] (duplicates removed), then for one single sample with column 4 holding e.g. ['hi', 'hello', 'yo', 'hi'], your sparse representation for this sample would be: [2, 1, 0, 1, 0], because the sample has two 'hi', one 'hello', zero 'wasup' and so on. This sparse representation would then be your single input for column 4 for the timestamp (that single sample).

It might be worth looking into something like the DictVectorizer and CountVectorizer from Scikit-Learn.

Dealing with number columns

As I mentioned right at the beginning, you could pad these to a chosen length, perhaps matching the length of the string based representation above (or not!), depending on your final model. You can then pad the inputs with a value that makes sense for your model (see the kind of options I mentioned at the beginning of my answer). This should land you with, once again, a single vector for the given day, containing the information in the numerical column.

$\endgroup$
2
  • $\begingroup$ Great answer. I have some notes on your thoughts about the scenario. 1- I'm considering to take off the time information as it's not relevant for the analysis. 2 - Does the rows order matters on this case? I mean, if "name X" occurred on index 0 of the first sample, it HAS to appear on every index 0 of the next samples. If so, the "Dealing of strings" makes complete sense. I was planning to take this off too. Unfortunately I cannot share the complete data with you but there's more 3 columns like "column5" that are relevant for traning, the rest I was planning to get rid. Please check my edit. $\endgroup$
    – denisb411
    May 7, 2018 at 19:05
  • $\begingroup$ If you are working with strings in one column then you create a sparse representation, the order of the strings won't matter if you use something that counts the appearances of each string. $\endgroup$
    – n1k31t4
    May 8, 2018 at 9:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.