2

I have a dataframe in python and would like to combine multiple rows together into 1 row to which share a common value in a column. Here is the dataframe:

df=pd.DataFrame({'drug1' : {0 : 'ZPE', 1 : 'HPE'},
             'plate_id' : {0 : '1', 1 : '1'},
             'response' : {0 : '14240', 1 : '147'},
             'cell_line_name' : {0 : 'H2009', 1 : 'H2009'},
             'cell_number_at_time0 ': {0 : '1000', 1 : '1000'},
             'response_stdev' : {0 : '1228', 1 : '23'}
             })
print(df)
      Index drug1 plate_id response cell_line_name cell_number_at_time0  response_stdev
         0   ZPE        1    14240          H2009                  1000           1228
         1   HPE        1      147          H2009                  1000             23

I would now like to combine the two rows based on plate_id and create a new columns named ZPE_response HPE_response ZPE_response_stdev HPE_response.

I will have multiple plates within the dataframe. I tried using pandas melt, pandas wide_to_long, and pivot but nothing works.

enter image description here

stats_table_stdev=stats_table.pivot_table(columns='drug1', index=['plate_id', 'cell_line_name', 'cell_number_at_time0'], values='response_stdev')
stats_table_stdev.rename(columns={'HPE':'HPE_std'}, inplace=True)
stats_table_stdev.rename(columns={'ZPE':'ZPE_std'}, inplace=True)
stats_table_export=pd.merge(left=stats_table_mean, right=stats_table_stdev, left_on=('plate_id', 'cell_line_name', 'cell_number_at_time0'), right_on=('plate_id', 'cell_line_name','cell_number_at_time0'))`
chionj
  • 35
  • 4
  • Please [edit] to convert your images of text into actual text. [See here](https://meta.stackoverflow.com/a/285557/11107541) for why. See also: ["how to format a table in a post"](https://meta.stackoverflow.com/q/277716/11107541). – starball Jan 05 '23 at 05:37
  • 1
    You have to pivot your dataframe then flat your index ('HPE', 'response') to 'HPE_response' – Corralien Jan 05 '23 at 05:40

0 Answers0