How can we split the data as mention below?
Sample Data:
EmployeeId City join_month Year
0 001 Mumbai 1 2018
1 001 Bangalore 3 2018
2 002 Pune 2 2019
3 002 Mumbai 6 2017
4 003 Delhi 9 2018
5 003 Mumbai 12 2019
6 004 Bangalore 11 2017
7 004 Pune 10 2018
8 005 Mumbai 5 2017
Required Output should be -
EmployeeId City join_month Year 2018_jan_count 2018_feb_count 2018_march_count
0 001 Mumbai 1 2018
1 001 Bangalore 3 2018
2 002 Pune 2 2019
3 002 Mumbai 6 2017
4 003 Delhi 9 2018
5 003 Mumbai 12 2019
6 004 Bangalore 11 2017
7 004 Pune 10 2018
8 005 Mumbai 5 2017
You can use df.apply
df.apply(pd.value_counts)
This will apply a column based aggregation function (in this case date)