Pandas DataFrame: assigning values to multiple separate elements

Question

I have the following dataframe:

           Well_ID  BOPD
Date                    
2020-01-01     101     1
2020-01-02     102     2
2020-01-03     102     3
2020-01-04     101     1
2020-01-05     103     2
2020-01-03     101     3

which I would like to reformat into:

            101  102  103
2020-01-01  1.0  NaN  NaN
2020-01-02  NaN  2.0  NaN
2020-01-03  3.0  3.0  NaN
2020-01-04  1.0  NaN  NaN
2020-01-05  NaN  NaN  2.0
2020-01-06  NaN  NaN  NaN
2020-01-07  NaN  NaN  NaN
2020-01-08  NaN  NaN  NaN
2020-01-09  NaN  NaN  NaN

Which I can obtain using the for loop:

for well, date, bopd in zip(wellTest['Well_ID'].tolist(),wellTest.index.to_list(),wellTest['BOPD'].to_list()):
    wellTestBr.loc[date, well] = bopd

Really think there should be a better way.

score 1 · Accepted Answer · edited Mar 02 '20 at 15:57

1

You're looking for pivot:

df.reset_index().pivot('Date', 'Well_ID', 'BOPD')

Well_ID     101  102  103
Date
2020-01-01  1.0  NaN  NaN
2020-01-02  NaN  2.0  NaN
2020-01-03  3.0  3.0  NaN
2020-01-04  1.0  NaN  NaN
2020-01-05  NaN  NaN  2.0

edited Mar 02 '20 at 15:57

Quang Hoang

131,600
10
43
63

answered Mar 02 '20 at 15:55

yatu

80,714
11
64
111

score 0 · Answer 2 · answered Mar 02 '20 at 15:57

You can set_index and unstack:

df.set_index(['Well_ID'], append=True)['BOPD'].unstack()

Output;

Well_ID     101  102  103
Date                     
2020-01-01  1.0  NaN  NaN
2020-01-02  NaN  2.0  NaN
2020-01-03  3.0  3.0  NaN
2020-01-04  1.0  NaN  NaN
2020-01-05  NaN  NaN  2.0

Pandas DataFrame: assigning values to multiple separate elements

2 Answers2