Pandas多索引长格式转宽格式

Question

Pandas多索引长格式转宽格式

5

我有一个看起来像这样的数据框：

data.head()
Out[2]: 
        Area Area Id                  Variable Name Variable Id  Year  \
0  Argentina       9  Conservation agriculture area        4454  1982   
1  Argentina       9  Conservation agriculture area        4454  1987   
2  Argentina       9  Conservation agriculture area        4454  1992   
3  Argentina       9  Conservation agriculture area        4454  1997   
4  Argentina       9  Conservation agriculture area        4454  2002   
     Value Symbol Md  
0      2.0            
1      6.0            
2    500.0

我希望将数据透视，使得变量名成为列，地区和年份成为索引，值成为数值。在我看来，最直观的方法是使用：

data.pivot(index=['Area', 'Year'], columns='Variable Name', values='Value)

然而我收到了错误：

Traceback (most recent call last):
  File "C:\Users\patri\Miniconda3\lib\site-packages\IPython\core\interactiveshell.py", line 2862, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-4-4c786386b703>", line 1, in <module>
    pd.concat(data_list).pivot(index=['Area', 'Year'], columns='Variable Name', values='Value')
  File "C:\Users\patri\Miniconda3\lib\site-packages\pandas\core\frame.py", line 3853, in pivot
    return pivot(self, index=index, columns=columns, values=values)
  File "C:\Users\patri\Miniconda3\lib\site-packages\pandas\core\reshape\reshape.py", line 377, in pivot
    index=MultiIndex.from_arrays([index, self[columns]]))
  File "C:\Users\patri\Miniconda3\lib\site-packages\pandas\core\series.py", line 250, in __init__
    data = SingleBlockManager(data, index, fastpath=True)
  File "C:\Users\patri\Miniconda3\lib\site-packages\pandas\core\internals.py", line 4117, in __init__
    fastpath=True)
  File "C:\Users\patri\Miniconda3\lib\site-packages\pandas\core\internals.py", line 2719, in make_block
    return klass(values, ndim=ndim, fastpath=fastpath, placement=placement)
  File "C:\Users\patri\Miniconda3\lib\site-packages\pandas\core\internals.py", line 1844, in __init__
    placement=placement, **kwargs)
  File "C:\Users\patri\Miniconda3\lib\site-packages\pandas\core\internals.py", line 115, in __init__
    len(self.mgr_locs)))
ValueError: Wrong number of items passed 119611, placement implies 2

我该如何理解这个？我也尝试了另一种方式：

data.set_index(['Area', 'Variable Name', 'Year']).loc[:, 'Value'].unstack('Variable Name')

我尝试获取相同的结果，但是出现了错误：

Traceback (most recent call last):
  File "C:\Users\patri\Miniconda3\lib\site-packages\IPython\core\interactiveshell.py", line 2862, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-5-222325ea01e1>", line 1, in <module>
    pd.concat(data_list).set_index(['Area', 'Variable Name', 'Year']).loc[:, 'Value'].unstack('Variable Name')
  File "C:\Users\patri\Miniconda3\lib\site-packages\pandas\core\series.py", line 2028, in unstack
    return unstack(self, level, fill_value)
  File "C:\Users\patri\Miniconda3\lib\site-packages\pandas\core\reshape\reshape.py", line 458, in unstack
    fill_value=fill_value)
  File "C:\Users\patri\Miniconda3\lib\site-packages\pandas\core\reshape\reshape.py", line 110, in __init__
    self._make_selectors()
  File "C:\Users\patri\Miniconda3\lib\site-packages\pandas\core\reshape\reshape.py", line 148, in _make_selectors
    raise ValueError('Index contains duplicate entries, '
ValueError: Index contains duplicate entries, cannot reshape

数据有问题吗？我确认数据帧的任何一行中都没有重复的组合 Area，Variable Name和Year，所以我认为不应该有任何重复的条目，但我可能是错的。既然这两种方法都不起作用，我该如何从长格式转换为宽格式？我已经查看了这里和这里的答案，但它们都涉及到某种类型的聚合。我尝试使用pivot_table，像这样：

data.pivot_table(index=['Area', 'Year'], columns='Variable Name', values='Value')

但我认为正在进行某种聚合并且数据集中有很多缺失值，这导致了此错误：

Traceback (most recent call last):
  File "C:\Users\patri\Miniconda3\lib\site-packages\IPython\core\interactiveshell.py", line 2862, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-7-77b28d2f0dbb>", line 1, in <module>
    pd.concat(data_list).pivot_table(index=['Area', 'Year'], columns='Variable Name', values='Value')
  File "C:\Users\patri\Miniconda3\lib\site-packages\pandas\core\reshape\pivot.py", line 136, in pivot_table
    agged = grouped.agg(aggfunc)
  File "C:\Users\patri\Miniconda3\lib\site-packages\pandas\core\groupby.py", line 4036, in aggregate
    return super(DataFrameGroupBy, self).aggregate(arg, *args, **kwargs)
  File "C:\Users\patri\Miniconda3\lib\site-packages\pandas\core\groupby.py", line 3468, in aggregate
    result, how = self._aggregate(arg, _level=_level, *args, **kwargs)
  File "C:\Users\patri\Miniconda3\lib\site-packages\pandas\core\base.py", line 435, in _aggregate
    **kwargs), None
  File "C:\Users\patri\Miniconda3\lib\site-packages\pandas\core\base.py", line 391, in _try_aggregate_string_function
    return f(*args, **kwargs)
  File "C:\Users\patri\Miniconda3\lib\site-packages\pandas\core\groupby.py", line 1037, in mean
    return self._cython_agg_general('mean', **kwargs)
  File "C:\Users\patri\Miniconda3\lib\site-packages\pandas\core\groupby.py", line 3354, in _cython_agg_general
    how, alt=alt, numeric_only=numeric_only)
  File "C:\Users\patri\Miniconda3\lib\site-packages\pandas\core\groupby.py", line 3425, in _cython_agg_blocks
    raise DataError('No numeric types to aggregate')
pandas.core.base.DataError: No numeric types to aggregate

- pbreach

data.pivot_table(index=['Area', 'Year'], columns='Variable Name', values='Value) 是怎样的？ - jezrael

我忘了提到我也尝试过这种方法，但出现了另一个错误。我认为这可能与数据集中的缺失值做某种类型的聚合有关。正在编辑问题。 - pbreach

1

你的数据有问题，不是数值类型。 - jezrael

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- jezrael · Accepted Answer

我认为您需要首先将列Value转换为数值，然后使用默认聚合函数mean进行pivot_table：

#if all float data saved as strings
data['Value'] = data['Value'].astype(float)
#if some bad data like strings and first method return value error
data['Value'] = pd.to_numeric(data['Value'], errors='coerce')

data.pivot_table(index=['Area', 'Year'], columns='Variable Name', values='Value')

或：

data.groupby(['Area', 'Variable Name', 'Year'])[ 'Value'].mean().unstack('Variable Name')