1

I have a DataFrame that looks like (it's a set of combinations):

   A  B  C  
a  1  1  3  
b  1  2  4  
c  2  1  5  
d  2  2  6

Which I would like to transform into a matrix where the new columns and indexes are unique values of two of the columns (A and B) and the cells are the join between these two unique values from a third column (C).

With A as the index, B as the columns and C as the cell values I would have something like:

   B
A  1 2
1  3 4  
2  5 6

To generate this new 'matrix' DataFrame I iteratively filter the original DF by the unique values in columns A, then get the C column as a Series, like:

for ind in unique_indexes: # made by using .drop_duplicates on the column
    rows = original_table[(original_table['A'] == ind)] 
    new_series = rows['C']

I'm then trying to glue all of these Series together as rows in a new DataFrame, but can't get any of them to either append or concat into the new DataFrame (following both the docs or similar questions on SO), e.g. 

# with suitable placement in 'for' loop
df = DataFrame()
df.append(new_series)

>>> print df
Empty DataFrame

Is there a) a better way of doing this transformation, or b) a step that I'm missing in appending series to a DataFrame?

Cheers

Community
  • 1
  • 1
mrmagooey
  • 4,832
  • 7
  • 37
  • 49

1 Answers1

0

Are you looking to make a pivot_table, like this?

>>> df
   A  B  C
a  1  1  3
b  1  2  4
c  2  1  5
d  2  2  6
>>> pd.pivot_table(df, rows="A", cols="B", values="C")
B  1  2
A      
1  3  4
2  5  6
DSM
  • 342,061
  • 65
  • 592
  • 494
  • Cheers, the [documentation](http://pandas.pydata.org/pandas-docs/stable/reshaping.html) is very clear on my use case as well – mrmagooey Apr 15 '13 at 00:42