Pandas: Adding data from multiple rows into extra columns for a single row

Question

I have a pandas dataframe like this:

id  value
1   25
2   40
3   30

Ideally I would like to convert it to this:

id value value_2  value_3
1   25    40       30
2   40    25       30
3   30    25       40

The logic behind the above conversation is that 2 extra columns containg the values for the other ids are added.

In the first row (id=1), value_2 = the value of the id=2, and value_3 = the value of id = 3.

In the second row (id=2), value_2 = the value of id=1, value_3 = the value of id=3

In the 3rd row (id=3), value_2 = the value of id=1, value_3 = the value of id=2

Is there a solution that allows me update all rows at once without having to iterate over each row, getting the value and then updating the dataframe of the other rows (one row at the time). Or what is the easiest solution to above challenge?

How are id_2 and id_3 associated with id? A dictionary? Another dataframe? — Michael Silverstein
– Michael Silverstein, Commented Nov 17, 2019 at 19:22
Hello. Sorry it was supposed to be value_2 and value_3 (edited this now). If we are in the first row (id=1) then value_2 = the value of id=2 and value_3 = the value id = 3. — MathiasRa
– MathiasRa, Commented Nov 17, 2019 at 19:25

Dani Mesejo · Accepted Answer · 2019-11-17 19:45:41Z

1

IUUC, you could do the following:

# create array (repeat of value)
repeats = np.tile(df['value'].values, (len(df), 1))

# remove elements from the diagonal
m = repeats.shape[0]
data = repeats[~np.eye(len(df), dtype=bool)].reshape(m, -1)

# create new DataFrame
df2 = pd.DataFrame(data=data[:, :], columns='value_' + df['id'].astype(str)[1:])

# concat old and new data
result = pd.concat([df, df2], axis=1)

print(result)

Output

   id  value  value_2  value_3
0   1     25       40       30
1   2     40       25       30
2   3     30       25       40

answered Nov 17, 2019 at 19:45

Dani Mesejo

62.2k6 gold badges56 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pandas: Adding data from multiple rows into extra columns for a single row

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related