How to convert a dataframe from long to wide, with values grouped by year in the index?
- The issue with iteratively creating the dataframe in the manner shown, is it requires the new column to match the length of the existing dataframe,
year
, index.
- In the smaller dataset, all the years are 365 days without missing days.
- The larger dataset has mixed length years of 365 and 366 days and there is missing data from 1990 and 2020, which is causing
ValueError: Length of values (365) does not match length of index (252)
.
- Following is a more succinct script, which achieves the desired dataframe shape, and plot.
- This implementation doesn't have issues with the unequal data lengths.
import pandas as pd
import matplotlib.pyplot as plt
# links to data
url1 = 'https://raw.githubusercontent.com/trenton3983/stack_overflow/master/data/so_data/2020-09-19%20%2063975678/daily-min-temperatures.csv'
url2 = 'https://raw.githubusercontent.com/trenton3983/stack_overflow/master/data/so_data/2020-09-19%20%2063975678/carringtonairtemp.csv'
# load the data into a DataFrame, not a Series
# parse the dates, and set them as the index
df1 = pd.read_csv(url1, parse_dates=['Date'], index_col=['Date'])
df2 = pd.read_csv(url2, parse_dates=['Date'], index_col=['Date'])
# groupby year and aggregate Temp into a list
dfg1 = df1.groupby(df1.index.year).agg({'Temp': list})
dfg2 = df2.groupby(df2.index.year).agg({'Temp': list})
# create a wide format dataframe with all the temp data expanded
df1_wide = pd.DataFrame(dfg1.Temp.tolist(), index=dfg1.index)
df2_wide = pd.DataFrame(dfg2.Temp.tolist(), index=dfg2.index)
# plot
fig, (ax1, ax2) = plt.subplots(ncols=2, figsize=(10, 10))
ax1.matshow(df1_wide, interpolation=None, aspect='auto')
ax2.matshow(df2_wide, interpolation=None, aspect='auto')
![enter image description here](https://i.stack.imgur.com/PXmHb.png)