Q:

convert some observations into variables

I have a table formatted like the following table:
Feature amount  ID  Location
Feat1      2    1   US
Feat2      0    1   US
Feat3      0    1   US
Feat4      1    1   US
Feat2      2    2   US
Feat4      0    2   US
Feat3      0    2   US
Feat6      1    2   US

Let's say I have 200 different IDs.
I want to convert all different features into variables and amount into observations, so I combine rows with same ID into one row. For example,
Feat1 Feat2 Feat3 Feat4 Feat5 Feat6 ID Location
  2     0     0     1    NA    NA   1    US
 NA     2     0     0    NA    1    2    US

Is there a good way to do it either in Python (pandas) or R?
Thanks in advance!

A:

Assume your table can be put into a pandas DataFrame object data with 4 columns as above. One way to achieve what you want is to do a GROUPBY using ID and Location. Then gradually assign values to each row of new table:
    newdata = pd.DataFrame(columns=['ID', 'Location', 'Feat1', 'Feat2', 'Feat3', 'Feat4', 'Feat5', 'Feat6'])
    grouped = data.groupby(['ID', 'Location'])

    for index, (group_name, d) in enumerate(grouped):
        newdata.loc[index, 'ID'] = group_name[0]
        newdata.loc[index, 'Location'] = group_name[1]
        for feature, amount in zip(d['Feature'], d['amount']):
            newdata.loc[index, feature] = amount

