Python DataFrame Transform Rows to Column

Question

I have a csv that stores information about a particular object and Date.

Device  Date        Category    Amount
Pen     01/01/2014  A           12
Pen     01/01/2014  B           42
Pen     01/01/2014  C           10
Pen     01/01/2014  D           5
Pen     02/01/2014  A           7
Pen     02/01/2014  B           52
Pen     02/01/2014  C           1
Pen     02/01/2014  D           7
Pencil  01/01/2014  A           22
Pencil  01/01/2014  B           42
Pencil  01/01/2014  C           70
Pencil  01/01/2014  D           8

I want to read it into a DataFrame and make Category a column and Amounts for a specific device and date a row. This will make the dataset much smaller.

Device  Date        A   B   C   D
Pen    01/01/2014   12  42  10  5
Pen    02/01/2014   7   52  1   7
Pencil  01/01/2014  22  42  70  8

Possible duplicate of python, pandas, dataframe, rows to columns — Mohammad Athar
– Mohammad Athar, Commented Sep 15, 2016 at 14:19

akuiper · Accepted Answer · 2016-09-15 14:15:49Z

5

You can use pivot_table where columns that you want to keep are set as index, columns that go to the header are set as columns and columns that fill the cells in the output data frame are set as values:

df.pivot_table(index=['Device', 'Date'], columns='Category', values='Amount').reset_index()

# Category  Device        Date     A     B   C  D
#        0     Pen  01/01/2014    12    42  10  5
#        1     Pen  02/01/2014     7    52   1  7
#        2  Pencil  01/01/2014    22    42  70  8

answered Sep 15, 2016 at 14:15

akuiper

216k33 gold badges363 silver badges380 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

piRSquared · Accepted Answer · 2016-09-15 14:50:41Z

2

using groupby and unstack

df.groupby(['Device', 'Date', 'Category'])['Amount'].sum().unstack().reset_index()

answered Sep 15, 2016 at 14:50

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Collectives™ on Stack Overflow

Python DataFrame Transform Rows to Column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related