CODEWITHSUNDEEP
pandas
Joe Fedorowicz
Joe Fedorowicz

Reputation: 785

Pandas Pivot Multiple Date Columns

I have a data frame like this:

  ColumnA ColumnB  1/1/20  1/2/20  1/3/20
0  Thing1   Item1       4       5       3
1  Thing2   Item1       4       4       5
2  Thing3   Item2       3       4       5

That I'd like to look like this:

Result

But I can't figure out the best method to achieve this via pandas. Any help would be much appreciated. Thanks.

Upvotes: 1

Views: 240

Answers (1)

piRSquared
piRSquared

Reputation: 294228

melt

df.melt(['ColumnA', 'ColumnB'], var_name='Date', value_name='Count')

  ColumnA ColumnB    Date  Count
0  Thing1   Item1  1/1/20      4
1  Thing2   Item1  1/1/20      4
2  Thing3   Item2  1/1/20      3
3  Thing1   Item1  1/2/20      5
4  Thing2   Item1  1/2/20      4
5  Thing3   Item2  1/2/20      4
6  Thing1   Item1  1/3/20      3
7  Thing2   Item1  1/3/20      5
8  Thing3   Item2  1/3/20      5

Clever Comprehension

colname_1, colname_2, *dates = [*df]
data = [
    (c1, c2, date, count)
    for c1, c2, *counts in zip(*map(df.get, df))
    for date, count in zip(dates, counts)
]
pd.DataFrame(data, columns=[colname_1, colname_2, 'Date', 'Count'])

  ColumnA ColumnB    Date  Count
0  Thing1   Item1  1/1/20      4
1  Thing1   Item1  1/2/20      5
2  Thing1   Item1  1/3/20      3
3  Thing2   Item1  1/1/20      4
4  Thing2   Item1  1/2/20      4
5  Thing2   Item1  1/3/20      5
6  Thing3   Item2  1/1/20      3
7  Thing3   Item2  1/2/20      4
8  Thing3   Item2  1/3/20      5

Upvotes: 3

Related Questions