Stack Overflow
Stack Overflow

Questions tagged [pivot-table]

A "pivot table" is a method of summarizing data in tabular form. Typically, these have a category column in the left hand side, and multiple summary columns to the right. Each different category will have its own row. Be sure to include the relevant language tag (python, excel, R, postgreSQL etc.) on your question.

A "pivot table" is a method of summarizing data in tabular form. Typically, these have a category column in the left hand side, and multiple summary columns to the right. Each different category will have its own row.

Pivot tables are typically found in data visualization programs, including spreadsheets and business intelligence. Note that a "pivot table" is also known as a "cross tab" or "cross tabulation".

Resources:

8105 questions
15
votes
2 answers

Pandas pivot table Percent Calculations

Given the following data frame and pivot table: import pandas as pd df=pd.DataFrame({'A':['x','y','z','x','y','z'], 'B':['one','one','one','two','two','two'], 'C':[2,18,2,8,2,18]}) df A B C 0 x one …
Dance Party2
  • 7,214
  • 17
  • 59
  • 106
15
votes
3 answers

Flatten DataFrame with multi-index columns

I'd like to convert a Pandas DataFrame that is derived from a pivot table into a row representation as shown below. This is where I'm at: import pandas as pd import numpy as np df = pd.DataFrame({ 'goods': ['a', 'a', 'b', 'b', 'b'], 'stock':…
orange
  • 7,755
  • 14
  • 75
  • 139
15
votes
6 answers

Filter Excel pivot table using VBA

I have tried copying and pasting solutions from the internet forever now to try to filter a pivot table in Excel using VBA. The code below doesn't work. Sub FilterPivotTable() Application.ScreenUpdating = False …
user1283776
  • 19,640
  • 49
  • 136
  • 276
14
votes
5 answers

Create a Pivot Table from a DataTable

I am using C# winforms to create an application that needs to turn a datatable into a pivot table. I have the pivot table working fine from a SQL end, but creating it from a datatable seems trickier. I couldn't seem to find anything built into…
ImGreg
  • 2,946
  • 16
  • 43
  • 65
14
votes
3 answers

Pandas pivot table for multiple columns at once

Let's say I have a DataFrame: nj ptype wd wpt 0 2 1 2 1 1 3 2 1 2 2 1 1 3 1 3 2 2 3 3 4 3 1 2 2 I would like to aggregate this data using ptype as the index like so: nj …
Grr
  • 15,553
  • 7
  • 65
  • 85
14
votes
3 answers

Pandas dataframe to count matrix

This must be obvious, but I couldn't find an easy solution. I have pandas DataFrame like this: actual | predicted ------ + --------- Apple | Apple Apple | Apple Apple | Banana Banana | Orange Orange | Apple I want this: | Apple |…
Gregor Sturm
  • 2,792
  • 1
  • 25
  • 34
14
votes
3 answers

Python Pandas : Pivot table : aggfunc concatenate instead of np.size or np.sum

I have some entries in dataframe like : name, age, phonenumber A,10, Phone1 A,10,Phone2 B,21,PhoneB1 B,21,PhoneB2 C,23,PhoneC Here is what I am trying to achieve as result of pivot table: name, age, phonenumbers, phonenocount A,10,…
Sivaswami Jeganathan
  • 663
  • 1
  • 6
  • 19
14
votes
1 answer

pandas equivalent for R dcast

I have some data like this: import pandas as pd df = pd.DataFrame(index = range(1,13), columns=['school', 'year', 'metric', 'values'], ) df['school'] = ['id1']*6 + ['id2']*6 df['year'] = (['2015']*3 + ['2016']*3)*2 df['metric'] = ['tuition',…
Don
  • 857
  • 1
  • 9
  • 19
14
votes
4 answers

Pivot in Excel without aggregation, to show text, not numbers?

Let's say I have a table like this: Country Region Mytext USA North a USA South b Brasil North c Brasil South d How can I obtain a pivot like this in Excel? Country North South USA a b Brasil c …
Pythonista anonymous
  • 8,140
  • 20
  • 70
  • 112
14
votes
3 answers

How can I collapse a dataframe by some variables, taking mean across others

I need to summarize a data frame by some variables, ignoring the others. This is sometimes referred to as collapsing. E.g. if I have a dataframe like this: Widget Type Energy egg 1 20 egg 2 30 jap 3 50 jap 1 60 Then collapsing by Widget,…
Alex Holcombe
  • 2,453
  • 4
  • 24
  • 34
14
votes
2 answers

Filtering pivot table data with Laravel models

Let's say I have three tables (this is just an example): users user_id username roles role_id name user_roles user_id role_id primary (boolean) And the corresponding laravel models: class User extends Eloquent { …
user1544110
  • 163
  • 1
  • 1
  • 4
14
votes
3 answers

How to add a weighted average in my pivot table?

I would like to know how to add a weighted average in my pivot table. In fact, I need to do the following calculation: SUM(col1 * col2)/SUM(col2). I tried to do it by using the calculated field option but when I enter my formula, I just have the…
Leep
  • 447
  • 1
  • 3
  • 11
13
votes
1 answer

TypeError: pivot_table() got multiple values for keyword argument 'values'

I am using Python2.7. I am learning pandas and was implementing pivot_table. While implementing the example given in pivot_table documentation : raw_data = {'A':['foo','foo','foo','foo','foo','bar','bar','bar','bar'], …
Kumar Gaurav
  • 156
  • 1
  • 1
  • 8
13
votes
5 answers

Refresh Pivot table using Apache POI

No/Minimal documentation regarding Apache POI for Pivot tables in the Apache site has got me to write this. I want to refresh a pivot table in a Work Book using Apache POI. Please let me know where I can get proper documentation and Examples…
pavi
  • 654
  • 1
  • 9
  • 29
13
votes
5 answers

Excel pivot table - average of calculated sums

I'm sure this is simple, but how do I get a pivot table to display an average for a calculated sum of fields? In the simplified example, I've filtered out fund x1, and the pivot table is showing the sums of the remaining funds per person. Now how…
Sparky McSparky
  • 166
  • 1
  • 1
  • 5
1 2 3
…
99 100