[Python] Pandas - Melt (Unpivot)

In [1]:

import os
import numpy as np
import pandas as pd

In [2]:

os.getcwd() #현재 디렉토리 확인

Out[2]:

'/Users/limjongjun/Desktop/JayJay/Growth/Python/Class101_Pandas'

In [122]:

pip install openpyxl

Collecting openpyxl
  Downloading openpyxl-3.1.2-py2.py3-none-any.whl (249 kB)
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 250.0/250.0 kB 9.5 MB/s eta 0:00:00
Collecting et-xmlfile
  Downloading et_xmlfile-1.1.0-py3-none-any.whl (4.7 kB)
Installing collected packages: et-xmlfile, openpyxl
Successfully installed et-xmlfile-1.1.0 openpyxl-3.1.2
Note: you may need to restart the kernel to use updated packages.

In [125]:

df14 = pd.read_excel('05_Stack.xlsx')
df14.head()

Out[125]:

	Unnamed: 0	Unnamed: 1	Unnamed: 2	Unnamed: 3	Unnamed: 4	Unnamed: 5	Unnamed: 6	정상	정상.1	정상.2	...	불용	불용.1	불용.2	불용.3	합계	합계.1	합계.2	합계.3	Unnamed: 23
0	구분	카테고리명	자재그룹	자재그룹명	제품명	단위	판매	2019-12-01 00:00:00	2020-10-01 00:00:00	2020-11-01 00:00:00	...	2019-12-01 00:00:00	2020-10-01 00:00:00	2020-11-01 00:00:00	2020-12-01 00:00:00	2019-12-01 00:00:00	2020-10-01 00:00:00	2020-11-01 00:00:00	2020-12-01 00:00:00	안전재고	분류
1	구분	CHB	1687	친환경 바디워시	A	천원	0	0	0	0	...	98.8	0	0	0	98.8	0	0	0	0	E
2	구분	CHB	1687	친환경 바디워시	B	천원	0	0	0	0	...	393.7	0	0	0	393.7	0	0	0	0	E
3	구분	CHB	1687	친환경 바디워시	C	천원	10354.1	9474.3	9741.7	6965.7	...	6.4	0	6.4	0	9480.7	9741.7	6972	3368.2	0	A
4	구분	CHB	1687	친환경 바디워시	D	천원	3543.3	7702.6	6117.1	4009.9	...	7.7	10.2	0	10.2	7710.3	6127.3	4009.9	2680.1	0	A

5 rows × 25 columns

In [126]:

df14 = pd.read_excel('05_Stack.xlsx', skiprows= 1)
df14.head()

Out[126]:

	구분	카테고리명	자재그룹	자재그룹명	제품명	단위	판매	2019-12-01 00:00:00	2020-10-01 00:00:00	2020-11-01 00:00:00	...	2019-12-01 00:00:00.2	2020-10-01 00:00:00.2	2020-11-01 00:00:00.2	2020-12-01 00:00:00.2	2019-12-01 00:00:00.3	2020-10-01 00:00:00.3	2020-11-01 00:00:00.3	2020-12-01 00:00:00.3	안전재고	분류
0	구분	CHB	1687	친환경 바디워시	A	천원	0.0	0.0	0.0	0.0	...	98.8	0.0	0.0	0.0	98.8	0.0	0.0	0.0	0.0	E
1	구분	CHB	1687	친환경 바디워시	B	천원	0.0	0.0	0.0	0.0	...	393.7	0.0	0.0	0.0	393.7	0.0	0.0	0.0	0.0	E
2	구분	CHB	1687	친환경 바디워시	C	천원	10354.1	9474.3	9741.7	6965.7	...	6.4	0.0	6.4	0.0	9480.7	9741.7	6972.0	3368.2	0.0	A
3	구분	CHB	1687	친환경 바디워시	D	천원	3543.3	7702.6	6117.1	4009.9	...	7.7	10.2	0.0	10.2	7710.3	6127.3	4009.9	2680.1	0.0	A
4	구분	CHB	1687	친환경 바디워시	E	천원	234603.9	56309.6	78268.0	74942.2	...	293.0	0.0	0.0	277.2	56602.6	78268.0	74942.2	84967.2	39593.3	A

5 rows × 25 columns

In [127]:

df14.columns

Out[127]:

Index([                   '구분',                 '카테고리명',
                        '자재그룹',                 '자재그룹명',
                         '제품명',                    '단위',
                          '판매',     2019-12-01 00:00:00,
           2020-10-01 00:00:00,     2020-11-01 00:00:00,
           2020-12-01 00:00:00, '2019-12-01 00:00:00.1',
       '2020-10-01 00:00:00.1', '2020-11-01 00:00:00.1',
       '2020-12-01 00:00:00.1', '2019-12-01 00:00:00.2',
       '2020-10-01 00:00:00.2', '2020-11-01 00:00:00.2',
       '2020-12-01 00:00:00.2', '2019-12-01 00:00:00.3',
       '2020-10-01 00:00:00.3', '2020-11-01 00:00:00.3',
       '2020-12-01 00:00:00.3',                  '안전재고',
                         ' 분류'],
      dtype='object')

