Operating on multiindex dataframe

Question

My dataframe head() looks like this:

                                         followers  following
experience   userid date                                     
Intermediate 0      2010-01-02 05:28:38      84330       1331
                    2010-01-02 18:46:36      84330       1331
                    2010-01-02 18:47:22      84330       1331
                    2010-01-02 18:50:12      84330       1331
                    2010-01-02 23:08:55      84330       1331

Where I have more rows. For each userid, I'd like to subtract the first followers value (e.g., in example above, subtract 84330 from all dates in userid=0). Is there some apply() command that will do this?

What do you mean by subtract? You mean remove the rows or subtract 84330 from a datetime which doesn't make sense to me — EdChum
– EdChum, Commented Oct 2, 2015 at 20:15
@EdChum: I mean subtract 84330 from the followers. Meaning for the rows shown above, make those followers 0. However future rows may have more, so their value will be their current value minus 84330. — David
– David, Commented Oct 3, 2015 at 16:54

user29791 · Accepted Answer · 2015-10-02 21:14:36Z

1

find the first row for each userid:

cut = df.groupby('userid').first()

Then merge it back to the original table:

cut.columns = cut.columns.map(lambda x: str(x) + '_a')
df2 = df.merge(cut, left_on=['userid'], right_index=True, how='left')

Then remove these rows that match the first rows value

new = df2[df2['followers'] != df2['followers']][['userid','date','followers']]

answered Oct 2, 2015 at 21:14

user29791

3321 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Over a year ago

This is close to what I want to do. Please see my comment to EdChum above

user29791 Over a year ago

You can replace the last line with: df2["diff"] = df2["followers"] - df2["followers_a"] Which will add a new column with the subtract between both values

Collectives™ on Stack Overflow

Operating on multiindex dataframe

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related