0

This is the dataframe I have:

       chr  value   region
      chr22     1   21-77
       chr6     3   12-65
       chr3     5   73-81
       chr3     8   91-96

And this is what I need:

                       21-77    12-65   73-81   91-96
           chr22        1       0       0       0                           
           chr6         0       3       0       0
           chr3         0       0       5       8

Please note that the first column of the initial dataframe contains duplicate values. (such as chr3)

Could you tell me how I can achieve this please. Thanks in advance.

anilbey
  • 1,817
  • 4
  • 22
  • 38
  • 1
    This should be easy to do with pandas. Please give http://pandas.pydata.org/pandas-docs/stable/reshaping.html a read – nehiljain Apr 04 '17 at 15:37

2 Answers2

1

Looks like the perfect application for pandas pivot_table.

Worth highlighting that pivot_table uses numpy mean as aggregation function (in case there are multiple observations with same index & column. So it implicitly requires numbers (int/floats) as values by default.

Let frame be the pandas dataframe containing your data:

import pandas as pd

cc = ['chr', 'value', 'region']
vals = [['chr22', 1, '21-77'],
       ['chr6',     3,   '12-65'],
       ['chr3',     5,   '73-81'],
       ['chr3',     8,   '91-96']]

frame = pd.DataFrame(vals, columns = cc)

result = pd.pivot_table(frame,
                        values = 'value', index = ['chr'], columns = ['region'],
                        fill_value = 0)
FLab
  • 7,136
  • 5
  • 36
  • 69
  • it raises DataError('No numeric types to aggregate') pandas.core.base.DataError: No numeric types to aggregate What might be the cause of it? – anilbey Apr 04 '17 at 16:04
  • This is the example I used and it runs correctly. Are you sure this is representative of your real case? – FLab Apr 04 '17 at 16:05
  • which version of pandas are you using? – FLab Apr 04 '17 at 16:09
0

Would this link help?

For future reference, please do research before posting questions, as there might already be answers that will help you, or maybe there are already people that have resolved the same problem you are having.

Diggy.
  • 6,744
  • 3
  • 19
  • 38