If you have not installed the additional module tablefunc, run this command once per database: CREATE EXTENSION tablefunc; Answer to question A very basic crosstab solution for your case: SELECT * FROM crosstab( ‘SELECT bar, 1 AS cat, feh FROM tbl_org ORDER BY bar, feh’) AS ct (bar text, val1 int, val2 int, val3 int); … Read more
The main difference between the two is the pivot_table expects your input data to already be a DataFrame; you pass a DataFrame to pivot_table and specify the index/columns/values by passing the column names as strings. With cross_tab, you don’t necessarily need to have a DataFrame going in, as you just pass array-like objects for index/columns/values. … Read more
First compute the average with the aggregate function avg(): SELECT neighborhood, bedrooms, avg(price) FROM listings GROUP BY 1,2 ORDER BY 1,2; Then feed the result to the crosstab() function as instructed in great detail in this related answer: PostgreSQL Crosstab Query
Use crosstab() from the tablefunc module. SELECT * FROM crosstab( $$SELECT user_id, user_name, rn, email_address FROM ( SELECT u.user_id, u.user_name, e.email_address , row_number() OVER (PARTITION BY u.user_id ORDER BY e.creation_date DESC NULLS LAST) AS rn FROM usr u LEFT JOIN email_tbl e USING (user_id) ) sub WHERE rn < 4 ORDER BY user_id $$ , … Read more
From Pandas 0.18.1 onwards, there’s a normalize option: In [1]: pd.crosstab(df.A,df.B, normalize=”index”) Out[1]: B A B C A one 0.333333 0.333333 0.333333 three 0.333333 0.333333 0.333333 two 0.333333 0.333333 0.333333 Where you can normalise across either all, index (rows), or columns. More details are available in the documentation.
I use groupby and size df.groupby([‘id’, ‘group’, ‘term’]).size().unstack(fill_value=0) Timing 1,000,000 rows df = pd.DataFrame(dict(id=np.random.choice(100, 1000000), group=np.random.choice(20, 1000000), term=np.random.choice(10, 1000000)))
Install the additional module tablefunc once per database, which provides the function crosstab(). Since Postgres 9.1 you can use CREATE EXTENSION for that: CREATE EXTENSION IF NOT EXISTS tablefunc; Improved test case CREATE TABLE tbl ( section text , status text , ct integer — “count” is a reserved word in standard SQL ); INSERT … Read more
I’m going to add a somewhat longer and more detailed explanation of the steps to take to solve this problem. I apologize if it’s too long. I’ll start out with the base you’ve given and use it to define a couple of terms that I’ll use for the rest of this post. This will be … Read more