Python Pandas: Transform multiindex dataframe

Question

I have this dataset as a pandas DataFrame with multiIndex:

                       cnt                                    
loginsmonth     2014-02-01  2014-03-01  2014-04-01  2014-05-01
app regmonth                                                  
1   2014-02-01        6069        1837         107          54
    2014-03-01           0       10742        2709        1387
    2014-04-01           0           0        5584        1103
    2014-05-01           0           0           0        5584

I need to transform it into a percentual value related to the diagonal:

                       cnt                                    
loginsmonth     2014-02-01  2014-03-01  2014-04-01  2014-05-01
app regmonth                                                  
1   2014-02-01   6069/6069   1837/6069    107/6069     54/6069
    2014-03-01           0 10742/10742  2709/10742  1387/10742
    2014-04-01           0           0   5584/5584   1103/5584
    2014-05-01           0           0           0   5584/5584

user3649071 · Accepted Answer · 2014-06-03 03:04:29Z

1

If you dont mind switching the diagonal around, you could do this:

#create dataset
data = pd.DataFrame({'2014-02-01': [6069,0,0,0], '2014-03-01': [1837,1042,0,0], '2014-04-01': [107,209,5584,0], '2014-05-01': [54,1387,1103,5384]}, index = [[1,1,1,1], ['2014-02-01', '2014-03-01', '2014-04-01', '2014-05-01']], columns = ['2014-02-01', '2014-03-01', '2014-04-01', '2014-05-01'])

#transpose dataset
data = data.T

#compute percentages
for x, col in enumerate(data):
    data[col] = [item/data[col][x] for item in data[col]]

#you can always re transpose back!
data = data.T

edited Jun 3, 2014 at 3:04

answered Jun 3, 2014 at 2:56

user3649071

661 silver badge7 bronze badges

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

1 Comment

jbastos Over a year ago

If I add another group to the dataset, say, app=2, like if I have this as the last line: "2 2014-02-01 3000 2000 100 50" I would get an "index out of bounds" error.

Collectives™ on Stack Overflow

Python Pandas: Transform multiindex dataframe

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related