CODEWITHSUNDEEP
pythonpandasdataframe
Rosa Alejandra
Rosa Alejandra

Reputation: 732

create new columns with info of other column on python pandas DataFrame

I have a grouped dataframe

 id  num  week  
101   23   7     3
           8     1
           9     2
102   34   8     4
           9     1
          10     2
       ...

And I need to create new columns and have a dataFrame like this

 id  num  7  8  9  10
101   23  3  1  2   0
102   34  0  4  1   2
        ...

As you may see, the values of the week column turned into several columns.

I may also have the input dataFrame not grouped, or with reset_index, like this:

 id  num  week  
101   23   7     3
101   23   8     1
101   23   9     2
102   34   8     4
102   34   9     1
102   34  10     2
       ...

but I don't know with which would be easier to start.

Notice that id and num are both keys

Upvotes: 2

Views: 58

Answers (1)

Julien Marrec
Julien Marrec

Reputation: 11895

Use unstack() and fillna(0) to not have NaNs.

Let's load the data:

id  num  week  val
101   23   7     3
101   23   8     1
101   23   9     2
102   34   8     4
102   34   9     1
102   34   10    2

s = pd.read_clipboard(index_col=[0,1,2], squeeze=True)

Notice I have set the index to be id, num and week. If you haven't yet, use set_index.

Now we can unstack: move from the index (rows) to the columns. By default it does it to the last level in line, which is week here, but you could specify it using level=-1 or level='week'

s.unstack().fillna(0)

Note that as pointed out by @piRsquared you can do s.unstack(fill_value=0) to do it in one go.

result

Upvotes: 4

Related Questions