Search code examples
pythonpandasdataframe

How to reorganize a pandas DataFrame


I have a pandas DataFrame defined like so (although with many more rows):

   segment  time concentration
0        1   1.0         data1
1        2   1.0         data2
2        3   1.0         data3
3        1   2.0         data4
4        2   2.0         data5
5        3   2.0         data6
6        1   3.0         data7
7        2   3.0         data8
8        3   3.0         data9

and essentially I want to reorganize this by using the segment column as the new headers for the DataFrame (and time may or may not be an index, I don't think it matters to me) to get something like the following:

   time      1      2      3
0   1.0  data1  data2  data3
1   2.0  data4  data5  data6
2   3.0  data7  data8  data9

Looking into how to approach this, I thought of how one might be able to define a DataFrame with many row, column, item pairs. Maybe a dictionary? But I'm not sure how to implement this.

I was also looking into the pd.melt() method and I found this question from over 6 years ago, which is essentially the opposite of what I'm asking. Again, not sure how I would implement pd.melt() or if that's even appropriate here.

Thank you!

Here is the data used to define the DataFrames above (for reproducibility purposes):

import pandas as pd

df = pd.DataFrame([[1,1.0,'data1'],  [2,1.0,'data2'], [3,1.0,'data3'],
                   [1,2.0,'data4'],  [2,2.0,'data5'], [3,2.0,'data6'],
                   [1,3.0,'data7'],  [2,3.0,'data8'], [3,3.0,'data9'],
                   ],
                  columns = ['segment','time','concentration'])

df_target = pd.DataFrame([[1.0,'data1','data2','data3'],
                          [2.0,'data4','data5','data6'],
                          [3.0,'data7','data8','data9'],
                          ],
                         columns = ['time',1,2,3])

Solution

  • Use DataFrame.pivot():

    df.pivot(index = 'time', columns = 'segment', values = 'concentration')