Build 3d Matrix from Dataframe in python

user_17921325 0
Viewed 46

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.

pandas python
1 Answers

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
user_87152780
Related