1

I have data with 3 locations. I would like to group by my locations and create new pandas DataFrame.

I have pandas DataFrame as follows:

Day  Time  LocationA  LocationB
1    8     XX         YY
1    8     XX         ZZ
1    8     XX         ZZ    
1    9     YY         ZZ
1    9     XX         YY
1    9     ZZ         XX
1    9     ZZ         YY
2    8     XX         ZZ
2    8     XX         YY

I need pandas DataFrame as follows:

Day  Time  Location  A  B
1    8     XX        3  0
1    8     YY        0  1
1    8     ZZ        0  2
1    9     XX        1  1
1    9     YY        1  2
1    9     ZZ        2  1
2    8     XX        2  0
2    8     YY        0  1
2    8     ZZ        0  1
Improve this question
2
  • please use code blocks to properly format the data Commented Jun 8, 2019 at 3:00
  • Thank you, I have corrected code blocks. Commented Jun 8, 2019 at 3:04

1 Answer 1

Reset to default
4

In your case using melt then groupby + stack 

yourdf=df.melt(['Day','Time']).groupby(['Day','Time','variable']).value.value_counts().unstack(level=2,fill_value=0).reset_index()
yourdf
Out[405]: 
variable  Day  Time value  LocationA  LocationB
0           1     8    XX          3          0
1           1     8    YY          0          1
2           1     8    ZZ          0          2
3           1     9    XX          1          1
4           1     9    YY          1          2
5           1     9    ZZ          2          1
6           2     8    XX          2          0
7           2     8    YY          0          1
8           2     8    ZZ          0          1
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.