What is the best way to reshape the following dataframe in pandas? This DataFrame df
has x,y
values for each sample (s1
and s2
in this case) and looks like this:
In [23]: df = pandas.DataFrame({"s1_x": scipy.randn(10), "s1_y": scipy.randn(10), "s2_x": scipy.randn(10), "s2_y": scipy.randn(10)})
In [24]: df
Out[24]:
s1_x s1_y s2_x s2_y
0 0.913462 0.525590 -0.377640 0.700720
1 0.723288 -0.691715 0.127153 0.180836
2 0.181631 -1.090529 -1.392552 1.530669
3 0.997414 -1.486094 1.207012 0.376120
4 -0.319841 0.195289 -1.034683 0.286073
5 1.085154 -0.619635 0.396867 0.623482
6 1.867816 -0.928101 -0.491929 -0.955295
7 0.920658 -1.132057 1.701582 -0.110299
8 -0.241853 -0.129702 -0.809852 0.014802
9 -0.019523 -0.578930 0.803688 -0.881875
s1_x
and s1_y
are the x/y values for sample 1, s2_x, s2_y
are the sample values for sample 2, etc. How can this be reshaped into a DataFrame containing only x
, y
columns but that contains an additional column sample
that says for each row in the DataFrame whether it's from s1
or s2
? E.g.
x y sample
0 0.913462 0.525590 s1
1 0.723288 -0.691715 s1
2 0.181631 -1.090529 s1
3 0.997414 -1.486094 s1
...
5 0.396867 0.623482 s2
...
This is useful for plotting things with Rpy2 later on, since many R plotting features can make use of this grouping variable, so that's my motivation for reshaping the dataframe.
I think the answer given by Chang She doesn't translate to dataframes that have a unique index, like this one:
In [636]: df = pandas.DataFrame({"s1_x": scipy.randn(10), "s1_y": scipy.randn(10), "s2_x": scipy.randn(10), "s2_y": scipy.randn(10), "names": range(10)})
In [637]: df
Out[637]:
names s1_x s1_y s2_x s2_y
0 0 0.672298 0.415366 1.034770 0.556209
1 1 0.067087 -0.851028 0.053608 -0.276461
2 2 -0.674174 -0.099015 0.864148 -0.067240
3 3 0.542996 -0.813018 2.283530 2.793727
4 4 0.216633 -0.091870 -0.746411 -0.421852
5 5 0.141301 -1.537721 -0.371601 -1.594634
6 6 1.267148 -0.833120 0.369516 -0.671627
7 7 -0.231163 -0.557398 1.123155 0.865140
8 8 1.790570 -0.428563 0.668987 0.632409
9 9 -0.820315 -0.894855 0.673247 -1.195831
In [638]: df.columns = pandas.MultiIndex.from_tuples([tuple(c.split('_')) for c in df.columns])
In [639]: df.stack(0).reset_index(1)
Out[639]:
level_1 x y
0 s1 0.672298 0.415366
0 s2 1.034770 0.556209
1 s1 0.067087 -0.851028
1 s2 0.053608 -0.276461
2 s1 -0.674174 -0.099015
2 s2 0.864148 -0.067240
3 s1 0.542996 -0.813018
3 s2 2.283530 2.793727
4 s1 0.216633 -0.091870
4 s2 -0.746411 -0.421852
5 s1 0.141301 -1.537721
5 s2 -0.371601 -1.594634
6 s1 1.267148 -0.833120
6 s2 0.369516 -0.671627
7 s1 -0.231163 -0.557398
7 s2 1.123155 0.865140
8 s1 1.790570 -0.428563
8 s2 0.668987 0.632409
9 s1 -0.820315 -0.894855
9 s2 0.673247 -1.195831
The transformation worked but in the process the column "names"
was lost. How can I keep the "names"
column in the df while still doing the melting transformation on the columns that have _
in their names? The "names"
column just assigns a unique name to each row in the dataframe. It's numeric here for example but in my data they are string identifiers.
thanks.
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…