crosstab을 이용하여 없는 컬럼 포함 집계테이블 만들기

Untitled1

Crosstab을 이용하여 집계 (존재하지 않는 값 포함)

  • 존재하지 않는 값을 포함하여 crosstab을 할 필요가 있다.
  • 이러한 경우에는 pd.Series를 이용하여 컬럼을 만들어준다.
In [60]:
import pandas as pd
import numpy as np
In [61]:
df = pd.DataFrame({'CODE' : ['a','b','c','d','e','e','b'], 'Q' : [2,3,3,2,1,2,3]})
In [62]:
df
Out[62]:
CODE Q
0 a 2
1 b 3
2 c 3
3 d 2
4 e 1
5 e 2
6 b 3
In [63]:
df1 = pd.crosstab(index = df.CODE, columns = pd.Series([0,1,2,3,4,5]), values = df.Q, aggfunc = 'sum')
df1
Out[63]:
col_0 0 1 2 3 4 5
CODE
a 2.0 NaN NaN NaN NaN NaN
b NaN 3.0 NaN NaN NaN NaN
c NaN NaN 3.0 NaN NaN NaN
d NaN NaN NaN 2.0 NaN NaN
e NaN NaN NaN NaN 1.0 2.0
In [64]:
df2 = df1.fillna(0)
df2
Out[64]:
col_0 0 1 2 3 4 5
CODE
a 2.0 0.0 0.0 0.0 0.0 0.0
b 0.0 3.0 0.0 0.0 0.0 0.0
c 0.0 0.0 3.0 0.0 0.0 0.0
d 0.0 0.0 0.0 2.0 0.0 0.0
e 0.0 0.0 0.0 0.0 1.0 2.0
  • 전체를 대상으로 퍼센티지를 구한다.
In [65]:
df3 = pd.crosstab(index = df.CODE, columns = pd.Series([0,1,2,3,4,5]), values = df.Q, aggfunc = 'sum', normalize='all')
df3
Out[65]:
col_0 0 1 2 3 4 5
CODE
a 0.153846 0.000000 0.000000 0.000000 0.000000 0.000000
b 0.000000 0.230769 0.000000 0.000000 0.000000 0.000000
c 0.000000 0.000000 0.230769 0.000000 0.000000 0.000000
d 0.000000 0.000000 0.000000 0.153846 0.000000 0.000000
e 0.000000 0.000000 0.000000 0.000000 0.076923 0.153846
  • 컬럼이나 행을 기준으로 퍼센티지를 구한다.
In [49]:
df4 = pd.crosstab(index = df.CODE, columns = pd.Series([0,1,2,3,4,5]), values = df.Q, aggfunc = 'sum', normalize='index')
df4
Out[49]:
col_0 0 1 2 3 4 5
CODE
a 1.0 0.0 0.0 0.0 0.000000 0.000000
b 0.0 1.0 0.0 0.0 0.000000 0.000000
c 0.0 0.0 1.0 0.0 0.000000 0.000000
d 0.0 0.0 0.0 1.0 0.000000 0.000000
e 0.0 0.0 0.0 0.0 0.333333 0.666667
In [51]:
df5 = pd.crosstab(index = df.CODE, columns = pd.Series([0,1,2,3,4,5]), values = df.Q, aggfunc = 'sum', normalize='columns')
df5
Out[51]:
col_0 0 1 2 3 4 5
CODE
a 1.0 0.0 0.0 0.0 0.0 0.0
b 0.0 1.0 0.0 0.0 0.0 0.0
c 0.0 0.0 1.0 0.0 0.0 0.0
d 0.0 0.0 0.0 1.0 0.0 0.0
e 0.0 0.0 0.0 0.0 1.0 1.0
  • TOTAL을 구할수도 있다.
In [56]:
df6 = pd.crosstab(index = df.CODE, columns = pd.Series([0,1,2,3,4,5]), values = df.Q, aggfunc = 'sum', normalize='index', margins=True, margins_name='TOTAL')
df6
Out[56]:
col_0 0 1 2 3 4 5
CODE
a 1.000000 0.000000 0.000000 0.000000 0.000000 0.000000
b 0.000000 1.000000 0.000000 0.000000 0.000000 0.000000
c 0.000000 0.000000 1.000000 0.000000 0.000000 0.000000
d 0.000000 0.000000 0.000000 1.000000 0.000000 0.000000
e 0.000000 0.000000 0.000000 0.000000 0.333333 0.666667
TOTAL 0.153846 0.230769 0.230769 0.153846 0.076923 0.153846
In [58]:
df7 = pd.crosstab(index = df.CODE, columns = pd.Series([0,1,2,3,4,5]), values = df.Q, aggfunc = 'sum', normalize='columns', margins=True, margins_name='TOTAL')
df7
Out[58]:
col_0 0 1 2 3 4 5 TOTAL
CODE
a 1.0 0.0 0.0 0.0 0.0 0.0 0.153846
b 0.0 1.0 0.0 0.0 0.0 0.0 0.230769
c 0.0 0.0 1.0 0.0 0.0 0.0 0.230769
d 0.0 0.0 0.0 1.0 0.0 0.0 0.153846
e 0.0 0.0 0.0 0.0 1.0 1.0 0.230769

25 thoughts on “crosstab을 이용하여 없는 컬럼 포함 집계테이블 만들기

  1. Antibiotics For Sale Canada [url=https://buycialisuss.com/]canadian pharmacy cialis[/url] Viagra Offiziell Kaufen Cialis which is better cialis or levitra

  2. You actually make it seem so easy with your presentation but I find this topic to be really something that I think I would never understand.
    It seems too complex and extremely broad for me. I am looking forward
    for your next post, I will try to get the hang of it!

    Feel free to visit my homepage; g do

  3. Its not my first time to visit this website, i am browsing
    this website dailly and take good information from here every day.

    Feel free to surf to my blog: g use

  4. Remarkable things here. I am very happy to see your
    post. Thanks so much and I’m having a look ahead to contact you.
    Will you kindly drop me a mail?

    Look at my web blog: g rsacwgxy

  5. If you are going for most excellent contents like myself, only pay
    a visit this website everyday for the reason that it presents feature contents, thanks

    Feel free to surf to my page – cbd oil (tinyurl.com)

  6. Its like you read my mind! You appear to grasp so much about this, such as you wrote the e book
    in it or something. I think cbd oil that works 2020
    you can do with some % to drive the message home a bit, but instead of that,
    this is wonderful blog. A fantastic read. I will certainly be back.

  7. Great blog here! Also your website loads up very fast! What web host are you using?
    Can I get your affiliate link to your host? I wish my web
    site loaded up as fast as yours lol

    Here is my web site – cbd oil that works 2020

  8. Buying Clobetasol [url=https://cheapcialisll.com/]Cialis[/url] Viagra Torrino Cialis Generic Levaquin Buying Best Website Shop Without Perscription

  9. Знаете ли вы?
    Зелёный чай может быть розовым.
    Самцы косатки, обитающие в Британской Колумбии, всю жизнь живут с мамой.
    Индонезийской закуской начиняют пирожки, посыпают рис и кладут в лапшу и супы.
    Роден назвал свои «Врата ада» напрямую, а его соотечественник только намекнул.
    Врач на карантине спел созданную для фильма песню Высоцкого «Давно смолкли залпы орудий».

    [url=http://arbeca.net/]http://www.arbeca.net/[/url]

  10. Kamagra Acheter France Jeargo [url=https://bbuycialisss.com/#]Cialis[/url] Heemiumbob Sublingual Viagra 150 Mg EndurlEnrors Cialis Shooth cialis levitra viagra difference

댓글 남기기