Search code examples
pythonpandasjoinmergeconcatenation

python pandas, transform data set, move rows into columns

There is a csv data frame which contains attributes and their values in an hourly interval. Not all attributes are listed each hour. It looks like this:

time                    attribute value
2019.10.11. 10:00:00    A           10
2019.10.11. 10:00:00    B           20
2019.10.11. 10:00:00    C           10
2019.10.11. 10:00:00    D           13
2019.10.11. 10:00:00    E           12
2019.10.11. 11:00:00    A           11
2019.10.11. 11:00:00    D           8
2019.10.11. 11:00:00    E           17
2019.10.11. 12:00:00    A           13
2019.10.11. 12:00:00    B           24
2019.10.11. 12:00:00    C           11
2019.10.11. 12:00:00    E           17

I would like to convert it to have one row for each hour and the attribute name should go as column with its value. If an attribute is not listed, then it should have a zero value or can also be left blank etc... Does pandas offer a way with merge, concat or join or anything else to automate this or do I have to implement it manually?

I would need the dataset in the following format:

time                    A   B   C   D   E
2019.10.11. 10:00:00    10  20  10  13  12
2019.10.11. 11:00:00    11  0   0   8   17
2019.10.11. 12:00:00    13  24  11  0   17

Thank you for reading it!

Solution

  • Use DataFrame.pivot_table:

    df=df.pivot_table(columns='attribute',index='time' ,values ='value',fill_value=0)
print(df)

    attribute              A   B   C   D   E
time                                   
2019.10.11. 10:00:00  10  20  10  13  12
2019.10.11. 11:00:00  11   0   0   8  17
2019.10.11. 12:00:00  13  24  11   0  17