如何比较数据帧的聚合部分?

2024-03-28 15:02:46 发布

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

是否可以比较数据框中的部分列?我有下面的Dataframe示例,其中保存了4种语言(en、de、nl、ua),每种语言都应该具有相同的键/相同数量的键,但具有不同的值(将static列保留在那里以供完成,因为我确实有一个静态列,其值始终保持不变)。你知道吗

static  │ langs   │ keys   │ values

x       │ en      │ key_1  │ value_en_1
x       │ en      │ key_2  │ value_en_2
x       │ en      │ key_3  │ value_en_3
x       │ de      │ key_1  │ value_de_1
x       │ de      │ key_2  │ value_de_2
x       │ de      │ key_3  │ value_de_3
x       │ nl      │ key_1  │ value_nl_1
x       │ nl      │ key_2  │ value_nl_2
x       │ ua      │ key_1  │ value_ua_1

我需要检查每种语言与英语(这里是“en”)相比缺少了哪些键和多少键,因此类似这样的内容将是所需的输出:

│ Lang │ Static   │ # Missing │ Keys          │ 
│ de   │ x        │ 0         │               │ 
│ nl   │ x        │ 1         │ key_3         │ 
│ ua   │ x        │ 2         │ key_2, key_3  │

这是我目前的进展:

import pandas as pd

# this is read from a csv, but I'll leave it as list of lists for simplicity
rows = [
    ['x', 'en', 'key_1', 'value_en_1'],
    ['x', 'en', 'key_2', 'value_en_2'],
    ['x', 'en', 'key_3', 'value_en_3'],
    ['x', 'de', 'key_1', 'value_de_1'],
    ['x', 'de', 'key_2', 'value_de_2'],
    ['x', 'de', 'key_3', 'value_de_3'],
    ['x', 'nl', 'key_1', 'value_nl_1'],
    ['x', 'nl', 'key_2', 'value_nl_2'],
    ['x', 'ua', 'key_1', 'value_en_1']
]

# create DataFrame out of rows of data
df = pd.DataFrame(rows, columns=["static", "language", "keys", "values"])
# print out DataFrame
print("Dataframe: ", df)

# first group by language and the static column
df_grp = df.groupby(["static", "language"])

# try to sum the number of keys and values per each language
df_summ = df_grp.agg(["count"])

# print out the sums
print()
print(df_summ)

# how to compare?
# how to get the keys?

这是df_summ的输出:

                 keys values
                count  count
static language             
x      de           3      3
       en           3      3
       nl           2      2
       ua           1      1

在这一点上我不知道如何继续。我很感激你的帮助/提示。你知道吗

另外,这是在python3.5上实现的。你知道吗


Tags: ofthekey语言dfvaluenlstatic
3条回答

编辑:

#get set per groups by static and language
a = df.groupby(["static",'language'])['keys'].apply(set).reset_index()
#filter only en language per group by static and create set
b = df[df['language'] == 'en'].groupby("static")['keys'].apply(set)
#subtract mapped set b and join
c = (a['static'].map(b) -  a['keys']).str.join(', ').rename('Keys')
#substract lengths
m = (a['static'].map(b).str.len() - a['keys'].str.len()).rename('Missing')

df = pd.concat([a[['static','language']], m, c], axis=1)
print (df)
  static language  Missing          Keys
0      x       de        0              
1      x       en        0              
2      x       nl        1         key_3
3      x       ua        2  key_3, key_2

编辑:

我尝试更改数据:

rows = [
    ['x', 'en', 'key_1', 'value_en_1'],
    ['x', 'en', 'key_2', 'value_en_2'],
    ['x', 'en', 'key_3', 'value_en_3'],
    ['x', 'de', 'key_1', 'value_de_1'],
    ['x', 'de', 'key_2', 'value_de_2'],
    ['x', 'de', 'key_3', 'value_de_3'],
    ['x', 'nl', 'key_1', 'value_nl_1'],
    ['x', 'nl', 'key_2', 'value_nl_2'],
    ['x', 'ua', 'key_1', 'value_en_1'],
    ['y', 'en', 'key_1', 'value_en_1'],
    ['y', 'en', 'key_2', 'value_en_2'],
    ['y', 'de', 'key_4', 'value_en_3'],
    ['y', 'de', 'key_1', 'value_de_1'],
    ['y', 'de', 'key_2', 'value_de_2'],
    ['y', 'de', 'key_3', 'value_de_3'],
    ['y', 'de', 'key_5', 'value_nl_1'],
    ['y', 'nl', 'key_2', 'value_nl_2'],
    ['y', 'ua', 'key_1', 'value_en_1']
]

# create DataFrame out of rows of data
df = pd.DataFrame(rows, columns=["static", "language", "keys", "values"])
# print out DataFrame
#print(df)

输出为:

print (df)
  static language  Missing          Keys
0      x       de        0              
1      x       en        0              
2      x       nl        1         key_3
3      x       ua        2  key_3, key_2
4      y       de       -3              
5      y       en        0              
6      y       nl        1         key_1
7      y       ua        1         key_2

问题是对于de对于y静态,在en语言中有更多的键。你知道吗

# First we group with `language` and aggregate `static` with `min` (it's always the same anyway)
# and `keys` with a lambda function that creates a `set`.
In [2]: grouped = df.groupby('language').agg({'static': 'min', 'keys': lambda x: set(x)})

# Then we get the missing keys...
In [3]: missing = (grouped['keys']['en'] - grouped['keys'])

# ... and count them
In [4]: missing_counts = missing.apply(len).rename('# Missing')

# Then we join all of this together and replace the keys with a joined string.
In [5]: grouped.drop('keys', axis=1).join(missing_counts).join(missing.apply(', '.join)).reset_index()
Out[5]:
  language static  # Missing          keys
0       de      x          0
1       en      x          0
2       nl      x          1         key_3
3       ua      x          2  key_2, key_3

因为您在问题中添加了R标记,下面介绍如何使用tidyrdplyr

library(dplyr);library(tidyr)
df %>% 
  complete(nesting(static, langs), keys) %>%
  group_by(langs)%>%
  summarise(Static=max(static),
            Missing=sum(is.na(values)),
            Keys=toString(keys[is.na(values)])
            )

  langs Static Missing         Keys
  <chr>  <chr>   <int>        <chr>
1    de      x       0             
2    en      x       0             
3    nl      x       1        key_3
4    ua      x       2 key_2, key_3

数据

df <- read.table(text="static   langs    keys    values
'x' 'en' 'key_1' 'value_en_1'
'x' 'en' 'key_2' 'value_en_2'
'x' 'en' 'key_3' 'value_en_3'
'x' 'de' 'key_1' 'value_de_1'
'x' 'de' 'key_2' 'value_de_2'
'x' 'de' 'key_3' 'value_de_3'
'x' 'nl' 'key_1' 'value_nl_1'
'x' 'nl' 'key_2' 'value_nl_2'
'x' 'ua' 'key_1' 'value_en_1'",header=TRUE,stringsAsFactors = FALSE)

相关问题 更多 >