How to group by certain columns and invert the group by in Python

Question

So I have a Data Frame for which there is the same ID contains multiple Custom Fields. I found this question but it's not quite what I am looking for. Code to create desired starter data frame below

df = pd.DataFrame()
df['ID'] = [np.random.randint(1,2000) for x in range(0,1000)]
new = pd.DataFrame()
for x in range(0,10):
    new = new.append(df)
new = new.sort_values('ID').reset_index(drop=True)
new['Custom Field'] = [np.random.randint(1,20) for x in new['ID']]
new['Value'] = [np.random.randint(0,10000000) for x in new['ID']]
new = new.groupby(['ID','Custom Field']).first().reset_index()
new = new.sort_values(['ID','Custom Field']).reset_index(drop=True)
new.head()

Essentially the below picture is what I am looking for:

This image shows that it's taking the values in the Custom Field table and transposing them into separate columns. For every ID it can have up to 20 values in the Custom Field table. I need each of the custom field values (1-20) to be in their own column. If a certain ID does not have the value, it will be blank. I am trying to be as specific as possible but it's hard to explain. Let me know if I need to edit the question to provide more detail.

You should set a seed so your data and results can be reproduced. — cs95, Oct 08 '17 at 04:44
How do I set a seed? Also I've created the data so that it always has the parameters I need. — Zander, Oct 08 '17 at 04:46
Maybe `new.pivot_table(index='ID', columns='Custom Field', values='Value')`? — cs95, Oct 08 '17 at 04:47
Next time search before asking and post only text than images. Hard to convert them to text. — Bharath M Shetty, Oct 08 '17 at 04:55

score 2 · Accepted Answer · answered Oct 08 '17 at 04:52

Use pivot with add prefix i.e

df.pivot('ID','Custom_Field','Value').add_prefix('CF')

Custom_Field        CF1        CF2        CF3        CF7        CF8  \
ID                                                                    
1                   NaN  5643962.0  6959658.0  4310939.0  5796051.0   
2             1121049.0  6044077.0        NaN        NaN        NaN   

Custom_Field        CF9       CF12       CF13       CF15       CF16       CF19  
ID                                                                              
1             1198701.0        NaN  2925189.0  8438978.0  1730570.0  3481493.0  
2             4483108.0  3327149.0        NaN  2700632.0        NaN  3249005.0

How to group by certain columns and invert the group by in Python

1 Answers1