Collapse Dataframe Pivot to Single Row

Question

I have a list of tuples that I need to be converted into a single dataframe row where the first tuple item is converted into a column and the second into the corresponding value. Here's what I tried:

The data:

[(0, 0.52776772063535005),
 (4, 0.18798097301734626),
 (6, 0.09831844955142259),
 (5, 0.059519666448517437),
 (3, 0.054459995937603152),
 (9, 0.052905323520468818)]

treating this data as test, I tried to convert to dataframe and then pivot but I can't get the data to get flattened out as one records.

test = pd.DataFrame.from_records(scores[0])
test.columns=['t1','t2']
   t1        t2
0   0  0.527768
1   4  0.187981
2   6  0.098318
3   5  0.059520
4   3  0.054460
5   9  0.052905

test2 = test.pivot(index=None, columns='t1',values='t2')

t1         0        3         4        5         6         9
0   0.527768      NaN       NaN      NaN       NaN       NaN
1        NaN      NaN  0.187981      NaN       NaN       NaN
2        NaN      NaN       NaN      NaN  0.098318       NaN
3        NaN      NaN       NaN  0.05952       NaN       NaN
4        NaN  0.05446       NaN      NaN       NaN       NaN
5        NaN      NaN       NaN      NaN       NaN  0.052905

Whereas what I want is it to be in one row:

 t1         0        3         4        5         6         9
    0   0.527768  0.05446  0.187981   0.05952  0.098318 0.052905

Is there a way I can collapse the pivot to just be one row instead of having my data across multiple indexes?

Answer 1

You can change your index to one single value

test.index=[0]*len(test)


test.pivot(index=None, columns='t1',values='t2')
Out[525]: 
t1         0        3         4        5         6         9
0   0.527768  0.05446  0.187981  0.05952  0.098318  0.052905

Or using bfill

test.pivot(index=None, columns='t1',values='t2').bfill().iloc[[0],:]
Out[532]: 
t1         0        3         4        5         6         9
0   0.527768  0.05446  0.187981  0.05952  0.098318  0.052905

Or we create your df from the data

pd.Series(dict(data)).to_frame().T
Out[555]: 
          0        3         4        5         6         9
0  0.527768  0.05446  0.187981  0.05952  0.098318  0.052905

Answer 2

you could alternatively set the index to t1 and when displaying, transpose the data frame, optionally sorting the values by index if you need to. This way it is not necessary to pivot the values.

import pandas as pd
records = [
 (0, 0.52776772063535005),
 (4, 0.18798097301734626),
 (6, 0.09831844955142259),
 (5, 0.059519666448517437),
 (3, 0.054459995937603152),
 (9, 0.052905323520468818)
]
test = pd.DataFrame.from_records(records, columns=['t1', 't2'])
test = test.set_index('t1')
test = test.sort_index().transpose()

# prints out:

t1         0        3         4        5         6         9
t2  0.527768  0.05446  0.187981  0.05952  0.098318  0.052905