We start by answering the first question:
Question 1
Why do I get
ValueError: Index contains duplicate entries, cannot reshape
This occurs because pandas is attempting to reindex either a columns or index object with duplicate entries. There are varying methods to use that can perform a pivot. Some of them are not well suited to when there are duplicates of the keys in which it is being asked to pivot on. For example. Consider pd.DataFrame.pivot. I know there are duplicate entries that share the row and col values:
df.duplicated(['row', 'col']).any()
True
So when I pivot using
df.pivot(index='row', columns='col', values='val0')
I get the error mentioned above. In fact, I get the same error when I try to perform the same task with:
df.set_index(['row', 'col'])['val0'].unstack()
Here is a list of idioms we can use to pivot
pd.DataFrame.groupby+pd.DataFrame.unstack- Good general approach for doing just about any type of pivot
- You specify all columns that will constitute the pivoted row levels and column levels in one group by. You follow that by selecting the remaining columns you want to aggregate and the function(s) you want to perform the aggregation. Finally, you
unstackthe levels that you want to be in the column index.
pd.DataFrame.pivot_table- A glorified version of
groupbywith more intuitive API. For many people, this is the preferred approach. And is the intended approach by the developers. - Specify row level, column levels, values to be aggregated, and function(s) to perform aggregations.
- A glorified version of
pd.DataFrame.set_index+pd.DataFrame.unstack- Convenient and intuitive for some (myself included). Cannot handle duplicate grouped keys.
- Similar to the
groupbyparadigm, we specify all columns that will eventually be either row or column levels and set those to be the index. We thenunstackthe levels we want in the columns. If either the remaining index levels or column levels are not unique, this method will fail.
pd.DataFrame.pivot- Very similar to
set_indexin that it shares the duplicate key limitation. The API is very limited as well. It only takes scalar values forindex,columns,values. - Similar to the
pivot_tablemethod in that we select rows, columns, and values on which to pivot. However, we cannot aggregate and if either rows or columns are not unique, this method will fail.
- Very similar to
pd.crosstab- This a specialized version of
pivot_tableand in it's purest form is the most intuitive way to perform several tasks.
- This a specialized version of
pd.factorize+np.bincount- This is a highly advanced technique that is very obscure but is very fast. It cannot be used in all circumstances, but when it can be used and you are comfortable using it, you will reap the performance rewards.
pd.get_dummies+pd.DataFrame.dot- I use this for cleverly performing cross tabulation.
Examples
What I'm going to do for each subsequent answer and question is to answer it using pd.DataFrame.pivot_table. Then I'll provide alternatives to perform the same task.
Question 3
How do I pivot
dfsuch that thecolvalues are columns,rowvalues are the index, mean ofval0are the values, and missing values are0?
pd.DataFrame.pivot_tablefill_valueis not set by default. I tend to set it appropriately. In this case I set it to0. Notice I skipped question 2 as it's the same as this answer without thefill_valueaggfunc='mean'is the default and I didn't have to set it. I included it to be explicit.df.pivot_table( values='val0', index='row', columns='col', fill_value=0, aggfunc='mean') col col0 col1 col2 col3 col4 row row0 0.77 0.605 0.000 0.860 0.65 row2 0.13 0.000 0.395 0.500 0.25 row3 0.00 0.310 0.000 0.545 0.00 row4 0.00 0.100 0.395 0.760 0.24
pd.DataFrame.groupbydf.groupby(['row', 'col'])['val0'].mean().unstack(fill_value=0)pd.crosstabpd.crosstab( index=df['row'], columns=df['col'], values=df['val0'], aggfunc='mean').fillna(0)
Question 4
Can I get something other than
mean, like maybesum?
pd.DataFrame.pivot_tabledf.pivot_table( values='val0', index='row', columns='col', fill_value=0, aggfunc='sum') col col0 col1 col2 col3 col4 row row0 0.77 1.21 0.00 0.86 0.65 row2 0.13 0.00 0.79 0.50 0.50 row3 0.00 0.31 0.00 1.09 0.00 row4 0.00 0.10 0.79 1.52 0.24pd.DataFrame.groupbydf.groupby(['row', 'col'])['val0'].sum().unstack(fill_value=0)pd.crosstabpd.crosstab( index=df['row'], columns=df['col'], values=df['val0'], aggfunc='sum').fillna(0)
Question 5
Can I do more that one aggregation at a time?
Notice that for pivot_table and crosstab I needed to pass list of callables. On the other hand, groupby.agg is able to take strings for a limited number of special functions. groupby.agg would also have taken the same callables we passed to the others, but it is often more efficient to leverage the string function names as there are efficiencies to be gained.
pd.DataFrame.pivot_tabledf.pivot_table( values='val0', index='row', columns='col', fill_value=0, aggfunc=[np.size, np.mean]) size mean col col0 col1 col2 col3 col4 col0 col1 col2 col3 col4 row row0 1 2 0 1 1 0.77 0.605 0.000 0.860 0.65 row2 1 0 2 1 2 0.13 0.000 0.395 0.500 0.25 row3 0 1 0 2 0 0.00 0.310 0.000 0.545 0.00 row4 0 1 2 2 1 0.00 0.100 0.395 0.760 0.24pd.DataFrame.groupbydf.groupby(['row', 'col'])['val0'].agg(['size', 'mean']).unstack(fill_value=0)pd.crosstabpd.crosstab( index=df['row'], columns=df['col'], values=df['val0'], aggfunc=[np.size, np.mean]).fillna(0, downcast='infer')
Question 6
Can I aggregate over multiple value columns?
pd.DataFrame.pivot_tablewe passvalues=['val0', 'val1']but we could've left that off completelydf.pivot_table( values=['val0', 'val1'], index='row', columns='col', fill_value=0, aggfunc='mean') val0 val1 col col0 col1 col2 col3 col4 col0 col1 col2 col3 col4 row row0 0.77 0.605 0.000 0.860 0.65 0.01 0.745 0.00 0.010 0.02 row2 0.13 0.000 0.395 0.500 0.25 0.45 0.000 0.34 0.440 0.79 row3 0.00 0.310 0.000 0.545 0.00 0.00 0.230 0.00 0.075 0.00 row4 0.00 0.100 0.395 0.760 0.24 0.00 0.070 0.42 0.300 0.46pd.DataFrame.groupbydf.groupby(['row', 'col'])['val0', 'val1'].mean().unstack(fill_value=0)
Question 7
Can Subdivide by multiple columns?
pd.DataFrame.pivot_tabledf.pivot_table( values='val0', index='row', columns=['item', 'col'], fill_value=0, aggfunc='mean') item item0 item1 item2 col col2 col3 col4 col0 col1 col2 col3 col4 col0 col1 col3 col4 row row0 0.00 0.00 0.00 0.77 0.00 0.00 0.00 0.00 0.00 0.605 0.86 0.65 row2 0.35 0.00 0.37 0.00 0.00 0.44 0.00 0.00 0.13 0.000 0.50 0.13 row3 0.00 0.00 0.00 0.00 0.31 0.00 0.81 0.00 0.00 0.000 0.28 0.00 row4 0.15 0.64 0.00 0.00 0.10 0.64 0.88 0.24 0.00 0.000 0.00 0.00pd.DataFrame.groupbydf.groupby( ['row', 'item', 'col'] )['val0'].mean().unstack(['item', 'col']).fillna(0).sort_index(1)
Question 8
Can Subdivide by multiple columns?
pd.DataFrame.pivot_tabledf.pivot_table( values='val0', index=['key', 'row'], columns=['item', 'col'], fill_value=0, aggfunc='mean') item item0 item1 item2 col col2 col3 col4 col0 col1 col2 col3 col4 col0 col1 col3 col4 key row key0 row0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.86 0.00 row2 0.00 0.00 0.37 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.50 0.00 row3 0.00 0.00 0.00 0.00 0.31 0.00 0.81 0.00 0.00 0.00 0.00 0.00 row4 0.15 0.64 0.00 0.00 0.00 0.00 0.00 0.24 0.00 0.00 0.00 0.00 key1 row0 0.00 0.00 0.00 0.77 0.00 0.00 0.00 0.00 0.00 0.81 0.00 0.65 row2 0.35 0.00 0.00 0.00 0.00 0.44 0.00 0.00 0.00 0.00 0.00 0.13 row3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.28 0.00 row4 0.00 0.00 0.00 0.00 0.10 0.00 0.00 0.00 0.00 0.00 0.00 0.00 key2 row0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.40 0.00 0.00 row2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.13 0.00 0.00 0.00 row4 0.00 0.00 0.00 0.00 0.00 0.64 0.88 0.00 0.00 0.00 0.00 0.00pd.DataFrame.groupbydf.groupby( ['key', 'row', 'item', 'col'] )['val0'].mean().unstack(['item', 'col']).fillna(0).sort_index(1)pd.DataFrame.set_indexbecause the set of keys are unique for both rows and columnsdf.set_index( ['key', 'row', 'item', 'col'] )['val0'].unstack(['item', 'col']).fillna(0).sort_index(1)
Question 9
我是否可以汇总列和行一起出现的频率，又称为“交叉表”？
pd.DataFrame.pivot_tabledf.pivot_table(index='row', columns='col', fill_value=0, aggfunc='size') col col0 col1 col2 col3 col4 row row0 1 2 0 1 1 row2 1 0 2 1 2 row3 0 1 0 2 0 row4 0 1 2 2 1pd.DataFrame.groupbydf.groupby(['row', 'col'])['val0'].size().unstack(fill_value=0)pd.crosstabpd.crosstab(df['row'], df['col'])pd.factorize+np.bincount# get integer factorization `i` and unique values `r` # for column `'row'` i, r = pd.factorize(df['row'].values) # get integer factorization `j` and unique values `c` # for column `'col'` j, c = pd.factorize(df['col'].values) # `n` will be the number of rows # `m` will be the number of columns n, m = r.size, c.size # `i * m + j` is a clever way of counting the # factorization bins assuming a flat array of length # `n * m`. Which is why we subsequently reshape as `(n, m)` b = np.bincount(i * m + j, minlength=n * m).reshape(n, m) # BTW, whenever I read this, I think 'Bean, Rice, and Cheese' pd.DataFrame(b, r, c) col3 col2 col0 col1 col4 row3 2 0 0 1 0 row2 1 2 1 0 2 row0 1 0 1 2 1 row4 2 2 0 1 1pd.get_dummiespd.get_dummies(df['row']).T.dot(pd.get_dummies(df['col'])) col0 col1 col2 col3 col4 row0 1 2 0 1 1 row2 1 0 2 1 2 row3 0 1 0 2 0 row4 0 1 2 2 1
问题10
如何通过仅旋转两列将DataFrame从长转换为宽？
第一步是为每行分配一个数字-该数字将成为透视结果中该值的行索引。使用GroupBy.cumcount以下命令完成此操作：
df2.insert(0, 'count', df.groupby('A').cumcount())
df2
count A B
0 0 a 0
1 1 a 11
2 2 a 2
3 3 a 11
4 0 b 10
5 1 b 10
6 2 b 14
7 0 c 7
第二步是使用新创建的列作为要调用的索引DataFrame.pivot。
df2.pivot(*df)
# df.pivot(index='count', columns='A', values='B')
A a b c
count
0 0.0 10.0 7.0
1 11.0 10.0 NaN
2 2.0 14.0 NaN
3 11.0 NaN NaN
问题11
我如何将多重索引展平为单索引
pivot
如果columns输入object字符串join
df.columns = df.columns.map('|'.join)
其他 format
df.columns = df.columns.map('{0[0]}|{0[1]}'.format)
您可以考虑扩展正式文档吗？
问题10的答案发生了什么？我懂了
KeyError: 'A'。还有更多答案吗？@MonicaHeddneck我将再次进行审查，并在必要时进行更新。但是，
'A'假设'A'您的数据框中有一个要分组的列。我可以汇总多个值列吗？答案将适用于不同数据类型的列。例如：value = ['val0'，'val1']，其中val0是int而val1是string