Keep only one line per date when doing get_dummy in python

Question

I have a data frame like this

Item  Value  Date
A      30    01/01/2010
A      50    01/02/2010
A      70    01/03/2010
A      50    01/04/2010
B      10    01/01/2010
B      15    01/02/2010
B      23    01/03/2010
B      11    01/04/2010

And I would like to convert it to a dataframe looking like this

Date         A    B
01/01/2010   30   10
01/02/2010   50   15
01/03/2010   70   23
01/04/2010   50   11

Keeping one line per date with all the information of each item, how can I do that?

can you be sure that there will be exactly one A and one B value for each date? — timgeb, May 02 '20 at 07:52

score 2 · Answer 1 · answered May 02 '20 at 07:57

2

Try pivot:

df.pivot(index="Date", columns="Item", values="Value")

Arguments used:

index: column used as new index
columns: column used to set new dataframe columns
values: values in the new columns

output

# Item         A   B
# Date
# 01/01/2010  30  10
# 01/02/2010  50  15
# 01/03/2010  70  23
# 01/04/2010  50  11

For much more details about this kind of transformation, see How to pivot a dataframe

answered May 02 '20 at 07:57

Alexandre B.

5,387
2
17
40

2

Equivalent `groupby`, but a little longer: `df.groupby(['Date', 'Item'])['Value'].first().unstack()` – timgeb May 02 '20 at 08:02

Keep only one line per date when doing get_dummy in python

1 Answers1