Search code examples
pythonpandasdataframedata-cleaning

How to replace values from even rows into odd rows in python?


I would like to replace the values of all odd rows with the values from even rows. This is my present data frame:

enter image description here

Say for example in the second index: the value for Cheek meat, trimmed is 141.23 and I want all these values to replace the value on top which is the first index row.

I have tried to work with .loc method and replace method and I am unable to do so. I am also new to python. Please help! Thank you

Example data frame

import pandas as pd
df = pd.DataFrame({'Date': ['2011', '2012', '2013','2014'], 
                   'col1': [20, 50, 40,60],'col2': [20, 50, 40,60]}})

Out[1]:
     Date  col1 col2
  1  2011  20    20
  2  2012  50.   50
  3  2013  40.   40
  4  2014  60.   60

I would like the output to be

Out[2]:
     Date  col1 col2
  1  2011  50.   50
  2  2012  00.   00
  3  2013  60.   60
  4  2014  00.   00

Say, we have multiple columns.


Solution

  • Try this:

    df = df.set_index('Date').shift(-1)
    df.loc[df.reset_index().index % 2 == 1] = 0
    df = df.reset_index()
    

    Output:

    >>> df
       Date  col1  col2
    0  2011  50.0  50.0
    1  2012   0.0   0.0
    2  2013  60.0  60.0
    3  2014   0.0   0.0