[FIXED] How to transform/pivot time series data

Issue

I have a table with time series data:

 A    B     C
_____________
ID1 1978    1
ID1 1979    2
ID1 1980    5
ID1 1947    6
ID2 1950    8
ID2 1952    2
ID2 1955    3
ID2 1958    5
ID2 1963    4
ID2 1969    3
ID3 1970    9
ID3 1976    8
ID3 2002    7
ID3 1972    4
ID3 1973    6

Which I would like to transform in such a way:

        1947    1950    1952    1955    1958    1963    1969    1970    1972    1973    1976    1978    1979    1980    2002
ID1     6                                                                                       1       2       5
ID2             8       2       3       5       4       3
ID3                                                             9       4       6         8                             7

How can I transpose the data and fill missing year values with empty cells?

Solution

You can use pandas pivot()

df.pivot(index='A', columns='B', values='C')

Output:

B    1947  1950  1952  1955  1958  1963  ...  1973  1976  1978  1979  1980  2002
A                                        ...                                    
ID1   6.0   NaN   NaN   NaN   NaN   NaN  ...   NaN   NaN   1.0   2.0   5.0   NaN
ID2   NaN   8.0   2.0   3.0   5.0   4.0  ...   NaN   NaN   NaN   NaN   NaN   NaN
ID3   NaN   NaN   NaN   NaN   NaN   NaN  ...   6.0   8.0   NaN   NaN   NaN   7.0

Answered By - Emi OB

Answer Checked By - Terry (PHPFixing Volunteer)