How can I pivot a dataframe?

A pivot is a transformation that takes a dataframe with columns representing categories and rows representing values, and reorients it so that the categories are in the rows, the values are in the columns, and the index is set to the original row values.

Basic syntax:

df.pivot(index=, columns=, values=)

Examples:

• Pivot on a single column:

df.pivot(index='row', columns='col', values='val')

• Pivot on multiple columns:

df.pivot(index=['row', 'item'], columns='col', values='val')

• Pivot on multiple values:

df.pivot(index='row', columns='col', values=['val0', 'val1'])

• Pivot with custom aggregation functions:

df.pivot(index='row', columns='col', values='val', aggfunc='mean')

• Handling duplicate keys:

By default, if there are duplicate keys in the row or column labels, an error will be raised. Alternatively, you can use:

df.pivot_table(index='row', columns='col', values='val', fill_value=0)

• Other methods for pivoting:

• groupby unstack:

  df.groupby('row', 'col')['val'].mean().unstack(fill_value=0)

• pd.DataFrame.set_index: Use set_index to set the row and column axes and then unstack to pivot.
• pd.crosstab: Specifically designed for creating crosstabulations or pivot tables.

Advanced Pivoting Techniques:

• Cross-tabulation (frequency counting):

pd.crosstab(index=df['row'], columns=df['col'], values=df['val'], aggfunc='count')

• Multiple aggregation functions:

df.pivot_table(index='row', columns='col', values='val', aggfunc=['mean', 'sum'])

• Subdividing by multiple columns:

df.pivot_table(index='row', columns=['item', 'col'], values='val', fill_value=0, aggfunc='mean')