Search code examples
pythonpandaspivot-tablemulti-index

Editing data with Conditional Functions within pandas Multi index

I'm fairly new to pandas and I am struggling to edit values within a pivot table using multiple conditions.

I have the below pivot table, I'm trying to edit the data for Desktop & Mobile under cost. The two are mutually exclusive so if an account has a cost against Desktop it gets Mobile for free (and mobile cost should be zeroed) and if there's 0 cost under desktop then Mobile should be 25.

enter image description here

code for the pivot table looks like this

cost_merge = pd.pivot_table(cost_merge, values=['Cost'], 
index=['ID', 'First Name', 'Last Name', 'Account Number', 'Creation Date', 'Country/Region'], 
columns=['Service Name'], fill_value=0)

when trying to use df.loc to form the conditional I get KeyError

When I run cost_merge.columns.levels I get FrozenList([['Cost'], ['1', 'Desktop', 'Mobile', '4', '5']]) but I am unable to utilise it for the logic I want.

I'd like the end result to look like this;

enter image description here

Feel like I've been through every pandas thread on SO to try and get the answer, so apologies if this has been asked before.

As I said before I'm relatively new to pandas so if something isn't formed in the most optimal manner please let me know so I can fix.

Solution

  • Using .loc with a multi-index can be tricky but it should still work for this situation in combination with a for loop:

    for i in cost_merge.index:
    if cost_merge.loc[i, ('Cost', 'Desktop')] == 25:
        cost_merge.loc[i, ('Cost', 'Mobile')] = 0

    This iterates through the index and checks if the value of the desktop cost is equal to 25, if so it then sets the mobile cost to 0.