Convert grouped columns into rows in python-CodePudding

I want to convert below grouped frame on date to rows.

<code>
    2011-01-07 00:00:00
    stock       date   close
    0      AA 2011-01-07   16.42
    12    AXP 2011-01-07   44.36
    24     BA 2011-01-07   69.38
    36    BAC 2011-01-07   14.25
    48    CAT 2011-01-07   93.73
    60   CSCO 2011-01-07   20.97
    2011-01-14 00:00:00
    stock       date   close
    1      AA 2011-01-14   15.97
    13    AXP 2011-01-14   46.25
    25     BA 2011-01-14   70.07
    37    BAC 2011-01-14   15.25
    49    CAT 2011-01-14   94.01
    61   CSCO 2011-01-14   21.21
</code>

as per below

<code>
    2011-01-07 16.42 44.36 69.38 14.25 93.73 20.97
    2011-01-14 15.97 46.25 70.07 15.25 94.01 21.21
</code>

I used below code to group the dataframe named dow.

<code>
    grouped = dow.groupby('date')
</code>

CodePudding user response：

A possible solution, based on pandas.DataFrame.pivot:

df.pivot(index='date', columns='stock', values='close')

Output:

stock          AA    AXP     BA    BAC    CAT   CSCO
date                                                
2011-01-07  16.42  44.36  69.38  14.25  93.73  20.97
2011-01-14  15.97  46.25  70.07  15.25  94.01  21.21

Data:

   stock        date  close
0     AA  2011-01-07  16.42
12   AXP  2011-01-07  44.36
24    BA  2011-01-07  69.38
36   BAC  2011-01-07  14.25
48   CAT  2011-01-07  93.73
60  CSCO  2011-01-07  20.97
1     AA  2011-01-14  15.97
13   AXP  2011-01-14  46.25
25    BA  2011-01-14  70.07
37   BAC  2011-01-14  15.25
49   CAT  2011-01-14  94.01
61  CSCO  2011-01-14  21.21

CodePudding user response：

This does not work precisely as you want, but I want to give you a starting point so you can analyze and learn and finish yourself.

#! /usr/bin/python3

with open("sql.txt") as fp:
    while True:
        line = fp.readline()
        if not line:
            break
        list = line.split()

        length = len(list)
        if (length <= 2):
            print(list[0])
        else:
            if (4 == length):
                print(list[3])