Pivot/Transpose long data to a result that has Months as columns and Years as rows

Question

I was wondering if there is a nice clean way in python/pandas to pivot/transpose a table of long data to a result that has months January to December as columns and years as rows, similar to the image below.

I've started off by adding a 'month' and a 'year' column to a really good sample dataset but now I'm not sure on the most efficient way to tackle this task.

import pandas as pd

data = pd.read_csv("https://fred.stlouisfed.org/graph/fredgraph.csv?bgcolor=%23e1e9f0&chart_type=line&drp=0&fo=open%20sans&graph_bgcolor=%23ffffff&height=450&mode=fred&recession_bars=on&txtcolor=%23444444&ts=12&tts=12&width=1168&nt=0&thu=0&trc=0&show_legend=yes&show_axis_titles=yes&show_tooltip=yes&id=FEDFUNDS&scale=left&cosd=1954-07-01&coed=2019-06-01&line_color=%234572a7&link_values=false&line_style=solid&mark_type=none&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Monthly&fam=avg&fgst=lin&fgsnd=2009-06-01&line_index=1&transformation=lin&vintage_date=2019-07-08&revision_date=2019-07-08&nd=1954-07-01")

data['DATE'] = pd.to_datetime(data['DATE'], format='%Y-%m-%d')
data['month'] = pd.DatetimeIndex(data['DATE']).month
data['year'] = pd.DatetimeIndex(data['DATE']).year

Any ideas please

jezrael · Accepted Answer · 2019-07-08T05:40:51.720

Simpliest is DataFrame.pivot if month names are not necessary:

data = data.pivot('year','month','FEDFUNDS')

Months in expected order is possible create by ordered categorical with Series.dt.month_name:

cats = ['January','February','March','April','May','June',
        'July','August','September','October','November','December']
data['month'] = pd.CategoricalIndex(data['DATE'].dt.month_name(),ordered=True, categories=cats)
data['year'] = data['DATE'].dt.year

data = data.pivot('year','month','FEDFUNDS')

Or rename columns names by dict created by enumerate:

cats = ['January','February','March','April','May','June',
        'July','August','September','October','November','December']
data['month'] = data['DATE'].dt.month
data['year'] = data['DATE'].dt.year

data = data.pivot('year','month','FEDFUNDS').rename(columns=dict(enumerate(cats, 1)))

Pivot/Transpose long data to a result that has Months as columns and Years as rows

1 Answers1