Move/shift values in Pandas Data Frame

Question

Assuming an example of a data frame df:

I would need to move each value from column A to each column - one value per column:

starting from second value: move to second column B,
third value to third column C,
and so on...

Desired output:

    A     B    C   D   E
0   4.3  NaN  NaN NaN NaN
1   NaN   75  NaN NaN NaN
2   NaN  NaN  8.5 NaN NaN
3   NaN  NaN  NaN 4.0 NaN
4   NaN  NaN  NaN Nan  98

One idea was to copy each value to second column and then erase it in previous column or to shift value from one column to another but I'm not sure how to apply this...

MWE

import pandas as pd
import numpy as np

df=pd.DataFrame(data=np.random.randint(0,100,(5,5)), columns=['A','B','C','D','E'])
df.iloc[:,1:] =np.nan

df.iloc[[1],[1]] = df.iloc[[1],[0]]
df.iloc[[1],[1]] = df.iloc[[1],[0]].shift(1,axis=1)

MaxU - stand with Ukraine · Accepted Answer · 2017-05-27 21:52:42Z

4

In [76]: import string

In [77]: r = pd.DataFrame(np.eye(len(df)), 
                          columns=list(string.ascii_uppercase[:len(df)])) \
               .replace(0, np.nan) * df.A.values

In [78]: r
Out[78]:
     A     B    C    D     E
0  4.3   NaN  NaN  NaN   NaN
1  NaN  75.0  NaN  NaN   NaN
2  NaN   NaN  8.5  NaN   NaN
3  NaN   NaN  NaN  4.0   NaN
4  NaN   NaN  NaN  NaN  98.0

or better:

In [11]: r = pd.DataFrame(index=df.index, columns=list(string.ascii_uppercase[:len(df)]))

In [12]: np.fill_diagonal(r.values, df.A)

In [13]: r
Out[13]:
     A    B    C    D    E
0  4.3  NaN  NaN  NaN  NaN
1  NaN   75  NaN  NaN  NaN
2  NaN  NaN  8.5  NaN  NaN
3  NaN  NaN  NaN    4  NaN
4  NaN  NaN  NaN  NaN   98

UPDATE:

how to "move" single value

we can use Series.shift method.

move horizontally:

In [94]: r.loc[1] = r.loc[1].shift(3)

In [95]: r
Out[95]:
     A   B    C    D     E
0  4.3 NaN  NaN  NaN   NaN
1  NaN NaN  NaN  NaN  75.0
2  NaN NaN  8.5  NaN   NaN
3  NaN NaN  NaN  4.0   NaN
4  NaN NaN  NaN  NaN  98.0

move vertically:

In [96]: r.loc[:, 'D'] = r.loc[:, 'D'].shift(-2)

In [97]: r
Out[97]:
     A   B    C    D     E
0  4.3 NaN  NaN  NaN   NaN
1  NaN NaN  NaN  4.0  75.0
2  NaN NaN  8.5  NaN   NaN
3  NaN NaN  NaN  NaN   NaN
4  NaN NaN  NaN  NaN  98.0

NOTE: shift will shift the whole row/column, but as soon as we have only one value in each row/column this will work.

edited May 27, 2017 at 21:52

answered May 27, 2017 at 20:36

MaxU - stand with Ukraine

212k37 gold badges402 silver badges437 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Michal Over a year ago

Thank you for your answer! Do you know how to "move" values from column A to any column and any row - not diagonally?

MaxU - stand with Ukraine Over a year ago

@Michal, could you make a reproducible example?

Michal Over a year ago

For example to move value 75 to column D or 8.5 to column E. Or even to move 8.5 to the second row, column E.

MaxU - stand with Ukraine Over a year ago

@Michal, do you want to move them always horizontally? Do you want to move single values or to do it in a vectorized way?

Michal Over a year ago

I would like to move single value horizontally and vertically.

spies006 · Accepted Answer · 2017-05-27 21:21:29Z

3

>>> import pandas as pd
>>> import numpy as np
>>> df = pd.DataFrame({'A':[4.3, 75, 8.5, 4.0, 98]})
>>> df
      A
0   4.3
1  75.0
2   8.5
3   4.0
4  98.0


>>> diag_df = pd.DataFrame(np.diag(df.A), index=df.index, columns=['A', 'B', 'C', 'D', 'E'])
>>> diag_df.replace(0, np.nan, inplace=True)
>>> diag_df
     A     B    C    D     E
0  4.3   NaN  NaN  NaN   NaN
1  NaN  75.0  NaN  NaN   NaN
2  NaN   NaN  8.5  NaN   NaN
3  NaN   NaN  NaN  4.0   NaN
4  NaN   NaN  NaN  NaN  98.0

Keep in mind that if you have 0 along the diagonal then it will be replaced with NaN if you use the replace method this way.

edited May 27, 2017 at 21:21

answered May 27, 2017 at 20:35

spies006

2,9472 gold badges22 silver badges29 bronze badges

2 Comments

MaxU - stand with Ukraine Over a year ago

what will happen to 0 values if they will be in the original df ? ;)

spies006 Over a year ago

@MaxU Thanks. My solution to this is too close to your answer to change. I'll make a note.

Collectives™ on Stack Overflow

Move/shift values in Pandas Data Frame

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related