Sorting by data in another Dataframe

Question

I've been stuck with an engineering problem thats Python/Pandas related. I'd appreciate any help given. I've simplified the numbers so I can better explain myself. I have something similar to the following:

	positioning(x-axis)	Calculated difference
1	0.25	0.05
2	0.75	0.06
3	1.25	0.02
4	0.25	0.05
5	0.75	0.05
6	1.25	0.02
7	0.25	0.09
8	0.75	0.01
9	1.25	0.02
10	0.25	0.05

What I need to do is re-organise the calculated difference based on the x-axis positioning. So it looks something like this:

(0.25)	(0.75)	(1.25)
0.05	0	0
0	0.06	0
0	0	0.02
0.5	0	0
0	0.5	0
0	0	0.02
0.09	0	0
0	0.01	0
0	0	0.02
0.05	0	0

As you can see, I need to organize everything based on the x-positioning. What is the best approach to this problem? Keep in mind I have 2000+ rows and the x positioning is dynamic but I'm currently working till up to 50(so a lot of columns).

I hope I've clarified the question.

Mayank Porwal · Accepted Answer · 2021-03-21 15:57:59Z

4

Use pd.get_dummies:

In [10]: pd.get_dummies(df['positioning(x-axis)']).mul(df['Calculated difference'],axis=0)
Out[10]: 
    0.25  0.75  1.25
1   0.05  0.00  0.00
2   0.00  0.06  0.00
3   0.00  0.00  0.02
4   0.05  0.00  0.00
5   0.00  0.05  0.00
6   0.00  0.00  0.02
7   0.09  0.00  0.00
8   0.00  0.01  0.00
9   0.00  0.00  0.02
10  0.05  0.00  0.00

edited Mar 21, 2021 at 15:57

answered Mar 21, 2021 at 15:55

Mayank Porwal

34.2k9 gold badges45 silver badges65 bronze badges

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

Comments

BENY · Accepted Answer · 2021-03-21 16:04:07Z

3

Just do pivot

df.pivot(columns='positioning(x-axis)',values='Calculated difference').fillna(0)
Out[363]: 
Calculated  0.25  0.75  1.25
0           0.05  0.00  0.00
1           0.00  0.06  0.00
2           0.00  0.00  0.02
3           0.05  0.00  0.00
4           0.00  0.05  0.00
5           0.00  0.00  0.02
6           0.09  0.00  0.00
7           0.00  0.01  0.00
8           0.00  0.00  0.02
9           0.05  0.00  0.00

answered Mar 21, 2021 at 16:04

BENY

324k22 gold badges176 silver badges250 bronze badges

Comments

piRSquared · Accepted Answer · 2021-03-21 16:06:38Z

2

`factorize`

i, p = pd.factorize(df['positioning(x-axis)'])
d = df['Calculated difference'].to_numpy()

a = np.zeros_like(d, shape=(len(df), len(p)))
a[np.arange(len(df)), i] = d

pd.DataFrame(a, df.index, p)

   0.25  0.75  1.25
0  0.05  0.00  0.00
1  0.00  0.06  0.00
2  0.00  0.00  0.02
3  0.05  0.00  0.00
4  0.00  0.05  0.00
5  0.00  0.00  0.02
6  0.09  0.00  0.00
7  0.00  0.01  0.00
8  0.00  0.00  0.02
9  0.05  0.00  0.00

answered Mar 21, 2021 at 16:06

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Comments

ThePyGuy · Accepted Answer · 2021-03-21 16:03:56Z

One way to do this would be to use pandas' pivot and then to reset the index. Given a data frame like this:

     positioning(x-axis)  Calculated difference
0                   0.0                   0.61
1                   0.0                   0.96
2                   0.0                   0.56
3                   0.0                   0.91
4                   0.0                   0.57
5                   0.0                   0.67
6                   0.1                   0.71
7                   0.1                   0.71
8                   0.1                   0.95
9                   0.1                   0.89
10                  0.1                   0.61

df.pivot(columns='positioning(x-axis)', values='Calculated difference').reset_index().drop(columns=['index']).fillna(0)

positioning(x-axis)   0.0   0.1   0.2   0.3   0.4   0.5   0.6   0.7   0.8   0.9   1.0
0                    0.61  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00
1                    0.96  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00
2                    0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.22  0.00  0.00
3                    0.00  0.66  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00
4                    0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.13  0.00  0.00
5                    0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00
6                    0.00  0.00  0.00  0.91  0.00  0.00  0.00  0.00  0.00  0.00  0.00
7                    0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.85
8                    0.00  0.00  0.37  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00
9                    0.00  0.91  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00

Collectives™ on Stack Overflow

Sorting by data in another Dataframe

4 Answers 4

Comments

Comments

`factorize`

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

factorize

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related

`factorize`