logo标签列表

按另一列的值对列值在pandas数据帧中进行计算共现。

pythonpandasdata-sciencedaskdata-analysis
4

问题

我正在使用Python 3.7.7上的Pandas。我想计算变量x的分类值在另一个变量y的值分组下的互信息。我的数据看起来像下面的表格：

+-----+-----+
|  x  |  y  |
+-----+-----+
| x_1 | y_1 |
| x_2 | y_1 |
| x_3 | y_1 |
| x_1 | y_2 |
| x_2 | y_2 |
| x_4 | y_3 |
| x_6 | y_3 |
| x_9 | y_3 |
| x_1 | y_4 |
| ... | ... |
+-----+-----+

我希望有一个数据结构（pandas MultiIndex系列/数据框或numpy矩阵或其他合适的东西），它可以存储给定特定y_k值的x_i和x_j对的共现次数。实际上，这将非常好，例如，可以轻松计算PMI：

+-----+-----+--------+-------+
| x_i | x_j |  cooc  |  pmi  |
+-----+-----+--------+-------+
| x_1 | x_2 |        |       |
| x_1 | x_3 |        |       |
| x_1 | x_4 |        |       |
| x_1 | x_5 |        |       |
| ... | ... |   ...  |  ...  |
+-----+-----+--------+-------+

有没有适合的内存高效方式？

附注：我正在使用相当大的数据（40k个不同的x值和8k个不同的y值，总共有300k个(x,y)条目，因此希望能够使用内存友好且经过优化的方法（也许依赖于第三方库，如Dask）

更新

非优化解决方案

我想出了一种使用pd.crosstab的解决方案。这里提供一个小例子：

import numpy as np
import pandas as pd
df = pd.DataFrame(np.random.randint(0,100,size=(100, 2)), columns=list('xy'))
"""
df:
  +-----+-----+
  |  x  |  y  |
  +-----+-----+
  | 4   | 99  |
  | 1   | 39  |
  | 39  | 56  |
  | ..  | ..  |
  | 59  | 20  |
  | 82  | 57  |
  +-----+-----+
 100 rows × 2 columns
"""
# Compute cross tabulation:
crosstab = pd.crosstab(df["x"], df["y"])
"""
crosstab:
  +------+-----+-----+-----+-----+
  |  y   |  0  |  2  |  3  | ... |
  |  x   +-----+-----+-----+-----+
  |  1   |  0  |  0  |  0  | ... |
  |  2   |  0  |  0  |  0  | ... |
  | ...  | ... | ... | ... | ... |
  +------+-----+-----+-----+-----+
 62 rows × 69 columns
"""
# Initialize a pandas MultiIndex Series storing PMI values
import itertools
x_pairs = list(itertools.combinations(crosstab.index, 2))
pmi = pd.Series(0, index = pd.MultiIndex.from_tuples(x_pairs))
"""
pmi:
  +-------------+-----+
  |    index    | val |
  +------+------|     |
  |  x_i |  x_j |     |
  +------+------+-----+
  |  1   |  2   |  0  |
  |      |  4   |  0  |
  |  ... |  ... | ... |
  |  95  |  98  |  0  |
  |      |  99  |  0  |
  |  96  |  98  |  0  |
  +------+------+-----+
 Length: 1891, dtype: int64
"""

然后，我用来填充Series的循环结构如下：
for x1, x2 in x_pairs:
    pmi.loc[x1, x2] = crosstab.loc[[x1, x2]].min().sum() / (crosstab.loc[x1].sum() * crosstab.loc[x2].sum())

这不是一个可选的解决方案，即使在小型用例中性能也很差。
1
我遇到了同样的问题，但最终通过过滤掉出现频率最低的数据来创建共现矩阵。 - Pygirl
这将是减少条目的好方法，但它并不能解决大数据规模问题。实际上，在我的情况下，共现频率非常低，基于频率进行过滤并不是最好的解决方案。 - Davide
1
假设只有一些x的组合会被观察到，使用稀疏矩阵表示是否公平？ - SultanOrazbayev
1
没错，@SultanOrazbayev，从40k个不同的“x”值和8k个不同的“y”值中，初始数据框中的300k行并没有涵盖所有160万个“x”值的组合。 - Davide
1
@SultanOrazbayev，我终于使用稀疏矩阵做到了，谢谢！ - Davide
1个回答
3

