python - Format Pandas Pivot Table -
i met problem in formatting pivot table created pandas. made matrix table between 2 columns (a,b) source data, using pandas.pivot_table column, , b index.
>> df = pd.read_excel("data.xls") >> table = pd.pivot_table(df,index=["b"], values='count',columns=["a"],aggfunc=[num.sum], fill_value=0,margins=true,dropna= true) >> table it returns as:
sum 1 2 3 b 1 23 52 0 75 2 16 35 12 65 3 56 0 0 56 95 87 12 196 and hope have format this:
all_b 1 2 3 1 23 52 0 75 b 2 16 35 12 65 3 56 0 0 56 all_a 95 87 12 196 how should this? ahead.
the table returned pd.pivot_table convenient work on (it's single-level index/column) , not require further format manipulation. if insist on changing format 1 mentioned in post, need construct multi-level index/column using pd.multiindex. here example on how it.
before manipulation,
import pandas pd import numpy np np.random.seed(0) = np.random.randint(1, 4, 100) b = np.random.randint(1, 4, 100) df = pd.dataframe(dict(a=a,b=b,val=np.random.randint(1,100,100))) table = pd.pivot_table(df, index='a', columns='b', values='val', aggfunc=sum, fill_value=0, margins=true) print(table) b 1 2 3 1 454 649 770 1873 2 628 576 467 1671 3 376 247 481 1104 1458 1472 1718 4648 after:
multi_level_column = pd.multiindex.from_arrays([['a', 'a', 'a', 'all_b'], [1,2,3,'']]) multi_level_index = pd.multiindex.from_arrays([['b', 'b', 'b', 'all_a'], [1,2,3,'']]) table.index = multi_level_index table.columns = multi_level_column print(table) all_b 1 2 3 b 1 454 649 770 1873 2 628 576 467 1671 3 376 247 481 1104 all_a 1458 1472 1718 4648
Comments
Post a Comment