Best way converting data in PANDAS DataFrame to matrix in Python

Question

I found one thread of converting a matrix to das pandas DataFrame. However, I would like to do the opposite - I have a pandas DataFrame with time series data of this structure:

row time stamp, batch, value
1, 1, 0.1
2, 1, 0.2
3, 1, 0.3
4, 1, 0.3
5, 2, 0.25
6, 2, 0.32
7, 2, 0.2
8, 2, 0.1
...

What I would like to have is a matrix of values with one row belonging to one batch:

[[0.1, 0.2, 0.3, 0.3],
[0.25, 0.32, 0.2, 0.1],
...]

which I want to plot as heatmap using matplotlib or alike.

Any suggestion?

amdex · Accepted Answer · 2019-07-05 14:19:01Z

1

What you can try is to first group by the desired index:

g = df.groupby("batch")

And then convert this group to an array by aggregating using the list constructor. The result can then be converted to an array using the .values property (or .as_matrix() function, but this is getting deprecated soon.)

mtr = g.aggregate(list).values

One downside of this method is that it will create arrays of lists instead of a nice array, even if the result would lead to a non-jagged array.

Alternatively, if you know that you get exactly 4 values for every unique value of batch you can just use the matrix directly.

df = df.sort_values("batch")
my_indices = [1, 2] # Or whatever indices you desire.
mtr = df.values[:, my_indices] # or df.as_matrix()
mtr = mtr.reshape(-1, 4) # Only works if you have exactly 4 values for each batch

edited Jul 5, 2019 at 14:19

answered Jul 5, 2019 at 14:06

amdex

7813 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

WolfiG Over a year ago

Hi amdex, this seems to work. The only problem I still have is that the "row time" are as well part of the output list together with the actual values. Is there a way to remove these "row time" entries so that only the values are part of the matrix?

amdex Over a year ago

Right, sorry, I did not consider this. You can select your desired columns after your groupby call. e.g. g["value"].aggregate(list).values will select only "value".

richsykes_ · Accepted Answer · 2019-07-05 14:02:10Z

0

Try and use crosstab from pandas, pd.crosstab(). You will have to confirm the aggfunction.

https://pandas.pydata.org/pandas-docs/version/0.23.4/generated/pandas.crosstab.html

and then .as_matrix()

answered Jul 5, 2019 at 14:02

richsykes_

211 silver badge4 bronze badges

Collectives™ on Stack Overflow

Best way converting data in PANDAS DataFrame to matrix in Python

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related