计算pandas中列组合的总和,按行计算,输出文件名为所述组合

2024-03-29 11:35:11 发布

您现在位置:Python中文网/ 问答频道 /正文

我正在寻找一种方法来生成一个csv文件的特定组合的数据列在一个数据帧。在

我的数据看起来像这样(除了200多行)

+-------------------------------+-----+----------+---------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+---------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+
|            Species            | OGT |  Domain  |       A       |      C       |      D       |      E       |      F       |      G       |      H       |      I       |      K       |       L       |      M       |      N       |      P       |      Q       |      R       |      S       |      T       |      V       |      W       |      Y       |
+-------------------------------+-----+----------+---------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+---------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+
| Aeropyrum pernix              |  95 | Archaea  |  9.7659115711 | 0.6720465616 | 4.3895390781 | 7.6501943794 | 2.9344881615 | 8.8666657183 | 1.5011817208 | 5.6901432494 | 4.1428307243 | 11.0604191603 |   2.21143353 | 1.9387130928 | 5.1038552753 | 1.6855017182 | 7.7664358772 |  6.266067034 | 4.2052190807 | 9.2692433532 |  1.318690698 | 3.5614200159 |
| Argobacterium fabrum          |  26 | Bacteria | 11.5698896021 | 0.7985475923 | 5.5884500155 | 5.8165463343 | 4.0512504104 | 8.2643271309 | 2.0116736244 | 5.7962804605 | 3.8931525401 |  9.9250463349 | 2.5980609708 | 2.9846761128 | 4.7828063605 | 3.1262365491 | 6.5684282943 | 5.9454781844 | 5.3740045968 | 7.3382308193 | 1.2519739683 | 2.3149400984 |
| Anaeromyxobacter dehalogenans |  27 | Bacteria | 16.0337898849 | 0.8860252895 | 5.1368827707 | 6.1864992608 | 2.9730203513 | 9.3167603253 | 1.9360386851 |  2.940143349 | 2.3473650439 |  10.898494736 | 1.6343905351 | 1.5247123262 | 6.3580285706 | 2.4715303021 | 9.2639057482 | 4.1890063803 | 4.3992339725 | 8.3885969061 | 1.2890166336 | 1.8265589289 |
| Aquifex aeolicus              |  85 | Bacteria |  5.8730327277 |  0.795341216 | 4.3287799008 | 9.6746388172 | 5.1386954322 | 6.7148035486 | 1.5438364179 | 7.3358775924 | 9.4641440609 | 10.5736658776 | 1.9263080969 | 3.6183861236 | 4.0518679067 | 2.0493569604 | 4.9229955632 | 4.7976564501 | 4.2005259246 | 7.9169763709 | 0.9292167138 | 4.1438942987 |
| Archaeoglobus fulgidus        |  83 | Archaea  |  7.8742687687 | 1.1695110027 | 4.9165979364 | 8.9548767369 |  4.568636662 | 7.2640358917 | 1.4998752909 | 7.2472039919 | 6.8957233203 |  9.4826333048 | 2.6014466253 |  3.206476915 | 3.8419576418 | 1.7789787933 | 5.7572748236 | 5.4763351139 | 4.1490633048 | 8.6330814159 | 1.0325605451 | 3.6494619148 |
+-------------------------------+-----+----------+---------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+---------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+

我想做的是找到一种方法,用species、OGT和其他一些列(比如a、C、E&G)以及这些特定值的百分比总和来生成csv。在

所以输出看起来是这样的:(这些总和是加起来的)

在ACEG.csv版在

^{pr2}$

这样做的目的是让我可以对每列(A-Y)的1000万个组合中的每一个都这样做,但我认为这是一个简单的for循环。最初我试图在R中实现这一点,但经过深思熟虑,在python中使用pandas可能是一个更好的选择。在


Tags: 文件csv数据方法domainspecies总和bacteria
3条回答

不是对最初问题的回答,但考虑到讨论,这可能是有用的。在

目标是找到列的组合,使列和与OGT具有最大的相关性。这很容易,因为协方差是双线性的:

  • cov(OGT, A+B) = cov(OGT, A) + cov(OGT, B)。在

我依靠两个简化的假设:

  1. 因素A、B、C等是独立的。在
  2. 物种的权重相等。在
  3. 每个因子的方差是1。在

想法是:

  1. 规范化所有列,使其具有单位方差(即假设3)。在
  2. 计算每列OGT的协方差。在
  3. 按协方差递减的顺序对因子A、B、C进行排序。最佳组合将作为这种安排的前缀出现。在
  4. 我们应该选择哪个前缀?与标准差之和最大的那个。由于第1步的规范化,对于大小为n的前缀,每个前缀之和的标准偏差仅为sqrt(n),因此需要在序列中找到一个最大索引,这很容易。在

这可能比检查所有可能的组合要快一点。在


import pandas as pd
import numpy as np

# set up fake data
import string

df = pd.DataFrame(np.random.rand(3, 26), columns=list(string.ascii_uppercase))

df["species"] = ["dog", "cat", "human"]
df["OGT"] = np.random.randint(0, 100, 3)
df = df.set_index("species")

# actual work
alpha_cols = list(string.ascii_uppercase)
# normalize standard deviations of each column
df = df[alpha_cols + ["OGT"]].div(df.std(0), axis=1)
# compute correlations (= covariances) of OGT with each column
corrs = df.corrwith(df.OGT).sort_values(ascending=False)
del corrs["OGT"]

# sort covariances in order from the greatest to the smallest
# compute cumulative sums
# divide by standard deviation of a group (i.e. sqrt(n) at index n-1)
cutoff = (corrs.cumsum() / np.sqrt(np.arange(corrs.shape[0]) + 1)).idxmax()
answer = sorted(corrs.loc[:cutoff].index.values)
print(answer)

# e.g.
# ['B', 'I', 'K', 'O', 'Q', 'S', 'U', 'V', 'Y']

以下是您可以尝试的方法:

from itertools import product
from string import ascii_uppercase
import pandas as pd

combinations = [''.join(i) for i in product(ascii_uppercase, repeat = 4)]

for combination in combinations:
    new_df = df[['Species', 'OGT']]
    new_df['Sum of percentage'] = df[list(combination)]
    new_df.to_csv(combination + '.csv')

===

按照Yakym Pirozhenko的评论进行编辑,combinations应该使用itertools.combinations来避免类似'AAAA'的重复:

^{pr2}$

像这样?在

def subset_to_csv(cols):
    df['Sum of percentage'] = your_data[list(cols)].sum(axis=1)
    df.to_csv(cols + '.csv')

df = your_data[['Species', 'OGT']]

for c in your_list_of_combinations:
    subset_to_csv(c)

其中cols是一个字符串,包含要子集的列,例如:'ABC'

相关问题 更多 >