Break down column to row values

Question

I have a data frame where I have a column named B which has apples and oranges as the data. How can I basically transform these values as separate columns in the same data frame.

Below is my code-

s={'A':[1,1,2,2],'B':['Apples','Oranges','Apples',"Oranges"],'C':[2014,2014,2016,2016],'value':[2,3,4,5]}
p=pd.DataFrame(data=s)

The O/p should be column with A, Apples, Oranges, C

How can I get this requirement done?

`new_df = p.pivot(index=['A', 'C'], columns='B', values='value').reset_index()` — Henry Ecker, Feb 23 '22 at 20:14
Every pandas DataFrame has an index whether that’s the default range index or something else. There is no way to remove it from the DataFrame object. What do you mean "don't need an index" in this context? — Henry Ecker, Feb 23 '22 at 21:37
@HenryEcker I don't want A or C as the index, just the nominal ones i.e. 0,1,2,3... — John, Feb 23 '22 at 23:31
Oh okay, the `reset_index()` at the end of the code in my first comment does that already no? — Henry Ecker, Feb 23 '22 at 23:55

score 1 · Accepted Answer · answered Feb 23 '22 at 20:10

import pandas as pd

s={'A':[1,1,2,2],'B':['Apples','Oranges','Apples',"Oranges"],'C':[2014,2014,2016,2016],'value':[2,3,4,5]}
p=pd.DataFrame(data=s)

frames = []
for u, v in p.groupby("B"):
    frames.append(v.rename(columns={"value": u}).drop(columns=["B"]))

pd.merge(frames[0], frames[1], how="inner", left_on=["A", "C"], right_on=["A", "C"])

results in

This assumes, that A is some kind of "index" and that the values in C are the same across the rows.

Break down column to row values

1 Answers1