Pandas Data Frame Convert Data in Long format to Wide format for specific Date Range

Question

I am trying to convert a time series data from long to wide format. The data is given below 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 |
+------+----------+------+------+

My end goal is to create a pivot table for a date range of 01/01/2018 to 01/04/2018. Since there is no value at dates 01/02/2018 or 01/03/2018 I would expect them to be filled with NaN. For further simplicity this is what my final table would look like:

+---+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+
|   | 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 |
+---+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+

There are two steps as per my understanding to get above table. First is to fill data in long format with dates not present in range between 01/01/2018 to 01/04/2018 i.e. is 01/02/2018 and 01/03/2018.

Second and the final step would be to pivot data in wide format.

To achieve the first step I referred to this post. As per the answer if there are similar dates in multiple rows df.reindex(date_range)would raise following error, ValueError: cannot reindex from a duplicate axis which is true and to overcome that I followed the following piece of code.

df['Date'] =  pd.to_datetime(df['Date'], format='%m/%d/%Y')
df.set_index('Date', inplace = True)

date_range = pd.date_range('2018-01-01', '2018-01-04', freq='D')
df = df.loc(date_range)

The above code goves me the following error:

TypeError: unhashable type: 'DatetimeIndex'

I resolved the above issue using this line of code

df = df.loc[date_range,:]

While the I am able to get the desired long format but Python gives a warning as follows:

Passing list-likes to .loc or [] with any missing label will raise
KeyError in the future, you can use .reindex() as an alternative.

The above warning, I think suggests that my way of achieving the long format table with missing dates is not correct, is that correct? If yes, than how should I go about doing it? Moreover, how would I reach to to wide format table which I want as my final table?

Edit: I've reached to the pivot table containing dates only '01/01/2018' and '01/04/2018'. Following is the piece of code.

df1 = df.pivot_table(index='Name', columns='Date', aggfunc='sum')

score 1 · Accepted Answer · answered Feb 23 '19 at 18:20

1

First reindex the Dataframe to add missing dates. Then pivot and combine columns.

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)

    Val1.01/01/2018 Val1.01/02/2018 Val1.01/03/2018 Val1.01/04/2018 Val2.01/01/2018 Val2.01/02/2018 Val2.01/03/2018 Val2.01/04/2018
Name                                
A   1.0             NaN             NaN              5.0            2.0 NaN NaN 6.0
B   2.0             NaN             NaN              6.0            3.0 NaN NaN 7.0
C   3.0             NaN             NaN              7.0            4.0 NaN NaN 8.0
D   NaN             NaN             NaN              4.0            NaN NaN NaN 5.0

answered Feb 23 '19 at 18:20

Vaishali

37,545
5
58
86

At line `new_df = df.pivot('Name', 'Date', ['Val1', 'Val2'])` I am getting an error, `Exception: Data must be 1-dimensional` – Furqan Hashim Feb 23 '19 at 18:27
Do you have more columns in your dataset? – Vaishali Feb 23 '19 at 22:13
No. Following is the result from `df.columns` ,`Index(['Name', 'Date', 'Val1', 'Val2'], dtype='object')` – Furqan Hashim Feb 23 '19 at 22:42
can't reproduce the error, can you provide a larger sample df? – Vaishali Feb 24 '19 at 00:42
After changing my python version from 3.5.x to 3.6.5 the error is resolved. – Furqan Hashim Feb 24 '19 at 12:47
New table only contains NaNs – Furqan Hashim Feb 24 '19 at 12:50
Figured out that the data file I was reading didn't had date parsed, after parsing them and running your code I had got to the table I wanted. – Furqan Hashim Feb 24 '19 at 13:41

Pandas Data Frame Convert Data in Long format to Wide format for specific Date Range

1 Answers1