Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pandas Shift Row Value to Match Column Name

I have a sample dataset that has a set list of column names. In shifting data around, I have each row printing letters in each row as seen below.

I am trying to shift the values of each row to match either respective column. I have tried doing pd.shift() to do so but have not had much success. I am trying to get what is seen below. Any thoughts?

import pandas as pd
df = pd.DataFrame({'A': list('AAAAA'),
                   'B': list('CBBDE'),
                   'C': list('DDCEG'),
                   'D': list('EEDF '),
                   'E': list('FFE  '),
                   'F': list('GGF  '),
                   'G': list('  G  ')})

   A  B  C  D  E  F  G
0  A  C  D  E  F  G   
1  A  B  D  E  F  G   
2  A  B  C  D  E  F  G
3  A  D  E  F         
4  A  E  G              

After:

    A   B   C   D   E   F   G
0   A       C   D   E   F   G
1   A   B       D   E   F   G
2   A   B   C   D   E   F   G
3   A           D   E   F   
4   A               E       G
like image 547
Dante Smith Avatar asked Mar 03 '23 10:03

Dante Smith


2 Answers

Here's a broadcasted comparison approach. This will be quite fast, but does have a higher memory complexity.


a = df.to_numpy()
b = df.columns.to_numpy()

pd.DataFrame(np.equal.outer(a, b).any(1) * b, columns=b)

   A  B  C  D  E  F  G
0  A     C  D  E  F  G
1  A  B     D  E  F  G
2  A  B  C  D  E  F  G
3  A        D  E  F
4  A           E     G
like image 56
user3483203 Avatar answered Mar 11 '23 00:03

user3483203


This is more list pivot problem

s=df.mask(df=='').stack().reset_index()
s.pivot(index='level_0',columns=0,values=0)
Out[34]: 
0        A    B  C    D
level_0                
0        A    B  C    D
1        A  NaN  C  NaN
2        A  NaN  C    D
like image 36
BENY Avatar answered Mar 10 '23 22:03

BENY