Pandas melt with n columns and order control (counter)

user_10976654 0
Viewed 151

I have a dataset in wide format where the x and y ordinates for a line are in separate columns. The example below has only 4 ordinate pairs, but the actual dataset has a few dozen. There is a column with the number of ordinates (n in the example below).

How can I melt this dataframe with so many xn, yn columns? Can I do this without explicitly calling ('x1', 'y1', 'x2', 'y2', 'x3', 'y3', 'x4', 'y4' ... 'xn', 'yn')? I need to track the order, such that the (x1,y1) pair is counter 1; (x2,y2) is counter 2; etc.

idx = [1, 2, 3]
colA = [10, 5, 12]
n = [3, 2, 4]
x1 = [0, 1, 7]
y1 = [4, 0, 4]
x2 = [3, 2, 8]
y2 = [5, 1, 5]
x3 = [4, np.nan, 10]
y3 = [3,np.nan, 3]
x4 = [np.nan, np.nan, 11]
y4 = [np.nan, np.nan, 3]

df = pd.DataFrame(list(zip(idx, colA, n, 
                           x1, y1, x2, y2, x3, y3, x4, y4
                          )), 
                  columns =['idx', 'colA', 'n', 
                            'x1', 'y1', 'x2', 'y2', 
                            'x3', 'y3', 'x4', 'y4'
                           ])
display(df)
idx colA n x1 y1 x2 y2 x3 y3 x4 y4
1 10 3 0 4 3 5 4.0 3.0 NaN NaN
2 5 2 1 0 2 1 NaN NaN NaN NaN
3 12 4 7 4 8 5 10.0 3.0 11.0 3.0

Desired Output

idx colA counter x y
1 10 1 0 4
1 10 2 3 5
1 10 3 4 3
2 5 1 1 0
2 5 2 2 1
3 12 1 7 4
3 12 2 8 5
3 12 3 10 3
3 12 4 11 3
melt pandas python
2 Answers

Let us try wide_to_long

out = pd.wide_to_long(df,['x','y'],i=['idx','colA','n'],j='cnt').dropna().reset_index()
Out[8]: 
   idx  colA  n  cnt     x    y
0   1    10  3     1   0.0  4.0
1   1    10  3     2   3.0  5.0
2   1    10  3     3   4.0  3.0
3   2     5  2     1   1.0  0.0
4   2     5  2     2   2.0  1.0
5   3    12  4     1   7.0  4.0
6   3    12  4     2   8.0  5.0
7   3    12  4     3  10.0  3.0
8   3    12  4     4  11.0  3.0
user_79645270

wide_to_long solves this easily; pivot_longer from pyjanitor is an alternative :

# pip install pyjanitor
import janitor
import pandas as pd
df.pivot_longer(index = ['idx', 'colA', 'n'], 
                names_to = (".value", "counter"), 
                names_pattern=r"(.)(.)", 
                sort_by_appearance = True).dropna()
 
    idx  colA  n counter     x    y
0     1    10  3       1   0.0  4.0
1     1    10  3       2   3.0  5.0
2     1    10  3       3   4.0  3.0
4     2     5  2       1   1.0  0.0
5     2     5  2       2   2.0  1.0
8     3    12  4       1   7.0  4.0
9     3    12  4       2   8.0  5.0
10    3    12  4       3  10.0  3.0
11    3    12  4       4  11.0  3.0

the .value in names_to signifies that those parts of the column names should be retained, while the rest of the column names is loaded into counter. names_pattern is a regular expression of capture groups that signifies to names_to which parts of the column go to which.

user_71757130
Related