How can I group different rows based on its value?-CodePudding

I have a data frame in pandas like this:

Attributes1	Attributes value1	Attributes2	Attributes value2
a	1	b	4
b	2	a	5

Does anyone know how can I get a new data frame like below?

a	b
1	2
5	4

Thank you!

CodePudding user response：

Try:

x = pd.DataFrame(
    df.apply(
        lambda x: dict(
            zip(x.filter(regex=r"Attributes\d $"), x.filter(like="value"))
        ),
        axis=1,
    ).to_list()
)

print(x)

Prints:

   a  b
0  1  4
1  5  2

CodePudding user response：

Hey you can try using pandas transpose function https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.transpose.html

CodePudding user response：

Use the transpose() function to transpose the rows to columns, then use the groupby() function to group the columns with the same name.

Also, in the future, please add what you've tried to do to solve the problem as well.

CodePudding user response：

We can do wide_to_long then pivot

s = pd.wide_to_long(df.reset_index(),
                    ['Attributes','Attributes value'], 
                    i = 'index',
                    j = 'a').reset_index().drop(['a'],axis=1)
s = s.pivot(*s)
Out[22]: 
Attributes  a  b
index           
0           1  4
1           5  2