Flattern_array
liang kuang
August 8, 2017
Introduction
A lot of times, we receive time-related data in a table format and we want convert it into a simple data format with one column of datetime and the other as value. See this sample table:
| Year | Jan | Feb | Mar | Apr | May | Jun | … |
|---|---|---|---|---|---|---|---|
| 2005 | 5.3 | 5.4 | 5.2 | 5.1 | 5.0 | 5.0 | … |
| 2006 | 4.3 | 4.4 | 4.2 | 4.1 | 4.0 | 3.0 | … |
| 2007 | 3.3 | 4.4 | 4.2 | 3.1 | 4.0 | 3.0 | … |
Now we want to convert this dataset into another format which can be easier to visulize and convert to other data structure like xts or timeSeries object. The converted data will be like:
| Date | value |
|---|---|
| 2005-01 | 5.3 |
| 2005-02 | 5.4 |
| 2005-03 | 5.2 |
| 2005-04 | 5.1 |
| … | … |
Sample Data
sampleData <- read.csv('table_date.csv')
sampleData## Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
## 1 2005 5.3 5.4 5.2 5.2 5.1 5.0 5.0 4.9 5.0 5.0 5.0 4.9
## 2 2006 4.7 4.8 4.7 4.7 4.6 4.6 4.7 4.7 4.5 4.4 4.5 4.4
## 3 2007 4.6 4.5 4.4 4.5 4.4 4.6 4.7 4.6 4.7 4.7 4.7 5.0
## 4 2008 5.0 4.9 5.1 5.0 5.4 5.6 5.8 6.1 6.1 6.5 6.8 7.3
## 5 2009 7.8 8.3 8.7 9.0 9.4 9.5 9.5 9.6 9.8 10.0 9.9 9.9
## 6 2010 9.8 9.8 9.9 9.9 9.6 9.4 9.4 9.5 9.5 9.4 9.8 9.3
## 7 2011 9.1 9.0 9.0 9.1 9.0 9.1 9.0 9.0 9.0 8.8 8.6 8.5
## 8 2012 8.3 8.3 8.2 8.2 8.2 8.2 8.2 8.1 7.8 7.8 7.7 7.9
## 9 2013 8.0 7.7 7.5 7.6 7.5 7.5 7.3 7.3 7.2 7.2 6.9 6.7
## 10 2014 6.6 6.7 6.7 6.2 6.3 6.1 6.2 6.2 5.9 5.7 5.8 5.6
## 11 2015 5.7 5.5 5.4 5.4 5.5 5.3 5.2 5.1 5.0 5.0 5.0 5.0
## 12 2016 4.9 4.9 5.0 5.0 4.7 4.9 4.9 4.9 4.9 4.8 4.6 4.7
## 13 2017 4.8 4.7 4.5 4.4 4.3 4.4 4.3 NA NA NA NA NAMethods 1
Flatten it into a vector.
tableDataFlat <- as.vector(t(sampleData[1:13,2:13]))
dates <- seq.Date(as.Date('2005-01-01'),as.Date('2017-12-01'),'month')
newTS <- data.frame(dates=dates,value=tableDataFlat)
head(newTS)## dates value
## 1 2005-01-01 5.3
## 2 2005-02-01 5.4
## 3 2005-03-01 5.2
## 4 2005-04-01 5.2
## 5 2005-05-01 5.1
## 6 2005-06-01 5.0plot(newTS)
Method 2
Another method is to use c to convert it to a nemeric
tableDataFlat <- c(t(as.matrix(sampleData[1:13,2:13])))
newTS <- data.frame(dates=dates,value=tableDataFlat)
head(newTS)## dates value
## 1 2005-01-01 5.3
## 2 2005-02-01 5.4
## 3 2005-03-01 5.2
## 4 2005-04-01 5.2
## 5 2005-05-01 5.1
## 6 2005-06-01 5.0plot(newTS) ## convert it to xts object for easier data analysis
newTS.xts <- as.xts(newTS[,-1], order.by = newTS[,1])
plot(newTS.xts)
How to do it in Python
In python, it is even more simple. Flatten the data matrix by using numarray.flatten().