Pandas data frame rotation, row duplication

Question

I have a little problem with rotation in pandas. dataframe (dates, location, data) I'm working on is as follows:

 dates location data date1 AX date2 AY date3 AZ date1 B XX date2 B YY 

Basically, I'm trying to rotate in place to end up with data:

 dates ABC date1 X XX etc... date2 Y YY date3 Z ZZ 

Unfortunately, when I rotate, the index equivalent to the source date column does not change, and I get:

 dates ABC date1 X NA etc... date2 Y NA date3 Z NA date1 NA XX date2 NA YY 

Does anyone know how I can fix this problem to get the dataframe formation I'm looking for?

I now call Pivot the following:

 df.pivot(index="dates", columns="location") 

because I have # columns of data that I want to collapse (I don't want to list each as an argument). In my opinion, by default, pivot rotates the rest of the columns in the dataframe. Thank.

Answer 1

If you have multiple columns of data, calling a column without columns of values ​​should give you a maximized frame with MultiIndex as columns:

 In [3]: df Out[3]: columns data1 data2 index 0 a -0.602398 -0.982524 x 1 a 0.880927 0.818551 y 2 b -0.238849 0.766986 z 3 b -1.304346 0.955031 x 4 c -0.094820 0.746046 y 5 c -0.835785 1.123243 z In [4]: df.pivot('index', 'columns') Out[4]: data1 data2 columns abcabc index x -0.602398 -1.304346 NaN -0.982524 0.955031 NaN y 0.880927 NaN -0.094820 0.818551 NaN 0.746046 z NaN -0.238849 -0.835785 NaN 0.766986 1.123243 

Answer 2

What do you call DataFrame.pivot and what data type is your date column?

Suppose I have a DataFrame similar to yours, the date columns contain datetime objects:

 In [52]: df Out[52]: data dates loc 0 0.870900 2000-01-01 00:00:00 A 1 0.344999 2000-01-02 00:00:00 A 2 0.001729 2000-01-03 00:00:00 A 3 1.565684 2000-01-01 00:00:00 B 4 -0.851542 2000-01-02 00:00:00 B In [53]: df.pivot('dates', 'loc', 'data') Out[53]: loc AB dates 2000-01-01 0.870900 1.565684 2000-01-02 0.344999 -0.851542 2000-01-03 0.001729 NaN 

Answer 3

Just answered my question. I used the old Sybase module to import data, and I think it used the old DateTimeType object from mxDatetime. In this module, the datetime from January 1, 2011 will not necessarily be equal to another time date January 1, 2011 (for example, each datetime was unique). Therefore, the data axis of the data processes each column value as unique in the index.

Thanks for the help.