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!

  • $\begingroup$ Please add some actual data examples to show us what the input data is and what the wanted output would look like. $\endgroup$
    – Oxbowerce
    Dec 13, 2021 at 10:06

1 Answer 1


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


Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.