Transpose a dataframe based on column entries with sequences

user_10232932 0
Viewed 54

right now I have a dataframe with two columns:

columnA   columnB
:16R:AB     NaN
:20C::XX    S400500
:16X:AB     NaN
:16R:AC     NaN
:16X:AC     NaN
:16R:AB     NaN
:31X::BB    Sema
:16R:AB     Nan

I want to transpose the dataframe based on some sequences. The :16R:AB till the next 16X:AB is a sequence, then from 16R:ACtill 16X:AC and so on. I also want to add a counter/ID then, so that the finale dataframe looks like:

Index/Counter :16R:AB :20C::XX :16X:AB :16R:AC :16X:AC :31X:BB
1                 NaN S400500   NaN     NaN    NaN      Nan
2               NaN    NaN       NaN     NaN   NaN      NaN
3               NaN     NaN       NaN    NaN    NaN      Sema

Is there any trick to do it? Or is it a manuell loop?

pandas python
1 Answers

So rebuilding aprototype of your example: D = pd.DataFrame([np.random.randint(1,5,20), np.random.randn(20)]).T D.columns = ["key", "value"]

key value

4.0 1.017081

4.0 -1.480929

3.0 -1.257809

1.0 -0.683207

...

now we can add a field, that counts the occurance of the same key

D["occurance"] = (D.key == 4.0).cumsum()

... and then we are able to pivot:

D.pivot(index="occurance", columns="key", values=["value"] )
user_16920720
Related