优化的解决方案

最终，我使用scipy稀疏矩阵进行中间计算，以更加节省内存地计算交叉出现：

import pandas as pd
import numpy as np
from scipy.sparse import csr_matrix

def df_compute_cooccurrences(df: pd.DataFrame, column1: str, column2: str) -> pd.DataFrame:
   
    # pd.factorize encode the object as an enumerated type or categorical variable, returning:
    # - `codes` (ndarray): an integer ndarray that’s an indexer into `uniques`.
    # - `uniques` (ndarray, Index, or Categorical): the unique valid values
    # see more at https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.factorize.html

    i, rows = pd.factorize(df[column1])
    # i    -> array([     0,      0,      0, ..., 449054,      0,      1])
    # rows -> Index(['column1_label1', 'column1_label2', ...])

    j, cols = pd.factorize(df[column2])
    # j    -> array([    0,     1,     2, ..., 28544,    -1,    -1])
    # cols -> Float64Index([column2_label1, column2_label2, ...])

    ij, tups = pd.factorize(list(zip(i, j)))
    # ij   -> array([      0,       1,       2, ..., 2878026, 2878027, 2878028])
    # tups -> array([(0, 0), (0, 1), (0, 2), ..., (449054, 28544), (0, -1), (1, -1)]

    # Then we can finally compute the crosstabulation matrix
    crosstab = csr_matrix((np.bincount(ij), tuple(zip(*tups))))
    # If we convert directly this into a Dataframe with
    # pd.DataFrame.sparse.from_spmatrix(crosstab, rows, cols)
    # we have the same result as using 
    # https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.crosstab.html
    # but we obtained it in a memory-friendly way (allowing big data processing)

    # In order to obtain the co-occurrences matrix for column 1, 
    # we have to multiply the crosstab matrix for its transposed 
    coocc = crosstab.dot(crosstab.transpose())
    
    # Then we can finally return the co-occurence matrix in in a DataFrame form
    return pd.DataFrame.sparse.from_spmatrix(coocc, rows, rows)

这里提供一个小例子：

import pandas as pd
import numpy as np
from scipy.sparse import csr_matrix

def df_compute_cooccurrences(df: pd.DataFrame, column1: str, column2: str) -> pd.DataFrame:
    i, rows = pd.factorize(df[column1])
    j, cols = pd.factorize(df[column2])
    ij, tups = pd.factorize(list(zip(i, j)))
    crosstab = csr_matrix((np.bincount(ij), tuple(zip(*tups))))
    coocc = crosstab.dot(crosstab.transpose())
    return pd.DataFrame.sparse.from_spmatrix(coocc, rows, rows)

df = pd.DataFrame(zip([1,1,1,2,2,3,4],["a","a","a","a","a","b","b"]), columns=list('xy'))
"""
df:
  +-----+-----+
  ¦  x  ¦  y  ¦
  +-----+-----+
  |  1  |  a  |
  |  1  |  a  |
  |  1  |  a  |
  |  2  |  a  |
  |  2  |  a  |
  |  3  |  b  |
  |  4  |  b  |
  +-----+-----+
"""
cooc_df = df_compute_cooccurrences(df, "x", "y")
"""
cooc_df:
    +---+---+---+---+
    ¦ 1 | 2 | 3 | 4 |
+---+---+---+---+---+
¦ 1 ¦ 9 | 6 | 0 | 0 |
¦ 2 ¦ 6 | 4 | 0 | 0 |
¦ 3 ¦ 0 | 0 | 1 | 1 |
¦ 4 ¦ 0 | 0 | 1 | 1 |
+---+---+---+---+---+
"""
cooc_df2 = df_compute_cooccurrences(df, "y", "x")
"""
cooc_df2:
    +----+----+
    ¦  a ¦  b ¦
+---+----+----+
¦ a ¦ 13 |  0 |
¦ b ¦  0 |  2 |
+---+----+----+
"""
看起来不错，最后并不需要进行PMI计算？ - SultanOrazbayev
1
给定共现情况，进一步计算很容易（例如通过将每个共现值除以两个元素的绝对出现次数之和来计算PMI），但我想在答案中提供一种干净的方法来计算共现而不必处理进一步的特定情况计算。 - Davide
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接