Stack Overflow
Stack Overflow

Questions tagged [crosstab]

A cross tab, also known as 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 also include the relevant language tag (python, R, sql-server etc.) on your question.

Resources:

1549 questions
277
votes
7 answers

PostgreSQL Crosstab Query

Does any one know how to create crosstab queries in PostgreSQL? For example I have the following table: Section Status Count A Active 1 A Inactive 2 B Active 4 B Inactive 5 I would like the query to…
user151419
265
votes
13 answers

MySQL - Rows to Columns

I tried to search posts, but I only found solutions for SQL Server/Access. I need a solution in MySQL (5.X). I have a table (called history) with 3 columns: hostid, itemname, itemvalue. If I do a select (select * from history), it will return …
Bob Rivers
  • 5,261
  • 6
  • 47
  • 59
103
votes
6 answers

Groupby value counts on the dataframe pandas

I have the following dataframe: df = pd.DataFrame([ (1, 1, 'term1'), (1, 2, 'term2'), (1, 1, 'term1'), (1, 1, 'term2'), (2, 2, 'term3'), (2, 3, 'term1'), (2, 2, 'term1') ], columns=['id', 'group', 'term']) I want to…
Salvador Dali
  • 214,103
  • 147
  • 703
  • 753
82
votes
6 answers

How to make a pandas crosstab with percentages?

Given a dataframe with different categorical variables, how do I return a cross-tabulation with percentages instead of frequencies? df = pd.DataFrame({'A' : ['one', 'one', 'two', 'three'] * 6, 'B' : ['A', 'B', 'C'] * 8, …
Brian Keegan
  • 2,208
  • 4
  • 24
  • 31
69
votes
1 answer

Transpose latest rows per user to columns

I have the following table, which gives multiple email addresses for each user. I need to flatten this out to columns on a user query. To give me the "newest" 3 email addresses based on the creation date.…
dacology
  • 954
  • 2
  • 7
  • 13
41
votes
5 answers

PostgreSQL convert columns to rows? Transpose?

I have a PostgreSQL function (or table) which gives me the following output: Sl.no username Designation salary etc.. 1 A XYZ 10000 ... 2 B RTS 50000 ... 3 C …
DonRaHulk
  • 575
  • 1
  • 6
  • 18
39
votes
3 answers

Create a pivot table with PostgreSQL

Suppose I have a table in Postgres called listings that looks like this: id neighborhood bedrooms price 1 downtown 0 256888 2 downtown 1 334000 3 riverview 1 505000 etc. How do…
Avishai
  • 4,512
  • 4
  • 41
  • 67
37
votes
5 answers

How is a Pandas crosstab different from a Pandas pivot_table?

Both the pandas.crosstab and the Pandas pivot table seem to provide the exact same functionality. Are there any differences?
user1008537
35
votes
6 answers

Dynamic alternative to pivot with CASE and GROUP BY

I have a table that looks like this: id feh bar 1 10 A 2 20 A 3 3 B 4 4 B 5 5 C 6 6 D 7 7 D 8 8 D And I want it to look like this: bar val1 val2 val3 A 10 20…
flipflop99
  • 583
  • 1
  • 5
  • 9
32
votes
2 answers

How to replace nulls with zeros in postgresql crosstabs

I've a product table with product_id and 100+ attributes. The product_id is text whereas the attribute columns are integer, i.e. 1 if the attribute exists. When the Postgresql crosstab is run, non-matching atrributes return null values. How do I…
Mike
  • 439
  • 1
  • 6
  • 10
26
votes
2 answers

I need to know how to create a crosstab query

I need help creating the below results. I thought of a sql pivot but I don't know how to use it. Looked at a few examples and cannot come up with a solution. Any other ideas on how to accomplish this is also welcome. Status columns must be…
Sam
  • 265
  • 1
  • 3
  • 9
25
votes
4 answers

Dynamically generate columns for crosstab in PostgreSQL

I am trying to create crosstab queries in PostgreSQL such that it automatically generates the crosstab columns instead of hardcoding it. I have written a function that dynamically generates the column list that I need for my crosstab query. The idea…
invinc4u
  • 1,125
  • 3
  • 15
  • 26
25
votes
1 answer

Why is my Crosstab being cut off in Excel?

I'm attempting to create an excel spreadsheet using BIRT. The spreadsheet is a crosstab mapping two objects together. The number of rows and columns are dynamic based on values in a MySQL database. Currently I have a working implementation of the…
radicaledward101
  • 555
  • 6
  • 15
22
votes
3 answers

visualizing crosstab tables with a plot in R

I saw a plot generated in excel and I was wondering if R could also do it. This picture is essentially a visualization of a crosstab table comparing the days of the week to preferred meals on that day and counting the number of people that fall…
CadisEtRama
  • 1,071
  • 5
  • 18
  • 31
21
votes
3 answers

Pivot on Multiple Columns using Tablefunc

Has anyone used tablefunc to pivot on multiple variables as opposed to only using row name? The documentation notes: The "extra" columns are expected to be the same for all rows with the same row_name value. I'm not sure how to do this without…
ideamotor
  • 856
  • 1
  • 7
  • 23
1
2 3
…
99 100