What is the best way to use pandas.DataFrame.pivot?

Question

I am trying to translate a dataframe from key strings, values ​​to a table with keys as columns and values ​​as cells. For instance:

Key data input frame, value:

>>>df = pd.DataFrame([['TIME', 'VAL1', 'VAL2', 'VAL3', 
                       'TIME', 'VAL1', 'VAL2', 'VAL3'],
                      ["00:00:01",1,2,3,"00:00:02", 1,2,3]]).T

    0       1
0   TIME    00:00:01
1   VAL1    1
2   VAL2    2
3   VAL3    3
4   TIME    00:00:02
5   VAL1    1
6   VAL2    2
7   VAL3    3

I want it to look like this:

TIME      VAL1 VAL2 VAL3
00:00:01  1    2    3
00:00:02  1    2    3

I can almost get what I want with the axis:

>>>df.pivot(columns=0, values=1)
    TIME        VAL1    VAL2    VAL3
0   00:00:01    None    None    None
1   None        1       None    None
2   None        None    2       None
3   None        None    None    3
4   00:00:02    None    None    None
5   None        1       None    None
6   None        None    2       None
7   None        None    None    3

And I can concatenate the lines to get what I want:

>>> df.pivot(columns=0, values=1).ffill().drop_duplicates(subset='TIME',
                                                          keep='last').set_index('TIME')
TIME      VAL1 VAL2 VAL3
00:00:01  1    2    3
00:00:02  1    2    3

But this seems like a rather inconvenient way to do this, which will throw out a lot of memory for a large data set. Is there a simpler method?

I was tired of looking at pd.DataFrame.from_items()and pd.DataFrame.from_records(), but was not successful.

Answer 1

"ID", , . , 4 , pandas , , . , ​​ DataFrame, .

, :

df['ID'] = np.arange(len(df))//4

, ID , :

>>> df
      0         1  ID
0  TIME  00:00:01   0
1  VAL1         1   0
2  VAL2         2   0
3  VAL3         3   0
4  TIME  00:00:02   1
5  VAL1         1   1
6  VAL2         2   1
7  VAL3         3   1

"" .

>>> df.pivot(index="ID", columns=0, values=1)
0       TIME VAL1 VAL2 VAL3
ID                         
0   00:00:01    1    2    3
1   00:00:02    1    2    3

Answer 2

:

In [65]: df
Out[65]: 
      0         1
0  TIME  00:00:01
1  VAL1         1
2  VAL2         2
3  VAL3         3
4  TIME  00:00:02
5  VAL1         1
6  VAL2         2
7  VAL3         3

In [66]: newdf = pd.concat([df[df[0] == x].reset_index()[1] for x in df[0].unique()], axis=1)

In [67]: newdf.columns = df[0].unique()

In [68]: newdf
Out[68]: 
       TIME VAL1 VAL2 VAL3
0  00:00:01    1    2    3
1  00:00:02    1    2    3

Answer 3

defaultdict, DataFrame:

import collections
keys = ['TIME', 'VAL1', 'VAL2', 'VAL3', 'TIME', 'VAL1', 'VAL2', 'VAL3']
values = ["00:00:01",1,2,3,"00:00:02", 1,2,3]

d = collections.defaultdict(list)
for k, v in zip(keys, values):
    d[k].append(v)

'''
d looks like this:
defaultdict(list,
            {'TIME': ['00:00:01', '00:00:02'],
             'VAL1': [1, 1],
             'VAL2': [2, 2],
             'VAL3': [3, 3]})'''

df = pd.DataFrame(d)
df
Out: 
       TIME  VAL1  VAL2  VAL3
0  00:00:01     1     2     3
1  00:00:02     1     2     3

Nehal J Wani, :

df = pd.DataFrame([['TIME', 'VAL1', 'VAL2', 'VAL3', 
                   'TIME', 'VAL1', 'VAL2', 'VAL3'],
                  ["00:00:01",1,2,3,"00:00:02", 1,2,3]]).T

d = collections.defaultdict(list)
for k, v in zip(df[0], df[1]):
    d[k].append(v)