CODEWITHSUNDEEP
pythonpandasdataframe
Rink Stiekema
Rink Stiekema

Reputation: 444

Add columns to a dataframe based on categorical values of existing column

I've got a dataframe that has a column containing the name of a variable, and a column containing the value of this variable. Some example rows in this table could be:

    id      variable    value   date
0   AS14.01 mood        6.000   2014-02-26
1   AS14.01 acti        0.091   2014-03-20
2   AS14.01 pers        0.101   2014-04-21

I want to transform this into a dataframe where each unique value in the variable column forms a new column containing the value of the respective row. There should be only 1 row per date for a specific id. Example rows for the dataframe given above would be:

    id      date        mood   acti   pers
0   AS14.01 2014-02-26  6.000  NaN    NaN
1   AS14.01 2014-03-20  NaN    0.091  NaN
2   AS14.01 2014-04-21  NaN    NaN    0.101

Maybe a similar question has been posted on stackoverflow before, but I cannot find the correct wording for this problem. Hope that anyone can help.

Upvotes: 2

Views: 922

Answers (1)

hilberts_drinking_problem
hilberts_drinking_problem

Reputation: 11602

Here is one way with get_dummies. You can also try using pivoting. This does not delete the original variable column automatically.

df = df.join(pd.get_dummies(df.variable).replace(0, np.nan).mul(df.value, axis=0))


        # id variable  value        date   acti  mood   pers
# 0  AS14.01     mood  6.000  2014-02-26    NaN   6.0    NaN
# 1  AS14.01     acti  0.091  2014-03-20  0.091   NaN    NaN
# 2  AS14.01     pers  0.101  2014-04-21    NaN   NaN  0.101

Upvotes: 1

Related Questions