How to reshape measurement data in pandas-CodePudding

My data looks like this:

I want it to look like this:

| x        | y        |    t     | 
|:---------|:---------|:---------|
|2.57      |22.970866 | 1.012199 |
|2.59      |22.931890 | 0.998285 |
|2.61      |22.892913 | 0.998285 |

Essentially I want the top row to be 'x' (I shortened the values for ease)

The first column to be 'y'

Then I need some way to map where those points intercept as 't'

I've been using pandas to try to achieve this but I'm starting to think it's not the right tool? I can't seem to find anyone else asking pandas questions with data that looks like mine.

Then I was thinking that I would separate all of these into lists then merging them back together in a new dataframe but I still wouldn't know how to match the values correctly in the 't' column.

CodePudding user response：

IIUC, try selecting the data you need and then rename at the end:

>>> pd.concat([df.iloc[0,1:],df.iloc[1:,:2]], axis=1).set_axis(list("xyt"), axis=1).reset_index(drop=True)

          x          y         t
0  2.574803  22.970866  1.012199
1  2.595669  22.931890  0.998285
2  2.616535  22.892913  0.998285

Alternatively, rename and then select:

>>> df.rename(columns={0:"y",1:"t"}).assign(x=df.iloc[0]).loc[1:,list("xyt")].reset_index(drop=True)

          x          y         t
0  2.574803  22.970866  1.012199
1  2.595669  22.931890  0.998285
2  2.616535  22.892913  0.998285

Input df:

df = pd.DataFrame({0: [0, 22.970866, 22.931890, 22.892913],
                   1: [2.57480315, 1.012199, 0.998285, 0.998285],
                   2: [2.595669291, 1.012199, 0.998285, 0.998285],
                   3: [2.616535433, 1.012199, 0.998285, 0.998285]
                   })
~~~

CodePudding user response：

Extract some part of your dataframe to create a new one with pd.concat:

out = pd.concat([df.iloc[0, 1:], df.iloc[1:, 0], df.iloc[1:, 1]],
                keys=list('xyt'), axis=1)
print(out)

# Output:
          x          y         t
1  2.574803  22.970866  1.012199
2  2.595669  22.931890  0.998285
3  2.616535  22.892913  0.998285

Setup:

df = pd.DataFrame({0: [0.0, 22.970866, 22.93189, 22.892913],
                   1: [2.574803, 1.012199, 0.998285, 0.998285],
                   2: [2.595669, 1.012199, 0.998285, 0.998285],
                   3: [2.616535, 1.012199, 0.998285, 0.998285]}