Transform a large dataframe - takes too long

Question

I have a dataframe loaded from a CSV in the following format:

           stock_code    price 
20180827     001          10
20180827     002          11
20180827     003          12
20180827     004          13
20180826     001          14
20180826     002          15
20180826     003          11
20180826     004          10
20180826     005          19

I want to transform it to the following format:

            001     002     003     004     005
20180827    10      11      12      13      nan
20180826    14      15      11      10      19

This is my function ( oracle_data is the original data frame) that does the transformation, but it takes 7 minutes for 547500 row dataframe. Is there a way to speed it up?

def transform_data(oracle_data):
    data_code = oracle_data[0]  
    data_date = oracle_data[1] 
    factor_date = sorted(data_date.unique()) 
    stock_list =  sorted(data_code.unique())     
    factor_data = pd.DataFrame(index = factor_date, columns = stock_list)
    sort = oracle_data.sort_index()
    for n in oracle_data.index:
        factor_data.at[oracle_data.at[n,1],oracle_data.at[n,0]]=oracle_data.at[n,2]
    return factor_data

[Please don't post images of code (or links to them)](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question) — jezrael, Aug 27 '18 at 06:10
@Amoxz here you go - https://gist.github.com/tuxdna/d6b16610e65e6c4ab05c70057518dbe8 — tuxdna, Aug 27 '18 at 06:31
Hi, please go through the link. Seems like a duplicate question. you can find multiple answers for your problem. https://stackoverflow.com/questions/22127569/opposite-of-melt-in-python-pandas — Akshay Sapra, Aug 27 '18 at 07:21

score 1 · Accepted Answer · answered Aug 27 '18 at 06:06

1

I believe here is possible use pandas.pivot:

df = pd.pivot(df.index, df['stock_code'], df['price'])

answered Aug 27 '18 at 06:06

jezrael

822,522
95
1,334
1,252

it works perfect ,thank you very much – Amoxz Aug 27 '18 at 06:53

Transform a large dataframe - takes too long

1 Answers1