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
10
votes
7 answers

Crosstab with multiple items

In SPSS, it is (relatively) easy to create a cross tab with multiple variables using the factors (or values) as the table heading. So, something like the following (made up data, etc.). Q1, Q2, and Q3 each have either a 1, a 2, or a 3 for each…
Michael Wexler
  • 111
  • 1
  • 1
  • 5
9
votes
3 answers

Create a symmetric matrix that counts the relational records

I would like to count the number of all possible pairwise relations through a column (Value) based on another column (ID). Example dataframe: ID Value 0 1 A 1 1 A 2 1 A 3 1 B 4 1 C 5 2 B 6 2 C 7 2 …
fillo
  • 365
  • 1
  • 12
9
votes
2 answers

Create contingency table Pandas with counts and percentages

Is there a better way to create a contingency table in pandas with pd.crosstab() or pd.pivot_table() to generate counts and percentages. Current solution cat=['A','B','B','A','B','B','A','A','B','B'] target =…
iboboboru
  • 1,112
  • 2
  • 10
  • 21
9
votes
4 answers

Using Linq to create crosstab results

Possible Duplicate: Is it possible to Pivot data using LINQ? I'm wondering if its at all possible to create crosstab style results with Linq. I have some data that looks like the following: var list = new[] { new {GroupId = 1,…
Mark
9
votes
3 answers

Better way to produce data frame using table()

Recently, I have found that I am using the following pattern over and over again. The process is: cross-tabulate numeric variable by factor using table create data frame from created table add original numeric values to data frame (from row names…
Tomas Greif
  • 21,685
  • 23
  • 106
  • 155
9
votes
3 answers

Jasper Reports crosstab sorting with comparatorExpression

I'm trying to sort my dynamic columns in a cross tab according to some custom scheme. In the docs I found mention of comparatorExpression: Crosstab group bucket comparator expression. The result of this expression is used to sort the buckets, in…
JayL
  • 2,819
  • 4
  • 22
  • 15
8
votes
2 answers

Quotation mark incorrect when using crosstab() in PostgreSQL

I have a table t1 as below: create table t1 ( person_id int, item_name varchar(30), item_value varchar(100) ); There are five records in this table: person_id | item_name | item_value 1 'NAME' 'john' 1 'GENDER' …
wureka
  • 731
  • 1
  • 11
  • 26
8
votes
1 answer

Dynamically generate columns in PostgreSQL

I have seen that there are quit a few similar questions like this one, but I havent understood how to code it myself. Please have in mind that I am just a beginner in this field. Basically I want to pivot the table like this: zoom | day |…
newbie_girl
  • 353
  • 1
  • 3
  • 15
8
votes
3 answers

How can I sort the columns in a crosstab query, when the column data is dynamic?

I've been doing a bit of research on this topic and I can't seem either find a workable solution, or one that is explained well enough for me to implement. If you've ever created a crosstab query in Access, you are aware that by default Access…
RLH
  • 15,230
  • 22
  • 98
  • 182
8
votes
4 answers

Transpose rows and columns (a.k.a. pivot) only with a minimum COUNT()?

Here's my table 'tab_test': year animal price 2000 kittens 79 2000 kittens 93 2000 kittens 100 2000 puppies 15 2000 puppies 32 2001 kittens 31 2001 kittens 17 2001 puppies 65 2001 puppies 48 2002 kittens 84 2002 …
user1626730
  • 3,783
  • 5
  • 20
  • 24
7
votes
2 answers

Is it possible to use Crosstab/Pivot Query in MySQL?

I'm using MySQL. This is table i have supplier_ID Item_ID Date Price QTY 1 1 2012-01-01 00:00:00 500.00 2 1 1 2012-01-03 00:00:00 450.00 10 2 1 2012-01-01…
SiHyung Lee
  • 349
  • 3
  • 8
  • 25
7
votes
2 answers

PostgreSQL cross tab with three columns with values summed from one column

I am new to SQL and was trying to do a crosstab in Postgres. I would have done it in Excel, but I have a database of around 3.5 million rows, 20,000 different values for code, 7 categories in cat, and variable values from 1 to 100. A code may only…
Siraj
  • 71
  • 1
  • 1
  • 2
7
votes
2 answers

Significance testing in R, determining if the proportion in one column is significantly different from the other column within the single variable

I'm sure this is an easy command in R, but for some reason, I'm having trouble finding a solution. I'm trying to run a bunch of crosstabs (using the table() command) in R, and each tab has two columns (treatment and no treatment). I would like to…
Captain Murphy
  • 855
  • 3
  • 15
  • 23
7
votes
3 answers

Compare values of a dictionary and return a count of matching values

I have a dictionary comprised of product names and unique customer emails who have purchased those items that looks like this: customer_emails = { 'Backpack':['customer1@gmail.com','customer2@gmail.com','customer3@yahoo.com','customer4@msn.com'],…
Trevor Theodore
  • 105
  • 7
7
votes
2 answers

Execute a dynamic crosstab query

I implemented this function in my Postgres database: http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/ Here's the function: create or replace function xtab (tablename varchar, rowc varchar, colc…
skilbjo
  • 510
  • 1
  • 7
  • 20
1 2
3
…
99 100