Pandas replacing the one column value with other

Question

Here is the sample data:

Time,RangeGreen,RangeRed,DistanceGreenRed,SlopeGreen,SlopeRed,PreviousCandleColor,CurrentCandleColor
2018.04.02 00:01:01,30-40,20-30,14.42241040084485,0.002837507264410963,-0.002233901393132696,Green,indecisive
2018.04.02 00:03:06,40-50,30-40,9.228956001044772,3.969502900848433,7.203315124348411,Green,indecisive
2018.04.02 00:04:06,10-20,30-40,-13.69498672180752,-19.36590965829607,-2.850639197642629,Red,indecisive

I am willing to replace the CurrentCandleColor values with the PreviousCandleColor but the 0th value of CurrentCandleColor should be the 1st value of PreviousCandleColor.

Hence my final value must be:

Time,RangeGreen,RangeRed,DistanceGreenRed,SlopeGreen,SlopeRed,PreviousCandleColor,CurrentCandleColor
2018.04.02 00:01:01,30-40,20-30,14.42241040084485,0.002837507264410963,-0.002233901393132696,Green,Green
2018.04.02 00:03:06,40-50,30-40,9.228956001044772,3.969502900848433,7.203315124348411,Green,Red
2018.04.02 00:04:06,10-20,30-40,-13.69498672180752,-19.36590965829607,-2.850639197642629,Red,indecisive

I tried to move it somewhat like this;

df['CurrentCandleColor'] = df[1:'PreviousCandleColor']

But I got the following error:

TypeError: cannot do slice indexing on <class 'pandas.core.indexes.range.RangeIndex'> with these indexers [PreviousCandleColor] of <class 'str'>

Kindly, help me.

`df['CurrentCandleColor'] = df['PreviousCandleColor'].shift()` — Bharath M Shetty, May 25 '18 at 12:08
But it didn't work for me. Had you tried. Please let me know. — Jaffer Wilson, May 25 '18 at 12:11
Check the output I received after running the command and what I expected: https://gist.github.com/JafferWilson/53f46959bf58d4fe41b7f4237ee6fe72 — Jaffer Wilson, May 25 '18 at 12:18
Please check the outputs that I was expecting please let me know if anything ambiguous — Jaffer Wilson, May 25 '18 at 12:20
Hence, my question is not what Dark had marked me as duplicate. — Jaffer Wilson, May 25 '18 at 12:24

jezrael · Accepted Answer · 2018-05-25T12:38:23.400

1

Use shift with fillna if no NaNs here:

a = df.loc[df.index[-1], 'CurrentCandleColor']
df['CurrentCandleColor'] = df['PreviousCandleColor'].shift(-1).fillna(a)
print (df)
                  Time        ...         CurrentCandleColor
0  2018.04.02 00:01:01        ...                      Green
1  2018.04.02 00:03:06        ...                        Red
2  2018.04.02 00:04:06        ...                 indecisive

[3 rows x 8 columns]

Or:

df['CurrentCandleColor'] =df['PreviousCandleColor'].shift(-1).fillna(df['CurrentCandleColor'])

If possible NaNs values first get last value of column and then set back:

last = df['CurrentCandleColor'].values[-1]
df['CurrentCandleColor'] = df['PreviousCandleColor'].shift(-1)
df.loc[df.index[-1], 'CurrentCandleColor'] = last

edited May 25 '18 at 12:38

answered May 25 '18 at 12:08

jezrael

822,522
95
1,334
1,252

perhaps commenting would be better. There are so many answers for shift na ? – Bharath M Shetty May 25 '18 at 12:09
1

Great that help . Thank you very much. – Jaffer Wilson May 25 '18 at 12:26

Pandas replacing the one column value with other

1 Answers1