透视表在处理时序问题上很方便,处理完后可转换为常规table,便于统计。举例说明:
class_id dates sale_num
0 1001 20150101 3
1 1001 20150102 6
2 1001 20150103 7
...
In[]:data = pd.pivot_table(mid_num,index=['class_id'],values=['sale_num'],columns=['dates'])
In[]:df = data.iloc[0:3,0:3]
In[]:df
Out[21]:
sale_num
dates 20150101 20150102 20150103
class_id
1001 3.0 6.0 7.0
1002 0.0 0.0 0.0
1004 1.0 3.0 3.0
In[]:df.stack()
Out[26]:
sale_num
class_id dates
1001 20150101 3.0
20150102 6.0
20150103 7.0
1002 20150101 0.0
20150102 0.0
20150103 0.0
1004 20150101 1.0
20150102 3.0
20150103 3.0
In[]:df.stack().reset_index()
Out[28]:
class_id dates sale_num
0 1001 20150101 3.0
1 1001 20150102 6.0
2 1001 20150103 7.0
3 1002 20150101 0.0
4 1002 20150102 0.0
5 1002 20150103 0.0
6 1004 20150101 1.0
7 1004 20150102 3.0
8 1004 20150103 3.0
In[]:df.unstack()
Out[27]:
dates class_id
sale_num 20150101 1001 3.0
1002 0.0
1004 1.0
20150102 1001 6.0
1002 0.0
1004 3.0
20150103 1001 7.0
1002 0.0
1004 3.0
In[]:
m4_md_num
日期 20150501 20150502 20150503
编码
10 17.0 10.0 5.0
11 7.0 0.0 1.0
12 145.0 32.0 29.0
In[]:df.index.names=[None]
In[]:df
Out[206]:
m4_md_num
日期 20150501 20150502 20150503
10 17.0 10.0 5.0
11 7.0 0.0 1.0
12 145.0 32.0 29.0
# data.unstack().stack() 进行还原
2、处理pivot_table,去掉索引:
(1)法一
In[]:df
m4_md_num
日期 20150501 20150502 20150503
编码
10 17.0 10.0 5.0
11 7.0 0.0 1.0
12 145.0 32.0 29.0
In[]:df.xs('m4_md_num',axis=1,drop_level=True)
Out[174]:
日期 20150501 20150502 20150503
编码
10 17.0 10.0 5.0
11 7.0 0.0 1.0
12 145.0 32.0 29.0
In[]:df.reset_index()
Out[175]:
日期 编码 20150501 20150502 20150503
0 10 17.0 10.0 5.0
1 11 7.0 0.0 1.0
2 12 145.0 32.0 29.0
In[]:df.to_csv('df.csv',index=False)
(2)法二
In[]:df.columns = df.columns.droplevel()
In[]:df.reset_index()
In[]:df.to_csv('df2.csv')
3、行转列
# 行转列
Out[32]:
class_id 2015-01-01 2015-01-02 2015-01-03 2015-01-04
0 1001 3.0 6.0 7.0 5.0
1 1002 0.0 0.0 0.0 0.0
2 1004 1.0 3.0 3.0 2.0
3 1101 1.0 0.0 0.0 0.0
In[]:pd.melt(df,id_vars=['class_id'],value_vars=df.columns[1:],var_name='dates',value_name='sale_num')
Out[34]:
class_id dates sale_num
0 1001 2015-01-01 3.0
1 1002 2015-01-01 0.0
2 1004 2015-01-01 1.0
3 1101 2015-01-01 1.0
4 1102 2015-01-01 0.0
5 1201 2015-01-01 64.0
4、