How to transpose a row within a pandas dataframe and create a columns?

Question

I have the following list:

list_1 = [{'28d_click': '2', 'action_type': 'comment', 'value': '2'},
 {'28d_click': '1779',
  '7d_view': '11144',
  'action_type': 'offsite_conversion.custom.xx',
  'value': '9425'},
 {'28d_click': '122', 'action_type': 'landing_page_view', 'value': '122'},
 {'28d_click': '21', 'action_type': 'like', 'value': '21'},
 {'28d_click': '175', 'action_type': 'link_click', 'value': '175'},
 {'28d_click': '1', 'action_type': 'post', 'value': '1'},
 {'28d_click': '23', 'action_type': 'post_reaction', 'value': '23'},
 {'28d_click': '222', 'action_type': 'page_engagement', 'value': '222'},
 {'28d_click': '201', 'action_type': 'post_engagement', 'value': '201'},
 {'28d_click': '1936',
  '7d_view': '11171',
  'action_type': 'offsite_conversion',
  'value': '9607'}]

I have then used this list to create a pandas DataFrame: df = pd.DataFrame(list_1)

What I would like to do is use the action_type as columns and store the values in the rows beneath:

Below is just an example - please NOTE: some action_types (conversions) will need to have two fields (28d_click) and (7d_view) as separate columns. EG: Column1: Conversion_1 (28d_click) | Column2: Conversion_1 (7d_view)

landing_page_view   link_click  offsite_conversion.custom.xx (28d_click)  offsite_conversion.custom.xx (7d_view).
122                 175         7                                         16

What I have tried:

df = pd.DataFrame(list_1).T

Almost got the headings the way I wanted, but not quite with the 28d and 7d values

print([d['action_type'] for d in list_1 if 'action_type' in d])
print([d['value'] for d in list_1 if 'value' in d])
print([d['28d_click'] for d in list_1 if '28d_click' in d])
print([d['7d_view'] for d in list_1 if '7d_view' in d])

This creates individual lists, but then not entirely sure what to do with them

Is what I'm asking for possible?

I have another DataFrame which I want to join this with but wanted to get this right first.

Any help would be greatly appreciated.

Thanks,

Adrian

jezrael · Accepted Answer · 2017-11-05 05:45:20Z

1

First set_index, then reshape by stack, create one column DataFrame by to_frame and transpose.

Get MultiIndex in columns, so is necessary flatting by map with separator _:

df = pd.DataFrame(list_1).set_index('action_type').stack().to_frame(0).T
df.columns = df.columns.map('_'.join)

print (df)
 comment_28d_click comment_value offsite_conversion.custom.xx_28d_click  \
0                 2             2                                   1779   

  offsite_conversion.custom.xx_7d_view offsite_conversion.custom.xx_value  \
0                                11144                               9425   

  landing_page_view_28d_click landing_page_view_value like_28d_click  \
0                         122                     122             21   

  like_value link_click_28d_click           ...            post_value  \
0         21                  175           ...                     1   

  post_reaction_28d_click post_reaction_value page_engagement_28d_click  \
0                      23                  23                       222   

  page_engagement_value post_engagement_28d_click post_engagement_value  \
0                   222                       201                   201   

  offsite_conversion_28d_click offsite_conversion_7d_view  \
0                         1936                      11171   

  offsite_conversion_value  
0                     9607  

[1 rows x 22 columns]

edited Nov 5, 2017 at 5:45

answered Nov 5, 2017 at 5:38

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

AdrianC Over a year ago

This worked. Thank you! Can you please explain how the second line works? I have broken the first line down (and it makes perfect sense) but why does '_'.join work in this line?

jezrael Over a year ago

Yes, give me a sec.

AdrianC Over a year ago

Thanks for taking the time to help me! Worked perfectly.

Collectives™ on Stack Overflow

How to transpose a row within a pandas dataframe and create a columns?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related