How to transpose 4 rows in a data frame including two columns and show them as column using pandas-CodePudding

date    time    value
0   01-01-2000  00:00   1
1   01-01-2000  00:00   2
2   01-01-2000  00:00   3
3   01-01-2000  00:00   4
4   01-01-2000  01:00   5
5   01-01-2000  01:00   6
6   01-01-2000  01:00   7
7   01-01-2000  01:00   8

i have above dataframe i wants to result out to below format

date    time    value   col1  col2  col3  col4
0   01-01-2000  00:00   1      2     3     4
1   01-01-2000  01:00   5      6     7     8

data list

date = ['01-01-2000','01-01-2000','01-01-2000','01-01-2000','01-01-2000','01-01-2000','01-01-2000','01-01-2000']
time = ['00:00','00:00','00:00','00:00','01:00','01:00','01:00','01:00']
value = [1,2,3,4,5,6,7,8]

CodePudding user response：

Use GroupBy.cumcount with DataFrame.pivot:

df['g'] = df.groupby(['time','time']).cumcount() 

f = lambda x: f'col{x   1}'
df = df.pivot(['date','time'], 'g', 'value').rename(columns=f).reset_index()
print (df)
g        date   time  col1  col2  col3  col4
0  01-01-2000  00:00     1     2     3     4
1  01-01-2000  01:00     5     6     7     8

Alternative is aggregate lists and then create DataFrame by costructor:

s = df.groupby(['date','time'])['value'].agg(list)

f = lambda x: f'col{x   1}'
df = pd.DataFrame(s.tolist(), index=s.index).rename(columns=f).reset_index()

print (df)
         date   time  col1  col2  col3  col4
0  01-01-2000  00:00     1     2     3     4
1  01-01-2000  01:00     5     6     7     8