CODEWITHSUNDEEP
pythonpandas
Ison
Ison

Reputation: 403

Transforming data frame (row to column and count)

Sorry for the dumb question, but I got stuck. I have the dataframe with the next structure:

|.....| ID | Cause |         Date       |  
|  1  | AR | SGNLss| 10-05-2019 05:01:00|  
|  2  | TD | PTRXX | 12-05-2019 12:15:00|
|  3  | GZ | FAIL  | 10-05-2019 05:01:00|  
|  4  | AR | PTRXX | 12-05-2019 12:15:00|  
|  5  | GZ | SGNLss| 10-05-2019 05:01:00|
|  6  | AR | FAIL  | 10-05-2019 05:01:00|

What I want is convert DATE column value to columns rounded to day so that the expected DF will have ID, 10-05-2019, 11-05-2019, 12-05-2019... columns and the values - the number of events (Causes) happened on this Id.

It's not a problem to round day and count values separately, but I can't get how to do both these operations.

Upvotes: 0

Views: 39

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150735

You can use pd.crosstab:

pd.crosstab(df['ID'], df['Date'].dt.date)

Output:

Date  2019-10-05  2019-12-05
ID                          
AR             2           1
GZ             2           0
TD             0           1

Upvotes: 2

Related Questions