Pandas data frame rate operation

Question

I am just starting with pandas and I would like to know how to count the number of documents (unique) per year per company

My details: DF

  year  document_id  company
0   1999    3     Orange
1   1999    5     Orange
2   1999    3     Orange
3   2001    41    Banana
4   2001    21    Strawberry
5   2001    18    Strawberry
6   2002    44    Orange

In the end, I would like to have a new dataframe like this

  year    document_id  company nbDocument
0   1999    [3,5]     Orange       2
1   2001    [21]      Banana       1
2   2001    [21,18]   Strawberry   2
3   2002    [44]      Orange       1

I tried:

count2 = apyData.groupby(['year','company']).agg({'document_id': pd.Series.value_counts})

But with the operation, groupbyI cannot have such a structure and consider a unique value for Orange in 1999, for example, is there a way to do this?

thanks

Answer 1

You can create a new one DataFrameand add a unique one document_idusing list comprensionas follows:

result = pd.DataFrame()
result['document_id'] = df.groupby(['company', 'year']).apply(lambda x: [d for d in x['document_id'].drop_duplicates()])

Now that you have a list of unique ones document_id, you only need to get the length of this list:

result['nbDocument'] = result.document_id.apply(lambda x: len(x))

:

result.reset_index().sort_values(['company', 'year'])

      company  year document_id  nbDocument
0      Banana  2001        [41]           1
1      Orange  1999      [3, 5]           2
2      Orange  2002        [44]           1
3  Strawberry  2001    [21, 18]           2

Answer 2

agg, document_id :

print apyData

   afx  year  document_id     company
0    0  1999            3      Orange
1    1  1999            5      Orange
2    2  1999            3      Orange
3    3  2001           41      Banana
4    4  2001           21  Strawberry
5    5  2001           18  Strawberry
6    6  2002           44      Orange

f = {'nbDocument' : lambda x: len(x.unique()), 'document_id' : lambda x: tuple(x)}
count2 = apyData.groupby(['year','company']).document_id.agg(f).reset_index()
print count2

   year     company  nbDocument document_id
0  1999      Orange           2   (3, 5, 3)
1  2001      Banana           1       (41,)
2  2001  Strawberry           2    (21, 18)
3  2002      Orange           1       (44,)

#convert to list
count2['document_id'] = count2['document_id'].apply(lambda x: list(x))
#reorder columns
count2 = count2[['year','document_id','company','nbDocument']]
print count2

   year document_id     company  nbDocument
0  1999   [3, 5, 3]      Orange           2
1  2001        [41]      Banana           1
2  2001    [21, 18]  Strawberry           2
3  2002        [44]      Orange           1

EDIT:

'document_id' : lambda x: list(x) agg, :

ValueError:

tuple, list.

EDIT1:

:

def je(apyData):
    f = {'nbDocument' : lambda x: len(x.unique()), 'document_id' : lambda x: tuple(x)}
    count2 = apyData.groupby(['year','company']).document_id.agg(f).reset_index()
    count2['document_id'] = count2['document_id'].apply(lambda x: list(x))
    return count2

def mm(df):
    out = pd.DataFrame()
    grouped = df.groupby(['year', 'company'])
    out['nbDocument'] = grouped.apply(lambda x: list(x['document_id'].drop_duplicates()))
    out['document_id'] = out['nbDocument'].apply(lambda x: len(x))
    return (out.reset_index().sort_values(['year', 'company']))

def st(df):
    result = pd.DataFrame()
    result['document_id'] = df.groupby(['company', 'year']).apply(lambda x: [d for d in x['document_id'].drop_duplicates()])    
    result['nbDocument'] = result.document_id.apply(lambda x: len(x))
    return result.reset_index().sort_values(['company', 'year'])

print mm(apyData)
print st(apyData)
print je(apyData)

:

In [48]: %timeit je(apyData)
100 loops, best of 3: 3.08 ms per loop

In [49]: %timeit mm(apyData)
100 loops, best of 3: 5.73 ms per loop

In [50]: %timeit st(apyData)
100 loops, best of 3: 5.8 ms per loop

Answer 3

This gives the desired result:

out = pd.DataFrame()
grouped = df.groupby(['year', 'company'])
out['nbDocument'] = grouped.apply(lambda x: list(x['document_id'].drop_duplicates()))
out['document_id'] = out['nbDocument'].apply(lambda x: len(x))
print(out.reset_index().sort_values(['year', 'company']))

   year     company nbDocument  document_id
0  1999      Orange     [3, 5]            2
1  2001      Banana       [41]            1
2  2001  Strawberry   [21, 18]            2
3  2002      Orange       [44]            1