Convert Columns into Rows With Pandas

Convert columns into rows with Pandas

UPDATE

From v0.20, melt is a first order function, you can now use

df.melt(id_vars=["location", "name"], 
        var_name="Date", 
        value_name="Value")

  location    name        Date  Value
0        A  "test"    Jan-2010     12
1        B   "foo"    Jan-2010     18
2        A  "test"    Feb-2010     20
3        B   "foo"    Feb-2010     20
4        A  "test"  March-2010     30
5        B   "foo"  March-2010     25

---

OLD(ER) VERSIONS: <0.20

You can use pd.melt to get most of the way there, and then sort:

>>> df
  location  name  Jan-2010  Feb-2010  March-2010
0        A  test        12        20          30
1        B   foo        18        20          25
>>> df2 = pd.melt(df, id_vars=["location", "name"], 
                  var_name="Date", value_name="Value")
>>> df2
  location  name        Date  Value
0        A  test    Jan-2010     12
1        B   foo    Jan-2010     18
2        A  test    Feb-2010     20
3        B   foo    Feb-2010     20
4        A  test  March-2010     30
5        B   foo  March-2010     25
>>> df2 = df2.sort(["location", "name"])
>>> df2
  location  name        Date  Value
0        A  test    Jan-2010     12
2        A  test    Feb-2010     20
4        A  test  March-2010     30
1        B   foo    Jan-2010     18
3        B   foo    Feb-2010     20
5        B   foo  March-2010     25

(Might want to throw in a .reset_index(drop=True), just to keep the output clean.)

Note: pd.DataFrame.sort has been deprecated in favour of pd.DataFrame.sort_values.

pandas python convert some columns into rows

There is problem in columns is MultiIndex:

print (df.columns)
MultiIndex([('Tahun',),
            (  'Jan',),
            (  'Feb',),
            (  'Mar',),
            (  'Apr',),
            (  'Mei',),
            (  'Jun',),
            (  'Jul',),
            (  'Ags',),
            (  'Sep',),
            (  'Okt',),
            (  'Nov',),
            (  'Des',)],
           )

Possible solution:

df = pd.DataFrame(output, columns=column_[0])

Or:

column_ = []
for column in columns:
    cols = column.find_all('th')
    cols = [item.text.strip() for item in cols]
    column_.extend([item for item in cols if item])
...

df = pd.DataFrame(output, columns=column_)

---

Last solution with melt working well.

Convert some columns into row and one column's rows into columns in python

Pivot and stack

df.pivot('City', 'Sales').stack(0).rename_axis(['City', 'Year'])

---

Sales        X    Y    Z
City Year               
A    2016  100   90  130
     2017  120  120  160
     2018  160  130  190
B    2016  200  290  230
     2017  220  220  260
     2018  260  230  290
C    2016  300  390  330
     2017  320  320  360
     2018  360  330  390

Python dataframe : converting columns into rows

If possible number in column names more like 9 use Series.str.extract for get integers with values before to MultiIndex to columns, so possible DataFrame.stack:

df = df.set_index('Movie')
df1 = df.columns.to_series().str.extract('([a-zA-Z]+)(\d+)')
df.columns = pd.MultiIndex.from_arrays([df1[0], df1[1].astype(int)])

df = df.rename_axis((None, None), axis=1).stack().reset_index(level=1, drop=True).reset_index()
print (df)
                      Movie FirstName  ID LastName
0  The Shawshank Redemption       Tim  TM  Robbins
1  The Shawshank Redemption    Morgan  MF  Freeman
2             The Godfather    Marlon  MB   Brando
3             The Godfather        Al  AP   Pacino

If not use indexing for get last values of columns names with all previous and pass to MultiIndex.from_arrays:

df = df.set_index('Movie')
df.columns = pd.MultiIndex.from_arrays([df.columns.str[:-1], df.columns.str[-1].astype(int)])
df = df.stack().reset_index(level=1, drop=True).reset_index()
print (df)
                      Movie FirstName  ID LastName
0  The Shawshank Redemption       Tim  TM  Robbins
1  The Shawshank Redemption    Morgan  MF  Freeman
2             The Godfather    Marlon  MB   Brando
3             The Godfather        Al  AP   Pacino

Python-Pandas convert columns into rows

We can do format with stack and unstack

df.set_index(['Country','Indicator']).stack().unstack(level=1).reset_index()
Indicator    Country level_1   x   y   z
0          Australia    1950  10   7  40
1          Australia    1951  27  11  32
2          Australia    1952  20   8  37

How to convert groups of columns into rows in Pandas?

You can use:

df1 = df.set_index('Name')
df1.columns = df1.columns.str.split('in', expand=True)

df2 = (df1.stack()
          .sort_index(axis=1, ascending=False)
          .rename_axis(index=['Name', 'Hour number'])
          .add_suffix('in the hr')
          .reset_index()
      )

df2['Hour number'] = df2['Hour number'].str.extract(r'(\d+)')

Result:

print(df2)

  Name Hour number  Time worked in the hr  Time wasted in the hr
0  foo           1                     45                     15
1  foo           2                     40                     20
2  bar           1                     35                     25
3  bar           2                     55                      5
4  baz           1                     50                     10
5  baz           2                     45                     15

Convert columns to rows by index value with Pandas

One way is to set_index + stack:

out = df.set_index(['Name','Identifier']).stack().droplevel(-1).reset_index(name='Tags')

Output:

                Name  Identifier                            Tags
0   Bottle Nose Well        1345          A- Groundwater Aquifer
1   Bottle Nose Well        1345        WL - Water Level Network
2   Bottle Nose Well        1345             104 - Area Wide Map
3   Bottle Nose Well        1345              110 - Area Network
4   Bottle Nose Well        1345      114 - Area Wide Monitoring
5     BMOs Adventure        3745         A - Groundwater Aquifer
6     BMOs Adventure        3745             HR - Domestic Wells
7     BMOs Adventure        3745  15 - Baylor County Well Survey
8     BMOs Adventure        3745             20- Data collection
9     BMOs Adventure        3745      3 - Water Level Measurable
10    BMOs Adventure        3745                      6 - Onsite
11    BMOs Adventure        3745               9 - Water Quality

Convert column with more than one string into rows with Pandas

You can try explode the split list Class column

out = (df.assign(Class=df['Class'].str.split(','))
       .explode('Class', ignore_index=True))

print(out)

   Name         Submitted At       Class
0   Bob  2022/08/12 23:56:42        Math
1   Bob  2022/08/12 23:56:42     English
2   Bob  2022/08/12 23:56:42     History
3  John  2022/08/12 23:56:42     English
4  John  2022/08/12 23:56:42     History
5   Ric  2022/08/12 23:56:42        Math
6   Ric  2022/08/12 23:56:42   Chemistry

---

Related Topics