CODEWITHSUNDEEP
sqlpandas
lpt
lpt

Reputation: 975

convert a pandas pivot into sql pivot (code help)

This is probably a very simple questions to sql gurus. But how would I covert a pandas pivot into sql pivot. (note of caution - I have a huge data, stored in sql), when I tried to import that in Jupyter Notebook to convert into pandas dataframe, it just does not (Jupyter kernel dies).

Now I need help to pivot in SQL

Here is my dummy data and solution in pandas

import numpy as np import pandas as pd

create fake data

newd = {'year': [2001, 2002, 2005, 2002, 2004, 1999, 1999, 1999, 2012, 2000, 2010, 2005, 2006, 2009, 2009, 
                 2009, 2009, 2010, 2007, 2008, 2009, 2010],
     'tin': [12, 23, 24, 28,30, 12,7, 12, 12, 23, 24, 7, 12, 35, 39,37, 36, 333, 13, 13, 13, 13],
     'ptin': [12, 23, 28, 22, 12, 12,0, 12, 12, 23, 27, 45, 99, 7, 7, 7, 7, 0, 17, 21, 26, 18] }

convert to pandas df

newdf=pd.DataFrame(newd)

group tin by payer's tin

df=newdf.groupby(['tin', 'year'])['ptin'].groups
print(list(df))
print(np.unique(newdf['year'].values))

observe the distribution in a pivot table by year

print(newdf.pivot_table(index='tin', columns='year', values='ptin', aggfunc=len, fill_value=0))
#newdf[newdf['year']==1999]

year  1999  2000  2001  2002  2004  2005  2006  2007  2008  2009  2010  2012
tin                                                                         
7        1     0     0     0     0     1     0     0     0     0     0     0
12       2     0     1     0     0     0     1     0     0     0     0     1
13       0     0     0     0     0     0     0     1     1     1     1     0
23       0     1     0     1     0     0     0     0     0     0     0     0
24       0     0     0     0     0     1     0     0     0     0     1     0
28       0     0     0     1     0     0     0     0     0     0     0     0
30       0     0     0     0     1     0     0     0     0     0     0     0
35       0     0     0     0     0     0     0     0     0     1     0     0
36       0     0     0     0     0     0     0     0     0     1     0     0
37       0     0     0     0     0     0     0     0     0     1     0     0
39       0     0     0     0     0     0     0     0     0     1     0     0
333      0     0     0     0     0     0     0     0     0     0     1     0

Upvotes: 1

Views: 1694

Answers (1)

Leslie Sage
Leslie Sage

Reputation: 391

Here's how to create your fake dataset in SQL. (N.B. Data is inserted by row in SQL, not by column.)

IF OBJECT_ID('fake_data','U') IS NOT NULL 
    DROP TABLE fake_data;
CREATE TABLE fake_data (
    [year] int
    ,[tin] int
    ,[ptin] int
);
INSERT INTO fake_data (
    [year]
    ,[tin]
    ,[ptin]
)
VALUES
    (2001,12,12),(2002,23,23),(2005,24,28),(2002,28,22),(2004,30,12),(1999,12,12)
    ,(1999,7,0),(1999,12,12),(2012,12,12),(2000,23,23),(2010,24,27),(2005,7,45)
    ,(2006,12,99),(2009,35,7),(2009,39,7),(2009,37,7),(2009,36,7),(2010,333,0)
    ,(2007,13,17),(2008,13,21),(2009,13,26),(2010,13,18);

Then view the sample dataset:

SELECT * FROM fake_data;

Here's the SQL query for your pivot. N.B. The brackets aren't always necessary for column names but are a good idea if your column names are reserved words in SQL (like 'year', a function.)

SELECT
    [tin],[1999],[2000],[2001],[2002],[2004],[2005],[2006],[2007],[2008],[2009],[2010],[2012]
FROM 
    fake_data
PIVOT (
    COUNT([ptin])
    FOR [year]
    IN ([1999],[2000],[2001],[2002],[2004],[2005],[2006],[2007],[2008],[2009],[2010],[2012])
) AS myPivotTable
ORDER BY [tin];

Look into dynamic SQL if you need the column headings to be the result of a query instead of assembled manually.

Upvotes: 1

Related Questions