In [130]:

df15 = df14.drop(columns=['구분','카테고리명','자재그룹','자재그룹명','단위','판매','안전재고',' 분류']) #drop 함수를 통해 삭제할 컬럼 지정
df15.head()

Out[130]:

	제품명	2019-12-01 00:00:00	2020-10-01 00:00:00	2020-11-01 00:00:00	2020-12-01 00:00:00	2019-12-01 00:00:00.1	2020-10-01 00:00:00.1	2020-11-01 00:00:00.1	2020-12-01 00:00:00.1	2019-12-01 00:00:00.2	2020-10-01 00:00:00.2	2020-11-01 00:00:00.2	2020-12-01 00:00:00.2	2019-12-01 00:00:00.3	2020-10-01 00:00:00.3	2020-11-01 00:00:00.3	2020-12-01 00:00:00.3
0	A	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0	98.8	0.0	0.0	0.0	98.8	0.0	0.0	0.0
1	B	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0	393.7	0.0	0.0	0.0	393.7	0.0	0.0	0.0
2	C	9474.3	9741.7	6965.7	3368.2	0.0	0.0	0.0	0	6.4	0.0	6.4	0.0	9480.7	9741.7	6972.0	3368.2
3	D	7702.6	6117.1	4009.9	2669.8	0.0	0.0	0.0	0	7.7	10.2	0.0	10.2	7710.3	6127.3	4009.9	2680.1
4	E	56309.6	78268.0	74942.2	84690.1	0.0	0.0	0.0	0	293.0	0.0	0.0	277.2	56602.6	78268.0	74942.2	84967.2

In [133]:

df16 = df15.melt(id_vars=['제품명']).rename(columns={'variable' :'일자', 'value' : '매출액'}) #unpivot

In [135]:

df16.pivot_table(index='제품명', values = '매출액', aggfunc='sum')

Out[135]:

	매출액
제품명
A	197.6
A1	8.8
A2	238295.4
A3	20684.1
B	787.4
B2	26849.2
B3	77405.9
C	59125.3
D	41055.1
E	589560.1
F	46092.0
FF1	43091.0
FF2	89.4
FF3	279876.8
G	159748.9
H	297757.6
I	110513.8
J	194242.5
K	187450.4
KC	812.8
KC2	107632.8
KC3	55963.8
L	87297.2
M	63899.4
N	93.8
O	3388.2
P	76.7
Q	445.4
R	516.4
S	345.4
T	63.6
U	38.0
V	1487.8
W	796.7
X	199.8
Y	1108.3
Z	165.4
Z1	52900.4
Z2	45977.9
Z3	37597.8

In [137]:

df17 = df14.melt(id_vars=['구분','카테고리명','자재그룹','자재그룹명','제품명','단위','판매','안전재고',' 분류']).rename(columns={'variable' :'일자', 'value' : '매출액'}) #unpivot
df17

Out[137]:

	구분	카테고리명	자재그룹	자재그룹명	제품명	단위	판매	안전재고	분류	일자	매출액
0	구분	CHB	1687	친환경 바디워시	A	천원	0.0	0.0	E	2019-12-01 00:00:00	0.0
1	구분	CHB	1687	친환경 바디워시	B	천원	0.0	0.0	E	2019-12-01 00:00:00	0.0
2	구분	CHB	1687	친환경 바디워시	C	천원	10354.1	0.0	A	2019-12-01 00:00:00	9474.3
3	구분	CHB	1687	친환경 바디워시	D	천원	3543.3	0.0	A	2019-12-01 00:00:00	7702.6
4	구분	CHB	1687	친환경 바디워시	E	천원	234603.9	39593.3	A	2019-12-01 00:00:00	56309.6
...	...	...	...	...	...	...	...	...	...	...	...
635	구분	CHB	1687	친환경 바디워시	Z2	천원	5012.4	0.0	K	2020-12-01 00:00:00.3	1619.5
636	구분	CHB	1687	친환경 바디워시	Z3	천원	585.0	0.0	K	2020-12-01 00:00:00.3	3191.2
637	구분	CHB	1687	친환경 바디워시	KC	천원	0.0	0.0	E	2020-12-01 00:00:00.3	0.0
638	구분	CHB	1687	친환경 바디워시	KC2	천원	16636.2	0.0	L	2020-12-01 00:00:00.3	16845.8
639	구분	CHB	1687	친환경 바디워시	KC3	천원	18871.3	0.0	F	2020-12-01 00:00:00.3	811.9

640 rows × 11 columns