CODEWITHSUNDEEP
pythonpandas
ns12345
ns12345

Reputation: 3105

Python dataframe transpose where some rows have multiple values

I've a dataframe:

field,value
a,1  
a,2  
b,8

I want to pivot it to this form 

a,b  
1,8  
2,8

Upvotes: 2

Views: 96

Answers (4)

piRSquared
piRSquared

Reputation: 294218

  • set_index with a cumcount on each field group + field
  • unstack + ffill
df.set_index(
    [df.groupby('field').cumcount(), 'field']
).value.unstack().ffill().astype(df.value.dtype)

field  a  b
0      1  8
1      2  8

Upvotes: 2

jezrael
jezrael

Reputation: 862406

print (df)
   0  1
0  a  1
1  a  2
2  b  8

Solution with creating groups for new index by GroupBy.cumcount, then pivot and fill forward missing values:

g = df.groupby(0).cumcount()
df1 = pd.pivot(index=g, columns=df[0], values=df[1]).ffill().astype(int)
        .rename_axis(None, axis=1)
print (df1)
   a  b
0  1  8
1  2  8

Another solution creates groups with apply and reshape by unstack:

print (df.groupby(0).apply(lambda x: pd.Series(x[1].values)).unstack(0).ffill().astype(int)
         .rename_axis(None, axis=1))
   a  b
0  1  8
1  2  8

Upvotes: 2

Mohammad Yusuf
Mohammad Yusuf

Reputation: 17054

You can do like so:

# df = pd.read_clipboard(sep=',')
df.pivot(columns=field, values=value).bfill().dropna()

Upvotes: 2

Shubham R
Shubham R

Reputation: 7644

A much simpler solution would just be to do DataFrame.T (transpose)

df_new = df.T

Upvotes: 0

Related Questions