How to convert a dataframe from long to wide, with values grouped by year in the index?

user_14122068 0
Viewed 208

The code below worked with the previous csv that I used, both csv's have the same amount of columns, and the columns have the same name.

Data for the csv that worked here

Data for csv that didnt here

What does this error mean? Why am I getting this error?

from pandas import read_csv
from pandas import DataFrame
from pandas import Grouper
from matplotlib import pyplot

series = read_csv('carringtonairtemp.csv', header=0, index_col=0, parse_dates=True, squeeze=True)

groups = series.groupby(Grouper(freq='A'))
years = DataFrame()

for name, group in groups:
    years[name.year] = group.values

years = years.T

pyplot.matshow(years, interpolation=None, aspect='auto')
pyplot.show()

Error

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-6-7173fcbe8c08> in <module>
      6 #     display(group.head())
      7 #     print(group.values[:10])
----> 8     years[name.year] = group.values

e:\Anaconda3\lib\site-packages\pandas\core\frame.py in __setitem__(self, key, value)
   3038         else:
   3039             # set column
-> 3040             self._set_item(key, value)
   3041 
   3042     def _setitem_slice(self, key: slice, value):

e:\Anaconda3\lib\site-packages\pandas\core\frame.py in _set_item(self, key, value)
   3114         """
   3115         self._ensure_valid_index(value)
-> 3116         value = self._sanitize_column(key, value)
   3117         NDFrame._set_item(self, key, value)
   3118 

e:\Anaconda3\lib\site-packages\pandas\core\frame.py in _sanitize_column(self, key, value, broadcast)
   3759 
   3760             # turn me into an ndarray
-> 3761             value = sanitize_index(value, self.index)
   3762             if not isinstance(value, (np.ndarray, Index)):
   3763                 if isinstance(value, list) and len(value) > 0:

e:\Anaconda3\lib\site-packages\pandas\core\internals\construction.py in sanitize_index(data, index)
    745     """
    746     if len(data) != len(index):
--> 747         raise ValueError(
    748             "Length of values "
    749             f"({len(data)}) "

ValueError: Length of values (365) does not match length of index (252)
pandas dataframe matplotlib arrays python
2 Answers
  • 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

user_77588040

You are getting this error because the groups do not have the same numbers of rows. So, firstly you add a column in the empty dataframe with 252 values and now the size of dataframe is 252. Then you are trying to add a column with 365 values which are a different size than 252. That's why you are getting this error. The datframe that the code worked had the same amount of values (364) per year(group). But now you have:

1990-12-31    252
1991-12-31    365
1992-12-31    366
...

For example, let's say we have this DataFrame:

   A
0  1
1  2
2  3

If we try to add a column with two values we will get this error:

df['B']=[1,2]

ValueError: Length of values does not match the length of the index

It is ok only if we add the same number of values:

df['B']=[1,2,3]
user_71834440
Related