python - Pandas 数据框 : creating pivot_table on a data frame without giving values

Question

我有一个文本文件，其中包含以下格式的数据

101.223.129.4 918801641445^0^paus
101.223.129.4 918801641445^0^german
101.223.129.4 918801641445^0^photo
101.223.129.4 918801641445^0^polish
101.223.129.4 918801641445^0^find
101.223.129.4 918801641445^0^extra
101.223.129.4 918801641445^0^access
101.223.129.4 918801641445^0^privat
101.223.129.4 918801641445^0^locat
101.223.129.4 918801641445^0^thank

我已经使用以下代码在 pandas 中阅读了此内容:

cols = ['msisdn','prob','desc']
txt_file = pd.read_csv('app_desc_fltrd.txt',sep="^",header=0,names=cols,low_memory=False)

现在我想使用此数据创建一个数据透视表

pivot_tbl = pd.pivot_table(data,index=['msisdn','prob'],columns=['desc'])

Traceback (most recent call last):
  File "transpose_txt_file.py", line 22, in <module>
    create_pivot(txt_file,cols)
  File "transpose_txt_file.py", line 15, in create_pivot
    pivot_tbl = pd.pivot_table(data,index=['msisdn','prob'],columns=['desc'])
  File "/opt/anaconda2.2/lib/python2.7/site-packages/pandas/util/decorators.py", line 88, in wrapper
    return func(*args, **kwargs)
  File "/opt/anaconda2.2/lib/python2.7/site-packages/pandas/util/decorators.py", line 88, in wrapper
    return func(*args, **kwargs)
  File "/opt/anaconda2.2/lib/python2.7/site-packages/pandas/tools/pivot.py", line 115, in pivot_table
    agged = grouped.agg(aggfunc)
  File "/opt/anaconda2.2/lib/python2.7/site-packages/pandas/core/groupby.py", line 676, in agg
    return self.aggregate(func, *args, **kwargs)
  File "/opt/anaconda2.2/lib/python2.7/site-packages/pandas/core/groupby.py", line 2615, in aggregate
    return getattr(self, arg)(*args, **kwargs)
  File "/opt/anaconda2.2/lib/python2.7/site-packages/pandas/core/groupby.py", line 691, in mean
    return self._cython_agg_general('mean')
  File "/opt/anaconda2.2/lib/python2.7/site-packages/pandas/core/groupby.py", line 2535, in _cython_agg_general
    new_items, new_blocks = self._cython_agg_blocks(how, numeric_only=numeric_only)
  File "/opt/anaconda2.2/lib/python2.7/site-packages/pandas/core/groupby.py", line 2585, in _cython_agg_blocks
    raise DataError('No numeric types to aggregate')
pandas.core.groupby.DataError: No numeric types to aggregate

只需稍加更改，我就能成功创建数据透视表。

pivot_tbl = pd.pivot_table(txt_file ,index=['msisdn'],columns=['desc'],values='desc')

但在这种情况下，我丢失了 msisdn 和 prob 字段的映射。

关于如何创建数据透视表的任何建议，行中包含 ['msisdn','prob']，列中包含 ['desc'] 以及值字段中是否存在 desc 的指示变量将不胜感激。

基本上对于给定的msisdn，概率永远不会改变。如果这能以任何方式提供帮助就好了。

谢谢。

Answer 1

您需要使用 pivot_table 继续操作 - 您的代码中有一些拼写错误:

import pandas as pd

pd.pivot_table(txt_file, rows=['msisdn','prob'], cols='desc',aggfunc=len)

您还可以使用groupby:

txt_file.groupby(['prob','msisdn'])['desc'].value_counts().unstack()