Split column into multiple columns based on variable

Question

I'm brand new to python and data science, and I have a dataframe which is

WellID  x  y  rho1  rho2  rho3  ...   rho5  dep1  dep2  dep3  dep4  dep5
0       1  5  3    44    67    34  ...     65     0     1     2     3     4
1       2  1  6    87    67    67  ...     34     0     1     2     3     4
2       3  6  3    65    34    34  ...     65     0     1     2     3     4
3       4  5  6    98    45    67  ...     32     0     1     2     3     4
4       5  3  3    34    65    34  ...     65     0     1     2     3     4
5       6  4  5    67    98    98  ...     34     0     1     2     3     4

After melting the df, my product is

df.melt(['WellID','x','y'])

WellID  x  y variable  value
0        1  5  3     rho1     44
48       1  5  3     dep4      3
42       1  5  3     dep3      2
36       1  5  3     dep2      1
6        1  5  3     rho2     67
30       1  5  3     dep1      0
24       1  5  3     rho5     65
54       1  5  3     dep5      4
12       1  5  3     rho3     34
18       1  5  3     rho4     67
43       2  1  6     dep3      2
37       2  1  6     dep2      1
31       2  1  6     dep1      0
49       2  1  6     dep4      3
19       2  1  6     rho4     34
.
.
.
.

and so forth.

However, what I need is a df where I have a 'dep' and 'rho' column which are correlated to the specific 'WellID' and numeric value of dep and rho, ie

This is the output I'm looking for

WellID  x  y  Dep  Rho
1       1  5  dep1   rho1
1       1  5  dep2   rho2
1       1  5  dep3   rho3
1       1  5  dep4   rho4
2       5  3  dep1   rho1
2       5  3  dep2   rho2
2       5  3  dep3   rho3

and so on, where the value in the depth and rho column are the corresponding values.

I've played around with pd.pivot, .stack() and .unstack() and some other stuff, but pd.melt() is the closest I've gotten.

It is probably worth noting that the 'dep' values are continuous through the data, but the 'rho' values are unique to each point.

Thanks

This was the melt initially used to go from the original df to the second one. Post edited for clarity. — bogey992, Feb 13 '19 at 22:35

score 0 · Accepted Answer · answered Feb 13 '19 at 22:40

0

You can try with wide_to_long

Newdf=pd.wide_to_long(df,['rho','dep'],i=['WellID','x','y'],j='drop').reset_index(level=[0,1,2])

answered Feb 13 '19 at 22:40

BENY

317,841
20
164
234

I'm not sure how to do this, as I don't have defined columns for 'rho' or 'dep', currently. Were you referencing the third df I posted? If so, that's my theoretical endpoint, not one I currently have. – bogey992 Feb 13 '19 at 23:03
@bogey992 df is your initial data – BENY Feb 13 '19 at 23:11
I don't know what I had keyed in incorrectly, but tried again and it worked. Thanks so much! – bogey992 Feb 13 '19 at 23:15
@bogey992 happy coding – BENY Feb 13 '19 at 23:16

Split column into multiple columns based on variable

1 Answers1