how to retrieve the last value of a cell dynamically while parsing each rows of a dataframe?

I'm trying to store the last value of a cell while I parse my dataframe, I can't know what this value will be so I need a dynamic method to do so.

The desired value is stored in the position [row, 2] of my dataframe, but at each new row it changes. I'd like a way to write the position [index, 2] while using my parsing method.

So far my code look like that :

value = df2.loc[(df2[2] == df2.loc[2,2]) , 2].reindex(df2.index).ffill()
code = df2.loc[df2[0] == '10', 1].reindex(df2.index).ffill()
df4 = df2.rename(columns={1: 1}) \
        .assign(Value=value, Code=code).loc[lambda x: x[0] == '50']

I tried to replace by :

value = df2.loc[(df2[2] == df2.loc[df2.index,2]) , 2].reindex(df2.index).ffill()

this line of code would work, but there I need to make sure that when it retrieve the 'value' the code of the same line has to be '10'

To go a bit further here is a sample of my dataframe :


>>> df2
    Code  Power/Character   value/color
0     10  Power-220         ARS
1     50  Artemis           pink
2     50  Ares              red
3     90  end               
4     10  Power-550         AZK
5     50  Artemis           blue   
6     90  end
7     10  Power-990         DLS
8     50  Zeus              grey
9     50  Kratos            white
10    90  end

the intended result is having a dataframe with these data :

    Code  Character        Color       Power       Value
1     50  Artemis           pink       Power-220   ARS
2     50  Ares              red        Power-220   ARS
5     50  Artemis           blue       Power-550   AZK
8     50  Zeus              grey       Power-990   DLS
9     50  Kratos            white      Power-990   DLS

Any ideas ?

Ps: In my code sample, I use 0, 1, 2 for the names of my cols, because my dataframe's columns has no names yet.

Solution

Split your rows in two dataframes: df3 for Code=50 and df4 for Code=10 then merge them:

df3 = df2.loc[df2['Code'].eq(50)].rename(columns={'Power/Character': 'Character',
                                                  'value/color': 'Color'})

df4 = df2.loc[df2['Code'].eq(10)].rename(columns={'Power/Character': 'Power',
                                                  'value/color': 'Value'})

df3 = df3.merge(df4.drop(columns='Code').reindex(df2.index).ffill(), 
                left_index=True, right_index=True, how='left')

Output:

>>> df3
   Code Character  Color      Power Value
1    50   Artemis   pink  Power-220   ARS
2    50      Ares    red  Power-220   ARS
5    50   Artemis   blue  Power-550   AZK
8    50      Zeus   grey  Power-990   DLS
9    50    Kratos  white  Power-990   DLS