CODEWITHSUNDEEP
pythonpandasnumpydataframeshift
denister
denister

Reputation: 35

Iterating over rows in pandas, shifting values to the right by one

I need to iterate over a dataframe to get the values from the first date to move to the next row and start with same first value. Importantly, I need the new inputs to stop at the column range and not overflow past day5 in the example below.

current output:

            day1  day2  day3  day4  day5
date                                    
2018-03-16   1.0   2.0   3.0   4.0   5.0
2018-03-17   NaN   NaN   NaN   NaN   NaN
2018-03-18   NaN   NaN   NaN   NaN   NaN
2018-03-19   NaN   NaN   NaN   NaN   NaN
2018-03-20   NaN   NaN   NaN   NaN   NaN

desired output:

             day1  day2  day3  day4  day5
date                                    
2018-03-16   1.0   2.0   3.0   4.0   5.0
2018-03-17   NaN   1.0   2.0   3.0   4.0
2018-03-18   NaN   NaN   1.0   2.0   3.0
2018-03-19   NaN   NaN   NaN   1.0   2.0
2018-03-20   NaN   NaN   NaN   NaN   1.0

sample code to iterate:

data = [1, 2, 3, 4, 5]
columns_name = ['day1', 'day2', 'day3', 'day4', 'day5']

df = pd.DataFrame(data)
df = df.T
df.columns = columns_name

dates = pd.date_range('2018-03-16', '2018-03-20').tolist()
dates_df = pd.DataFrame(dates)
dates_df.columns = ['date']

dfs = [df, dates_df]
combined = pd.concat(dfs, axis=1)
combined = combined.set_index(['date'])

Upvotes: 2

Views: 586

Answers (1)

piRSquared
piRSquared

Reputation: 294218

numpy.triu_indices

Slice the first row with triangle indices and assign

v = df.values
i, j = np.triu_indices(v.shape[1])
v[i, j] = v[0][j - i]
df

            day1  day2  day3  day4  day5
date                                    
2018-03-16   1.0   2.0   3.0   4.0   5.0
2018-03-17   NaN   1.0   2.0   3.0   4.0
2018-03-18   NaN   NaN   1.0   2.0   3.0
2018-03-19   NaN   NaN   NaN   1.0   2.0
2018-03-20   NaN   NaN   NaN   NaN   1.0

If that happens not to work for you because df.values is a copy and not a view:

v = df.values
i, j = np.triu_indices(v.shape[1])
v[i, j] = v[0][j - i]
df.loc[:] = v
df

            day1  day2  day3  day4  day5
date                                    
2018-03-16   1.0   2.0   3.0   4.0   5.0
2018-03-17   NaN   1.0   2.0   3.0   4.0
2018-03-18   NaN   NaN   1.0   2.0   3.0
2018-03-19   NaN   NaN   NaN   1.0   2.0
2018-03-20   NaN   NaN   NaN   NaN   1.0

numpy.lib.stride_tricks.as_strided

NOT RECOMMENDED
But still fun

from numpy.lib.stride_tricks import as_strided as strided

n = df.shape[1]
v = np.append([np.nan for _ in range(n - 1)], df.values[0])
s = v.strides[0]

df.loc[:] = strided(v[n - 1:], df.shape, (-s, s))

df

            day1  day2  day3  day4  day5
date                                    
2018-03-16   1.0   2.0   3.0   4.0   5.0
2018-03-17   NaN   1.0   2.0   3.0   4.0
2018-03-18   NaN   NaN   1.0   2.0   3.0
2018-03-19   NaN   NaN   NaN   1.0   2.0
2018-03-20   NaN   NaN   NaN   NaN   1.0

Upvotes: 4

Related Questions