CODEWITHSUNDEEP
pythonpandasdataframe
HRDSL
HRDSL

Reputation: 761

Problem applying lambda expression to a groupby object

I'm creating a program which is intended to analyze the content of a Wiki dump. It must count the number of users which have edited more than 5 articles each month. This is my dataframe:

{'revision_id': {0: 17447, 1: 23240, 2: 23241, 3: 23242, 4: 23243,
                 5: 23245, 6: 24401, 7: 3055, 8: 3056, 9: 3057},
 'page_id': {0: 4433, 1: 6639, 2: 6639, 3: 6639, 4: 6639, 5: 6639, 6: 6639, 7: 1896, 8: 1896, 9: 1896},
 'page_title': {0: 'Slow Gin Finn', 1: '43 con Leche', 2: '43 con Leche', 3: '43 con Leche', 4: '43 con Leche',
                5: '43 con Leche', 6: '43 con Leche', 7: '57 Chevy', 8: '57 Chevy', 9: '57 Chevy'},
 'page_ns': {0: 0, 1: 0, 2: 0, 3: 0, 4: 0, 5: 0, 6: 0, 7: 0, 8: 0, 9: 0},
 'timestamp': {0: '2011-02-02 23:16:11', 1: '2014-03-25 00:48:27', 2: '2014-03-25 00:48:43',
               3: '2014-03-25 00:49:48', 4: '2014-03-25 00:50:22', 5: '2014-03-25 00:57:02',
               6: '2014-08-11 16:47:53', 7: '2005-04-28 22:32:02', 8: '2005-04-29 03:42:39',
               9: '2006-04-05 12:19:00'},
 'contributor_id': {0: 3096602, 1: 1416077, 2: 1416077, 3: 1416077, 4: 1416077, 5: 1416077, 6: 1416077, 7: 740443,
                    8: 740443, 9: 740560},
 'contributor_name': {0: 'Babyjabba', 1: 'Sings-With-Spirits', 2: 'Sings-With-Spirits', 3: 'Sings-With-Spirits',
                      4: 'Sings-With-Spirits', 5: 'Sings-With-Spirits', 6: 'Sings-With-Spirits', 7: 'FlexiSoft',
                      8: 'FlexiSoft', 9: 'Vampiric.Media'},
 'bytes': {0: 558, 1: 284, 2: 288, 3: 339, 4: 339, 5: 374, 6: 378, 7: 294, 8: 238, 9: 268}}

Which is composed by 8 columns: revision_id, page_id, page_title, page_ns, timestamp, contributor_id, contributor_name and bytes.

I have the following code in order to process the wiki dump and put it into a dataframe, and then, in order to get the number of pages each user has edited per month, I create a groupby object grouping by timestamp and contributor_name. Then, I managed to create another dataframe which contains only those users which have more than 5 editions each month:

import pandas as pd 
df = pd.read_csv('/home/Peter/hi/hi2/data/cocktails.csv', delimiter=';', quotechar='|', index_col='revision_id') 
df['timestamp'] = pd.to_datetime(df['timestamp'])
# Filter out anonymous users: 
df = df[df['contributor_name'] != 'Anonymous']
# get the number of edits each user has done each month: this is a series
editions_per_user_monthly = df.groupby([pd.Grouper(key='timestamp', freq='MS'), pd.Grouper(key='contributor_name')]).size()
# filter users with number >= requested 
df2 = (editions_per_user_monthly[editions_per_user_monthly >= 5]).to_frame(name='pages_edited')

Once I have the df2 dataframe, I wanted to apply this lambda expression in order to know how many users with more than 5 editions each month contains:

> Blockquote series = df2.apply(lambda x: len(x))

But it doesn't work. ¿Can anyone help me with this task?

Upvotes: 0

Views: 95

Answers (1)

Alex
Alex

Reputation: 7065

I've cleaned up the code a bit, this was hard to read and understand what is going on. Have a look here for tips on how to format/write a question that is more likely to get you help.

import pandas as pd 
df = pd.read_csv('data.csv', sep=';', quotechar='|', index_col='revision_id') 
df['timestamp'] = pd.to_datetime(df['timestamp'])
# Filter out anonymous users: 
df = df[df['contributor_name'] != 'Anonymous']
# get the number of edits each user has done each month
monthly_edits_per_user = df.groupby([pd.Grouper(key='timestamp', freq='MS'),
                                    'contributor_name']).size()
# filter users with number >= requested 
df2 = monthly_edits_per_user[monthly_edits_per_user >= 5].to_frame(name='pages_edited').reset_index()

This produces:

   timestamp    contributor_name  pages_edited
0 2014-03-01  Sings-With-Spirits             5

I've added some more dummy data here to show the next aggregation:

   timestamp    contributor_name  pages_edited
0 2014-03-01  Sings-With-Spirits             5
1 2014-05-01                 foo             7
2 2014-05-01                 bar            10
3 2014-06-01                 foo             5
4 2014-10-01                 baz             8

Now you can add a new column to this DataFrame using this:

df2['monthly_sum'] = df2.groupby('timestamp')['pages_edited'].transform(sum)

   timestamp    contributor_name  pages_edited  monthly_sum
0 2014-03-01  Sings-With-Spirits             5            5
1 2014-05-01                 foo             7           17
2 2014-05-01                 bar            10           17
3 2014-06-01                 foo             5            5
4 2014-10-01                 baz             8            8

df2['monthly_sum_per_user'] = df2.groupby(['timestamp', 'contributor_name'])['pages_edited'].transform(sum)

   timestamp    contributor_name  pages_edited  monthly_sum  monthly_sum_per_user
0 2014-03-01  Sings-With-Spirits             5            5                     5
1 2014-05-01                 foo             7           17                     7
2 2014-05-01                 bar            10           17                    10
3 2014-06-01                 foo             5            5                     5
4 2014-10-01                 baz             8            8                     8

Upvotes: 1

Related Questions