CODEWITHSUNDEEP
python-3.xpandaspivot-table
CollegePythonNoob
CollegePythonNoob

Reputation: 37

Pivot Pandas Dataframe On Multiole Columns WIthout Agg

I have a pandas data frame:

    test_1      test_2  test_3  test_4    recording_id  group   subgroup
0   0.873514    0.0     0.0     0.890000    732         29      13
1   0.845559    0.0     0.0     0.875713    763         29      13
2   0.826116    0.0     0.0     0.867159    791         29      13
3   0.886980    0.0     0.0     0.923520    822         29      13
4   0.808065    0.0     0.0     0.863643    852         29      13

This is just a small sample from the dataframe.

I want to pivot the data so that my new dataframe will look like this:

test_type    value        recording_id    group    subgroup
test_1      0.873514     732             29       13
test_1      0.845559     763             29       13
test_1      0.826116     791             29       13
test_1      0.886980     822             29       13
test_2      0.0          732
test_2      0.0          763
test_2      0.0          791
ect.

The goal is to create two new columns identifying the test type and the value from that test while maintaining the recording_id, group, and subgroup for each test type and value. This would make the table long instead of wide.

I have tried:

test_data_pivot = test_data.pivot_table(index= ['test_1', 'test_2', 'test_3', 'test_4'], columns= ['recording_id', 'group', 'subgroup'], values = ['test_1', 'test_2', 'test_3', 'test_4'])

And I get

DataError: No numeric types to aggregate

I've also tried pivoting on the non test columns:

test_data_pivot = test_data.pivot_table(index= ['recording_id', 'group', 'subgroup'], columns= ['test_1', 'test_2', 'test_3', 'test_4'], values = ['test_1', 'test_2', 'test_3', 'test_4'])

And I get the same error.

I've also tried to pivot on one test column at a time and appending the resulting rows to a new table, but that failed too. After reading the pandas docs and looking at a fair number of pandas pivot questions already asked, I'm stumped.

Thanks in advance for any direction provided.

Sincerely,

A lost Bio major trying to learn Python on his own

Upvotes: 2

Views: 69

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150785

You should be looking at melt, not pivot:

df.melt(['recording_id', 'group','subgroup'], var_name='test_type')

Output:

    recording_id  group  subgroup test_type     value
0            732     29        13    test_1  0.873514
1            763     29        13    test_1  0.845559
2            791     29        13    test_1  0.826116
3            822     29        13    test_1  0.886980
4            852     29        13    test_1  0.808065
5            732     29        13    test_2  0.000000
6            763     29        13    test_2  0.000000
7            791     29        13    test_2  0.000000
8            822     29        13    test_2  0.000000
9            852     29        13    test_2  0.000000
10           732     29        13    test_3  0.000000
11           763     29        13    test_3  0.000000
12           791     29        13    test_3  0.000000
13           822     29        13    test_3  0.000000
14           852     29        13    test_3  0.000000
15           732     29        13    test_4  0.890000
16           763     29        13    test_4  0.875713
17           791     29        13    test_4  0.867159
18           822     29        13    test_4  0.923520
19           852     29        13    test_4  0.863643

Upvotes: 2

Related Questions