Adding missing period in the data using Pandas and fill values with Zeros. How can I then select each segment to manipulation

user_19646180 0
Viewed 54

I have not used pandas to solve complex problems relating to my data(SQL was my preferred choice till now). I was solving this problem where

Input I receive

Column1|Column2|Column3|Column4|Column5|Period|Value1|Value2|Value3
A1|B1|C1|D1|E1|0|10|10|10
A1|B1|C1|D1|E1|1|10|10|10
A1|B1|C1|D1|E1|3|10|10|10
A2|B2|C2|D2|E2|0|10|10|10
A2|B2|C2|D2|E2|1|10|10|10
A2|B2|C2|D2|E2|2|10|10|10
A2|B2|C2|D2|E2|4|10|10|10

The output I want to have is this . So i want to add the missing period for each segment A segment is like (A1&B1&C1&D1&E1).

Output i want to produce

Column1|Column2|Column3|Column4|Column5|Period|Value1|Value2|Value3
A1|B1|C1|D1|E1|0|10|10|10
A1|B1|C1|D1|E1|1|10|10|10
A1|B1|C1|D1|E1|2|0|0|0
A1|B1|C1|D1|E1|3|10|10|10
A2|B2|C2|D2|E2|0|10|10|10
A2|B2|C2|D2|E2|1|10|10|10
A2|B2|C2|D2|E2|2|10|10|10
A2|B2|C2|D2|E2|3|0|0|0
A2|B2|C2|D2|E2|4|10|10|10

After this i wanted to take each segment and wanted to perform some calculation on the Value columns. I have seem some of the answers in this forum but this particular kind of scenario has not been asked here. In SQL I can use PARTITION BY to get to my desired objective. But I am finding it it difficult to do it in pandas (or may be i am too ignorant wrt pandas)

Thanks for your help

pandas dataframe sql python
1 Answers

We can use Dataframe.unstack + DataFrame.stack

new_df = df.set_index(df.columns[df.columns.str.contains('Period|Column', 
                                                         regex=True)].tolist())\
           .unstack(fill_value=0).stack()\
           .reset_index().reindex(df.columns, axis=1)

Or:

new_df = df.set_index(['Column5', 'Period'])\
           .unstack().stack(dropna=False)\
           .reset_index().reindex(df.columns, axis=1)
new_df = new_df.assign(**new_df.loc[:, 'Period':].fillna(0))\
               .assign(**new_df.loc[:, :'Period'].ffill())
print(new_df)

  Column1 Column2 Column3 Column4 Column5  Period  Value1  Value2  Value3
0      A1      B1      C1      D1      E1       0    10.0    10.0    10.0
1      A1      B1      C1      D1      E1       1    10.0    10.0    10.0
2      A1      B1      C1      D1      E1       2     0.0     0.0     0.0
3      A1      B1      C1      D1      E1       3    10.0    10.0    10.0
4      A1      B1      C1      D1      E1       4     0.0     0.0     0.0
5      A2      B2      C2      D2      E2       0    10.0    10.0    10.0
6      A2      B2      C2      D2      E2       1    10.0    10.0    10.0
7      A2      B2      C2      D2      E2       2    10.0    10.0    10.0
8      A2      B2      C2      D2      E2       3     0.0     0.0     0.0
9      A2      B2      C2      D2      E2       4    10.0    10.0    10.0
user_118842370
Related