Home Ask Login Register

Developers Planet

Your answer is one click away!

NewtoPython February 2016

Dataframe column transpose in pandas

I have data in source file in the format

Open,0.001
High,0.001
Low,0.001
Close,0.001
Volume,0
Adj Close,0.001
SMA_20,0.0010000000000000005
SMA_50,0.0010000000000000007
date_of_trade,2016-02-05 00:00:00
code,AFT
Open,2.9300000000000002
High,2.9700000000000002
Low,2.8300000000000001
Close,2.8999999999999999
Volume,631100
Adj Close,2.8999999999999999
SMA_20,3.2214999999999998
SMA_50,3.0767999999999969
date_of_trade,2016-02-05 00:00:00
code,1PG
Open,6.9900000000000002
High,7.0999999999999996
Low,6.9000000000000004
Close,6.9000000000000004
Volume,4300

i want to convert into following format

open,high,low,close,volume,adj_close,sma_20,sma_50,data_of_trade,code
.001,.001,.001,0,.001,.0001000005,.0000100007,2016-02-05 00:00:00,aft
2.93,2.97,.......................................................,1pg

the first column in source file is column name and second is corresponding column value .

i tried pivot etc but couldn't make it to work.any help is welcome.

Answers


Kris February 2016

I don't think you really need the Pandas machinery here. You could just do the transposition by hand:

from csv import DictWriter
from toolz import partition

cols = 'Open', 'High', 'Low', 'Close', 'Volume', 'Adj Close', 'SMA_20', 'SMA_50', 'date_of_trade', 'code'

with open('old.csv') as old, open('new.csv', 'w') as new:
    writer = DictWriter(new, cols)
    writer.writeheader()

    for lines in partition(len(cols), old):
        writer.writerow(dict(l.strip().split(',') for l in lines))


Aprillion February 2016

for pivot to properly work, you need add a unique identifier for each record, e.g. if 1 record is 10 rows long in the original data, then integer division of the row number by 10 would do:

df = pd.read_csv(data, header=None, names=["key", "value"])
df["index"] = [i // 10 for i in range(len(df))]
df = df.pivot("index", "key", "value")

FTR: if you have more than say a few billion records (> free GB of your RAM), then use http://pandas.pydata.org/pandas-docs/stable/io.html#io-chunking

Post Status

Asked in February 2016
Viewed 1,698 times
Voted 5
Answered 2 times

Search




Leave an answer


Quote of the day: live life