2

I am manipulating a data frame using Pandas in Python to match a specific format.

I currently have a data frame with a row for each measurement location (A or B). Each row has a nominal target and multiple measured data points.

This is the format I currently have:

df=
    Location    Nominal Meas1   Meas2   Meas3
    A           4.0     3.8     4.1     4.3
    B           9.0     8.7     8.9     9.1

I need to manipulate this data so there is only one measured data point per row, and copy the Location and Nominal values from the source rows to the new rows. The measured data also needs to be put in the first column.

This is the format I need:

df =
     Meas       Location    Nominal
     3.8        A           4.0
     4.1        A           4.0
     4.3        A           4.0
     8.7        B           9.0
     8.9        B           9.0
     9.1        B           9.0

I have tried concat and append functions with and without transpose() with no success.

This is the most similar example I was able to find, but it did not get me there:

for index, row in df.iterrows():
    pd.concat([row]*3, ignore_index=True)

Thank you!

Andrew Winter
  • 89
  • 5

2 Answers2

2

Its' a wide to long problem 

pd.wide_to_long(df,'Meas',i=['Location','Nominal'],j='drop').reset_index().drop('drop',1)
Out[637]: 
  Location  Nominal  Meas
0        A      4.0   3.8
1        A      4.0   4.1
2        A      4.0   4.3
3        B      9.0   8.7
4        B      9.0   8.9
5        B      9.0   9.1
BENY
  • 317,841
  • 20
  • 164
  • 234
0

Another solution, using melt:

new_df = (df.melt(['Location','Nominal'],
                  ['Meas1', 'Meas2', 'Meas3'],
                  value_name = 'Meas')
          .drop('variable', axis=1)
          .sort_values('Location'))

>>> new_df
  Location  Nominal  Meas
0        A      4.0   3.8
2        A      4.0   4.1
4        A      4.0   4.3
1        B      9.0   8.7
3        B      9.0   8.9
5        B      9.0   9.1
sacuL
  • 49,704
  • 8
  • 81
  • 106