panda grouping dates by variable with transposed value variables

I had read this post

panda grouping by month with transpose

and it gave me the nearest answer to my question but not the completely solution.

How would I get somewhat like the reverse output?

My target is: I have a pivoted df with a grouped text variable like above in the second pic and dates are my columns. But I would like to get the dates grouped by type and the text variable values are my new columns. It would something like txt and transdate would be transposed but grouped by type. I had used some approaches like grouppy, unstack, reshape but none of them worked completely well and I don't get the correct result.

Posted this as a unique question in hope to get more suggestions and help from the community. Thanks in advance to all of you!

Topic groupby reshape time-series pandas

Category Data Science


Sorry about that, I thought the link would be enough.

df of regional states and country towns

So, I have a df containing the German regional states and its country towns and some codes indicating policies. In the columns are the dates as longitudinal indicators of the specific policies. The desired output is to transpose the date columns and the policy column. So that each policy would have a unique column. And the date range can be used as index variable which repeates for every country town. If I use

df.groupby(['bl', 'kreis', 'policy', 'd20200301', 'd20200302', ...]).size().unstack() # without 'kreisziffer' because it only indicates the 'kreis' as integer and is irrelevant in that case

grouped df

the output grouped and the regional states and country towns only occur ones and the policy will group to this categories.

If I use df.transpose() I only get columns as rownames but the policies will further be replicated by each country town although I get the dates in the desired format as a single column.

transposed dataset

If i use df.pivot(index = ['kreis', 'bl'], columns = 'policy') thte single dates are hierarical indexed with each policy value.

pivoted df

If I use df.melt(id_vars= ['bl', 'kreisziffer', 'kreis', 'policy]) I will get each cell value as a new column named value but the df part of the policy x dates should be something like transposed. But policies are unique columns and each value block reffering to a country town will be concatenated and the date interval is replicated each time the 'kreis' variable value changes. Something like that:

desired df example

About

Geeks Mental is a community that publishes articles and tutorials about Web, Android, Data Science, new techniques and Linux security.