Reputation: 113
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!
Upvotes: 1
Views: 306
Reputation: 13831
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
Upvotes: 3