Trying to replicate SUMIFS using Pandas in Python

Question

I have a dataframe that is essentially this:

And I'd like an output that's like this

Where it extends until the last period.

Is anyone sure how to do this using python/pandas?

Thanks!

Answer 1

You could set your index to be "Location" and "Asset" and use stack on all the Period columns which would get you the structure you are looking for.

Then it's a matter of groupby.sum():

df_reshaped = df.set_index(['Asset','Location'])\
    .stack().reset_index()\
        .sort_values(by=['Asset','level_2'])\
            .rename(columns={'level_2':'Period',0:'Cost'})

df_reshaped.groupby(['Asset','Location','Period'],as_index=False).sum()

prints:

   Asset Location Period  Cost
0      A     Scot    P01   163
1      A     Scot    P02   131
2      A     Scot    P03   114
3      A     Scot    P04   176
4      B      Eng    P01   134
5      B      Eng    P02    74
6      B      Eng    P03   112
7      B      Eng    P04    33
8      C      Eng    P01    85
9      C      Eng    P02    90
10     C      Eng    P03    19
11     C      Eng    P04    68
12     C     Scot    P01    51
13     C     Scot    P02    95
14     C     Scot    P03    79
15     C     Scot    P04    62