Append contents of previous row to the next one

user_2825403 0
Viewed 63

A bit stuck here. Seems easy but for some reason can't seem to get it to work.

I have a csv file that I need to read from and then add contents of the previous row to the next one. So for example if original data looks like this:

   0
0  a
1  b
2  c
3  d

Then I need to get it to be like this:

   a  b  c
0  a  0  0
1  b  a  0
2  c  b  a
3  d  c  b

I tried with Pandas first, but then quickly got lost in trying to find a simple and quick way of iterating over rows/columns.

After all this didn't quite work I decided to simply read the csv line-by-line and then recursively add data to the previous row's contents, but been unsuccessful so far in it constantly running into recursion limit issues and such.

What would be the best way to approach the problem?

pandas dataframe recursion python
4 Answers

Just a for loop would do:

for i in range(1,3):
    # may need to replace '0' with 0 or the actual column name
    # also i with f'{i}' if you want column name as string
    df[i] = df['0'].shift(i, fill_value=0)

    # another column to shift:
    df[f'other_col_{i}'] = df['other_col'].shift(i, fill_value=0)

If you have even more than two columns, maybe something similar to ALollz's excellent deleted answer:

cols = ['col1', 'col2', 'col3']
new_df = pd.concat([df[cols].shift(i, fill_value=0).add_suffix(f'_{i}')
                      for i in range(3)
                   ])

Output:

   0  1  2
0  a  0  0
1  b  a  0
2  c  b  a
3  d  c  b
user_42384080

IIUC we can do the cumsum then do the split and sorted

s=df.iloc[:,0].add(',').cumsum().str[:-1].str.split(',',expand=True).T.apply(lambda x : sorted(x,key=pd.notnull))
      0     1     2  3
0  None  None  None  a
1  None  None     a  b
2  None     a     b  c
3     a     b     c  d

#s=s.iloc[:,::-1]
user_79645270

Something like this maybe? I am not sure what the logic is in your example for adding the next row but here is my solution based on some assumptions I made

import pandas as pd
df = pd.read_csv("yourcsv.csv")

for item in df['firstcolname']:
   temp_list = [0] * len(df.columns)
   while len(temp_list) != len(df['firstcolname']):
      for element in df['firstcolname']:
         temp_list.append(element)

   df[item] = temp_list
user_95611540

I bit changed YOBEN_S Answer for your requirement.

import pandas as pd

df= pd.DataFrame(['A', 'B', 'C', 'D'])
s=df.iloc[:,0].add(',').cumsum().str[:-1].str.split(',',expand=True).T.apply(lambda x : sorted(x,key=pd.notnull))

s=s.iloc[:,::-1]

new_header = s.iloc[:,0] #grab the first col
s.columns = new_header #set the header row as the df header
s.fillna(value=0, inplace=True)

print(s)

Output

3  A  B  C  D
0  A  0  0  0
1  B  A  0  0
2  C  B  A  0
3  D  C  B  A
user_78770990
Related