0

I have a dataframe df like this:

date1     date2     A
2015.1.1  2015.1.2  1
2015.1.1  2015.1.3  2
2015.1.1  2015.1.4  3
2015.1.2  2015.1.3  4
2015.1.2  2015.1.4  5
2015.1.3  2015.1.4  6

<class 'pandas.core.frame.DataFrame'>
Int64Index: 1711 entries, 0 to 1710
Data columns (total 3 columns):
date1    1711 non-null datetime64[ns]
date2    1711 non-null datetime64[ns]
A        1711 non-null float64
dtypes: datetime64[ns](2), float64(1)
memory usage: 53.5 KB

How to convert df to a matrix mx like this:

         2015.1.2 2015.1.3 2015.1.4
2015.1.1    1         2       3
2015.1.2    -         4       5
2015.1.3    -         -       6

Then, I need to export mx to the Excel or csv file.

Anand S Kumar
  • 88,551
  • 18
  • 188
  • 176
seizetheday
  • 333
  • 1
  • 6
  • 15

1 Answers1

3

This is a classic application of pivoting, where one data column becomes the index, another data column is used for the column headers, and the data is distributed in the new DataFrame according to the values they were associated with in the original table.

In [19]: df
Out[19]:
      date1     date2  A
0  2015.1.1  2015.1.2  1
1  2015.1.1  2015.1.3  2
2  2015.1.1  2015.1.4  3
3  2015.1.2  2015.1.3  4
4  2015.1.2  2015.1.4  5
5  2015.1.3  2015.1.4  6

In [20]: df.pivot(index="date1", columns="date2", values="A")
Out[20]:
date2    2015.1.2 2015.1.3 2015.1.4
date1
2015.1.1        1        2        3
2015.1.2      NaN        4        5
2015.1.3      NaN      NaN        6
holdenweb
  • 33,305
  • 7
  • 57
  • 77