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
You could group by both the bins and username, compute the group sizes and then use unstack(): >>> groups = df.groupby([‘username’, pd.cut(df.views, bins)]) >>> groups.size().unstack() views (1, 10] (10, 25] (25, 50] (50, 100] username jane 1 1 1 1 john 1 1 1 1
For anyone who is still interested in the difference between pivot and pivot_table, there are mainly two differences: pivot_table is a generalization of pivot that can handle duplicate values for one pivoted index/column pair. Specifically, you can give pivot_table a list of aggregation functions using keyword argument aggfunc. The default aggfunc of pivot_table is numpy.mean. … Read more
When using Many to Many relationships with Eloquent, the resulting model automatically gets a pivot attribute assigned. Through that attribute you’re able to access pivot table columns. Although by default there are only the keys in the pivot object. To get your columns in there too, you need to specify them when defining the relationship: … Read more
Here are couple of ways to reshape your data df In [27]: df Out[27]: Col X Col Y 0 class 1 cat 1 1 class 2 cat 1 2 class 3 cat 2 3 class 2 cat 3 1) Using pd.crosstab() In [28]: pd.crosstab(df[‘Col X’], df[‘Col Y’]) Out[28]: Col Y cat 1 cat 2 cat … Read more
Here are couple of ways to reshape your data df In [27]: df Out[27]: Col X Col Y 0 class 1 cat 1 1 class 2 cat 1 2 class 3 cat 2 3 class 2 cat 3 1) Using pd.crosstab() In [28]: pd.crosstab(df[‘Col X’], df[‘Col Y’]) Out[28]: Col Y cat 1 cat 2 cat … Read more
This is called a pivot table. It’s kind of awkward to produce: SELECT ID, MAX(CASE Type WHEN 202 THEN Degignation END) AS `202` MAX(CASE Type WHEN 234 THEN Degignation END) AS `234` MAX(CASE Type WHEN 239 THEN Degignation END) AS `239` Email FROM mytable GROUP BY ID, Email Note that you must know all the … Read more
Basing my answer on a table of the form: CREATE TABLE tbl ( sl_no int , username text , designation text , salary int ); Each row results in a new column to return. With a dynamic return type like this, it’s hardly possible to make this completely dynamic with a single call to the … Read more
This code should do what you need. To learn more about filtering PivotTables quickly, check out my blogpost on the subject. Option Explicit Sub FilterPivot() Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim i As Long Dim vItem As Variant Dim vCountries As Variant Set pt = ActiveSheet.PivotTables(“PivotTable1”) Set pf … Read more
Create procedure CREATE PROCEDURE pivot (tablename VARCHAR(64), groupname VARCHAR(64), pivotname VARCHAR(64), valuename VARCHAR(64)) BEGIN SELECT CONCAT(‘CREATE VIEW to_columnslist AS\n’, ‘SELECT DISTINCT CONCAT(\’`\’, `’, pivotname,’`, \’` VARCHAR(255) path \\\’$.”\’, ‘, pivotname,’, \'”\\\’\’) line\n’, ‘FROM ‘, tablename) INTO @sql; PREPARE stmt FROM @sql; EXECUTE stmt; DROP PREPARE stmt; SELECT CONCAT( ‘SELECT to_json.`’, groupname,’`, parsed.*’, ‘\n’, ‘FROM (SELECT … Read more