CODEWITHSUNDEEP
pythonpandasdataframe
Jonathan
Jonathan

Reputation: 13

Reorganize/Restructure Pandas Dataframe

I want to reorganize my pandas dataframe.

Currently my dataframe looks like this:

Name C Nr Value R
1 B 1 1.1 p
1 B 2 1.3 p
1 G 1 2.4 p
1 G 2 2.5 p
2 B 1 4.4 n
2 B 2 8.0 n
2 G 1 8.1 n
2 G 2 7.0 n
3 B 1 9.2 p
3 G 1 6.5 p

and i want my dataframe to look like this

ID B1 B2 G1 G2 R
1 1.1 1.3 2.4 2.5 p
2 4.4 8.0 8.1 7.0 n
3 9.2 N/A 6.5 N/A p

So far i was not able to find a combination of groupby stack unstack to solve this problem.

Do you have any ideas?

The real dataframe is much bigger with ~10000 rows and the desired dataframe would have 800 columns

Upvotes: 1

Views: 39

Answers (1)

jezrael
jezrael

Reputation: 862681

Use DataFrame.pivot For MultiIndex in index and columns DataFrame and then map columns for flatten values:

df = df.pivot(index=['Name','R'], columns=['C','Nr'], values='Value')
df.columns = df.columns.map(lambda x: f'{x[0]}{x[1]}')
df = df.reset_index()
print (df)
   Name  R   B1   B2   G1   G2
0     1  p  1.1  1.3  2.4  2.5
1     2  n  4.4  8.0  8.1  7.0
2     3  p  9.2  NaN  6.5  NaN

If R column should be last:

df['R'] = df.pop('R')
print (df)

   Name   B1   B2   G1   G2  R
0     1  1.1  1.3  2.4  2.5  p
1     2  4.4  8.0  8.1  7.0  n
2     3  9.2  NaN  6.5  NaN  p

Upvotes: 1

Related Questions