Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save mapcloud/3ab282389dd14cdcac06df2cf7efdffd to your computer and use it in GitHub Desktop.
Save mapcloud/3ab282389dd14cdcac06df2cf7efdffd to your computer and use it in GitHub Desktop.
pandas data frame rows into columns based by category. Kind of transposing the data.
# Source: https://stackoverflow.com/questions/39635993/how-to-convert-pandas-dataframe-rows-into-columns-based-on-category
# convert the module variables into columns and group by the id. So something like:
# Example
ls = [{'count':5, 'module':'payroll', 'id':2}, {'count': 53, 'module': 'general','id':2}, {'id': 5,'count': 35, 'module': 'tax'}, ]
df = pd.DataFrame.from_dict(ls)
# Solution
# You can use groupby by columns which first create new index and last column. then need aggreagate some way - I use mean, then convert one column DataFrame to Series by DataFrame.squeeze (then is not necessary remove top level of Multiindex in columns) and reshape by unstack. Last add_suffix to column name
df = df.groupby(['id','module']).mean().squeeze().unstack().add_suffix('_count')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment