CODEWITHSUNDEEP
pythonnumpypandas
jbastos
jbastos

Reputation: 1217

Python Pandas: Transform multiindex dataframe

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

Upvotes: 1

Views: 571

Answers (1)

user3649071
user3649071

Reputation: 66

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

Upvotes: 1

Related Questions