CODEWITHSUNDEEP
pythonpandasdataframeconcatenation
Devarshi Goswami
Devarshi Goswami

Reputation: 1225

Pandas-iterate through a dataframe concatenating row values and column values into a new dataframe with respect to a specific column value

I have a data-frame of 32250 rows x 901 columns :data I want to iterate throughout the row values of column 'TRAINSET' and concatenate respective row value of columns '1','2','3'...n and keep 'date' as the same for the concatenated field:

for example

 d= {     'TS': ['a', 'b', 'c'],
       'date':  [ 7,   6,   8 ],
         'X':   ['x', 'x', 'x'],
         'Y':   ['y', 'y', 'y']
     }

, i.e after operation, the resultant dataframe will look like this 

d= {   'TS+1':  ['ax','ay','bx','by','cx','cy'],
       'date':  [ 7,   7,   6,   6,    8,   8 ],
         'X':   ['x', 'x',  'x', 'x', 'x', 'x'],
         'Y':   ['y', 'y',  'y', 'y', 'y', 'y']
    }

column x,y....n contains 32250 entries of the same value please check the image for actual data description

The first few values of the resultant table will be like

d= { 'TRAINSET':['TNST175TC101','TNST175TC102','TNST175TC103','TNST175TC104','TNST175TC105'],
   'date':[ '2018-1-5','2018-1-5','2018-1-5','2018-1-5','2018-1-5'],
     '1':   ['TC101', 'TC101',  'TC101', 'TC101', 'TC101'],
     '2':   ['TC102', 'TC102',  'TC102', 'TC102', 'TC102']
        }

Thanks in advance :)

Upvotes: 0

Views: 1074

Answers (1)

jezrael
jezrael

Reputation: 863341

Use concat with add new values to TS columns by DataFrame.assign, then DataFrame.sort_index and create default RangeIndex by reset_index:

df = pd.concat([df.assign(TS = df['TS'] + 'x'), 
                df.assign(TS = df['TS'] + 'y')]).sort_index().reset_index(drop=True)
print (df)
   TS  date  X
0  ax     7  x
1  ay     7  x
2  bx     6  y
3  by     6  y
4  cx     8  z
5  cy     8  z

Upvotes: 1

Related Questions