Trying to replicate SUMIFS using Pandas in Python-CodePudding

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!

CodePudding user response：

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