Transform a 3-column dataframe into a matrix

user_2835670 0
Viewed 2888

I have a dataframe df, for example:

A = [["John", "Sunday", 6], ["John", "Monday", 3], ["John", "Tuesday", 2], ["Mary", "Sunday", 6], ["Mary", "Monday", 4], ["Mary", "Tuesday", 7]] 
df = pandas.DataFrame(A, columns=["names", "dates", "times"])

And I want to reshape it so that, instead of three columns, I can create a matrix where the first column indexes the rows, the second column indexes the columns, and the third column becomes the matrix value, something like:

B = [["John", 6, 3, 2], ["Mary", 6, 4, 7]]
df2 = pandas.DataFrame(B, columns=["names", "Sunday", "Monday", "Tuesday"])

or even better:

B = numpy.asarray(B)
B = pandas.DataFrame(B)

How do I transform A into B?

I have created a double for loop, but in my case df is very large and it takes a very long time. Is there a better way to do it?

This is not just a reshape, since A has 18 values and B has 8

pandas dataframe matrix python
2 Answers

You can use pivot_table(), e.g.:

In []:
df.pivot_table(columns='dates', index='names', values='times').reset_index()

Out[]:
dates names  Monday  Sunday  Tuesday
0      John       3       6        2
1      Mary       4       6        7
user_27504920

Try:

df.set_index(['names','dates'])['times'].unstack().reset_index()

Output:

dates names  Monday  Sunday  Tuesday
0      John       3       6        2
1      Mary       4       6        7

Or:

pd.crosstab(df.names, df.dates, df.times, aggfunc='sum').reset_index()

Or:

df.groupby(['names','dates']).sum()['times'].unstack().reset_index()

Or:

df.pivot('names','dates','times').reset_index()
user_63615310
Related