Pandas Data Sorting/Transpose

Question

I have a dataframe with datetimes (column 0) as index. example shown below:

DateTime  Category  Value
16:24:00    A       51
16:24:00    B       52
16:24:00    C       53
16:24:00    D       54
17:28:24    A       71
17:28:24    B       72
17:28:24    C       73
17:28:24    D       74

I need to transpose the data so that "Category" Column 1 becomes my column headers and value remains the dependent variable with respect to the datetime. I'm looking for an output as shown below.

DateTime  A  B  C  D  
16:24:00  51 52 53 54 
17:28:24  71 72 73 74

You are looking to pivot your dataframe, there are 100's of questions of SO on that topic. Something like this would work, pd.pivot(df.reset_index(),index='DateTime',columns = 'Category',values = 'Value').reset_index() — Vaishali, Dec 29 '20 at 01:15

Akshay Sehgal · Answer 1 · 2020-12-29T01:03:56.547

1

You can use unstack after setting the columns to index

ddf = df.set_index(['DateTime','Category']).unstack().reset_index()
ddf.columns = ['DateTime','A','B','C','D']
print(ddf)

   DateTime   A   B   C   D
0  16:24:00  51  52  53  54
1  17:28:24  71  72  73  74

edited Dec 29 '20 at 01:03

answered Dec 29 '20 at 00:58

Akshay Sehgal

18,741
3
21
51

score 1 · Answer 2 · answered Dec 29 '20 at 01:04

Or you could try using this ugly code:

new = df.groupby('DateTime').apply(lambda x: x.T).reset_index(level=[0,1]).groupby('DateTime').last().drop('level_1', axis=1)
new.columns = df['Category'].drop_duplicates().sort_values().tolist()
new = new.reset_index()
print(new)

Output:

   DateTime   A   B   C   D
0  16:24:00  51  52  53  54
1  17:28:24  71  72  73  74

Pandas Data Sorting/Transpose

2 Answers2