I have a time series data in long format which looks like as follows:
+======+==========+======+======+
| Name | Date | Val1 | Val2 |
+======+==========+======+======+
| A | 1/1/2018 | 1 | 2 |
+------+----------+------+------+
| B | 1/1/2018 | 2 | 3 |
+------+----------+------+------+
| C | 1/1/2018 | 3 | 4 |
+------+----------+------+------+
| D | 1/4/2018 | 4 | 5 |
+------+----------+------+------+
| A | 1/4/2018 | 5 | 6 |
+------+----------+------+------+
| B | 1/4/2018 | 6 | 7 |
+------+----------+------+------+
| C | 1/4/2018 | 7 | 8 |
+------+----------+------+------+
I need to convert the above data into wide format which like as follows:
+---+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+
| | Val1.1/1/2018 | Val2.1/1/2018 | Val1.1/2/2018 | Val2.1/2/2018 | Val1.1/3/2018 | Val2.1/3/2018 | Val1.1/4/2018 | Val2.1/4/2018 |
+---+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+
| A | 1 | 2 | NULL | NULL | NULL | NULL | 5 | 6 |
| B | 2 | 3 | NULL | NULL | NULL | NULL | 6 | 7 |
| C | 3 | 4 | NULL | NULL | NULL | NULL | 7 | 8 |
| D | NULL | NULL | NULL | NULL | NULL | NULL | 4 | 5 |
+---+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+
To achieve that I've followed the following steps
First I've converted my initial data set date column to date format and added dates ranging from 01/01/2018
to 01/04/2018
in long format since I am dealing with time series data, I would want dates 01/02/2018
and 01/03/2018
to be included in wide format table even though those columns would contain NaNs.
To achieve the above mentioned task I've used the following code:
df = pd.read_csv('data.csv')
df['Date'] = pd.to_datetime(df['Date'], format='%m/%d/%Y')
idx = pd.MultiIndex.from_product([df.Name.unique(), pd.date_range(df.Date.min(), df.Date.max())])
df = df.set_index(['Name','Date']).reindex(idx).reset_index().rename(columns = {'level_0':'Name', 'level_1':'Date'})
df.Date = df.Date.dt.strftime('%m/%d/%Y')
new_df = df.pivot('Name', 'Date', ['Val1', 'Val2'])
new_df.columns = new_df.columns.map('.'.join)
I think the above code is not optimized to deal with larger data set (1.2 millions rows). How could I go about optimizing this code?
The similar task done in R with the follwing code takes much lesser time:
library(dplyr)
library(tidyr) #complete
library(data.table) #dcast and setDT
df %>% mutate(Date=as.Date(Date,'%m/%d/%Y')) %>%
complete(Name, nesting(Date=full_seq(Date,1))) %>%
setDT(.) %>% dcast(Name ~ Date, value.var=c('Val2','Val1'))
Credits: Python code mentioned in this post is taken from here. R code mentioned in this post is taken from here.