2

I have data containing a list of topics (topics 1-5; and 0 meaning no topic is assigned) and their value. I want to create a new column for each topic and fill the column with the value. Here's what the table looks like...

reviewId   topic   value
      01       2      -4
      02       2       9
      03       0      -7
      04       5      -1
      05       1      38

What should I do to create a table looking like this?

reviewId topic value t1 t2 t3 t4 t5
01 2 -4 0 -4 0 0 0
02 2 9 0 9 0 0 0
03 0 -7 0 0 0 0 0
04 5 -1 0 0 0 0 -1
05 1 38 38 0 0 0 0

Here's reproducible data to work on.

raw_df = pd.DataFrame({
    'reviewId': ['01', '02', '03', '04', '05'],
    'topic': [2, 2, 0, 5, 1],
    'value': ['-4', '9', '-7', '-1', '38']})

Here's a link to a similar problem with this one.

Dewani
  • 137
  • 6

2 Answers2

6
df.join(pd.get_dummies(df["topic"])
          .reindex(columns=range(1, 5+1), fill_value=0)
          .mul(df["value"], axis="rows")
          .add_prefix("t"))
  • get "1-hot" representation of the "topic" column
  • some topics may be missing, e.g., 3, 4 in the sample data, so reindex to guarantee that
    • and put zeros to those topics values as they were missing
  • now we have a 1/0 dataframe of shape (len(df), len(topics))
    • we multiply this with the "value" column row-wise
    • 1/0's will act as a "selector" of values
  • add "t" from left to the column names for "t1" ... "t5"

and join it with the original frame to get

  reviewId  topic  value  t1  t2  t3  t4  t5
0       01      2     -4   0  -4   0   0   0
1       02      2      9   0   9   0   0   0
2       03      0     -7   0   0   0   0   0
3       04      5     -1   0   0   0   0  -1
4       05      1     38  38   0   0   0   0
Mustafa Aydın
  • 17,645
  • 4
  • 15
  • 38
  • 1
    Same method is also possible with `df.pivot`. E.g. `df.join(df.pivot(columns='topic', values='value').reindex(columns=range(1, 5+1), fill_value=0).fillna(0).add_prefix("t"))`. – ouroboros1 Jan 15 '23 at 08:51
1

So A way I figured it out(Might not be the most optimal way is )

First you get all the topics in your original df.

new_df = pd.get_dummies(raw_df['topic'])

The output will be

   0  1  2  5
0  0  0  1  0
1  0  0  1  0
2  1  0  0  0
3  0  0  0  1
4  0  1  0  0

Where 0 1 2 5 are your t0, t1, t2, t5 based on unique values in topic.

Now you can set all values to 0 initially.


> Blockquote

new_df.loc[:] = 0

Then you can combine both dfs. So

new_df2 = pd.concat([raw_df, new_df], axis=1)

This will concatenate based on columns.

Output will be

  reviewId  topic value  0  1  2  5
0       01      2    -4  0  0  0  0
1       02      2     9  0  0  0  0
2       03      0    -7  0  0  0  0
3       04      5    -1  0  0  0  0
4       05      1    38  0  0  0  0

Now you can simply loop and update the values.

for i, x in enumerate(new_df2['topic'].values):
     print(i, x)
     new_df2.loc[i, int(x)] = new_df2.loc[i, "value"]

So the complete code will be

In [63]: new_df = pd.get_dummies(raw_df['topic'])
    ...: new_df.loc[:] = 0
    ...: new_df2 = pd.concat([raw_df, new_df], axis=1)
    ...: for i, x in enumerate(new_df2['topic'].values):
    ...:      new_df2.loc[i, int(x)] = new_df2.loc[i, "value"]

Hope it helps a bit

Ahmad Anis
  • 2,322
  • 4
  • 25
  • 54