Build 3d Matrix from Dataframe in python

Question

I'm new to python and have a rather basic question.

I want to rearrange this dataframe:

df = pd.DataFrame({
  "sm1_wf1": [6, 6.1, 5.9, 6],
  "sm1_wf2": [7, 7.1, 6.9, 7],
  "sm2_wf1": [13, 12.9, 13.1, 13]
  "sm1_wf2":[4, 3.9, 4.1, 4]})

so that each row becomes a 2x2 matrix (row name will be sm1 and sm2, while column name will be wf1 and wf2), and a total of four 2x2 matrices (one for each row in the original df). I know this could be done manually, but I want some more efficient way since the actual data has many observations.

Could someone please show me how to do that? Thank you so much.

C. Braun · Accepted Answer · 2022-01-13 06:44:31Z

1

It's unclear from your question what format you want these 2x2 matrices in, but here is maybe one way to do it:

df = pd.DataFrame({
  "sm1_wf1": [6, 6.1, 5.9, 6],
  "sm1_wf2": [7, 7.1, 6.9, 7],
  "sm2_wf1": [13, 12.9, 13.1, 13],
  "sm2_wf2": [4, 3.9, 4.1, 4]  # <-- note I changed this column name
})

# Keep original index to keep 2x2 matrices distinct
df = df.reset_index().melt("index")

# Split smX_wfY into two columns sm and wf, then join back onto data
labels = df.pop("variable").str.split("_", expand=True)
labels.columns = ["sm", "wf"]
df = labels.join(df)

# Restructure to 2x2 matrices
df.pivot_table("value", ["index", "sm"], "wf")

This gives:

wf          wf1  wf2
index sm            
0     sm1   6.0  7.0
      sm2  13.0  4.0
1     sm1   6.1  7.1
      sm2  12.9  3.9
2     sm1   5.9  6.9
      sm2  13.1  4.1
3     sm1   6.0  7.0
      sm2  13.0  4.0

answered Jan 13, 2022 at 6:44

C. Braun

5,2611 gold badge22 silver badges51 bronze badges

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

2 Comments

Allen997 Over a year ago

Thank you for your help and sorry for the late reply. I tried your solution and I noticed that the result matrix has a dimension of 2. I know maybe I wasn't clear about my question, but how can I make this a 2x2x4 matrix instead of 2x8? Thanks again.

C. Braun Over a year ago

Using pandas, data is expressed 2-dimensionally. If you are working with higher-dimensioned matrices, I'd recommend working more directly with numpy, which pandas is built on. To access the numpy array under the dataframe use .values. For example, to convert the data in the last step to a matrix with the dimensions you specified, try df.values.reshape((2, 2, 4)). I'd also recommend looking into the package xarray if you want numpy functionality with pandas indexing.

Collectives™ on Stack Overflow

Build 3d Matrix from Dataframe in python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related