Stack data from columns to rows in pandas dataframe

Question

I am trying to stack financial values year wise in pandas Dataframe. But not able to get started.

All I have tried is

df1 = df.set_index(['refnum','y1gp','y2gp','y3gp']).stack()\
.reset_index(name='REV').rename(columns={'level_5':'Year'})

Existing:

refnum	y1	y1rev	y1gp	y2	y2rev	y2gp	y3	y3rev	y3gp
10001	2021	300	200	2022	100	600	2023	300	300
10002	2020	300	200	2021	200	500	2022	300	300
10003	2021	300	200	2022	500	500	2023	300	300

Expected:

refnum	year	REV	GP	Base Year
10001	2021	300	200	BaseYear
10001	2022	100	600	BaseYear+1
10001	2023	300	300	BaseYear+2
10002	2020	300	200	BaseYear
10002	2021	200	500	BaseYear+1
10002	2022	300	300	BaseYear+2
10003	2021	300	200	BaseYear
10003	2022	500	500	BaseYear+1
10003	2023	300	300	BaseYear+2

But is it just the literal words "BaseYear", "BaseYear+1" etc? not some calculation? — Henry Ecker
– Henry Ecker ♦, Commented Jul 15, 2021 at 22:07

Andrej Kesely · Accepted Answer · 2021-07-15 22:20:11Z

4

Try:

df.columns = [re.sub(r"y(\d+)(.*)", r"\2-\1", c) for c in df.columns]
x = (
    pd.wide_to_long(
        df, stubnames=["", "gp", "rev"], sep="-", i="refnum", j="Base Year"
    )
    .rename(columns={"": "year"})
    .reset_index()
    .sort_values(by="refnum")
)
print(x)

Prints:

   refnum  Base Year  year   gp  rev
0   10001          1  2021  200  300
3   10001          2  2022  600  100
6   10001          3  2023  300  300
1   10002          1  2020  200  300
4   10002          2  2021  500  200
7   10002          3  2022  300  300
2   10003          1  2021  200  300
5   10003          2  2022  500  500
8   10003          3  2023  300  300

answered Jul 15, 2021 at 22:20

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Henry Ecker Over a year ago

Interesting, I didn't know you could use empty string as a stubname. Way cool! I was going to use wide_to_long but I wanted to avoid the sort step.

Henry Ecker · Accepted Answer · 2021-07-15 22:13:49Z

Let's convert the headers to a useable MultiIndex using str.replace and str.split then stack to go from wide-form to long. Then groupby cumcount to create the BaseYear column.

# Save Columns
df = df.set_index('refnum')
# Create a MultiIndex with Numbers at the end and split into multiple levels
df.columns = (
    df.columns.str.replace(r'^(.*?)(\d+)(.*)$', r'\1\3/\2', regex=True)
        .str.split('/', expand=True)
)
# Wide Format to Long + Rename Columns
df = df.stack().droplevel(-1).reset_index().rename(
    columns={'y': 'Year', 'ygp': 'GP', 'yrev': 'REV'}
)
# Add Base Year Column
df['BaseYear'] = "BaseYear+" + df.groupby('refnum').cumcount().astype(str)
# df['BaseYear'] = df.groupby('refnum').cumcount()  # (int version)

df:

   refnum  Year   GP  REV    BaseYear
0   10001  2021  200  300  BaseYear+0
1   10001  2022  600  100  BaseYear+1
2   10001  2023  300  300  BaseYear+2
3   10002  2020  200  300  BaseYear+0
4   10002  2021  500  200  BaseYear+1
5   10002  2022  300  300  BaseYear+2
6   10003  2021  200  300  BaseYear+0
7   10003  2022  500  500  BaseYear+1
8   10003  2023  300  300  BaseYear+2

sammywemmy · Accepted Answer · 2021-07-15 23:44:46Z

You could use pivot_longer from pyjanitor; for this scenario, you pass regexes to names_pattern, and the new column names in names_to:

# pip install pyjanitor
import janitor
import pandas as pd
df.pivot_longer(index='refnum', 
                names_to=['year', 'REV', 'GP'], 
                names_pattern=['^y\d$', '.*rev$', '.*gp$']
               )

   refnum  year  REV   GP
0   10001  2021  300  200
1   10002  2020  300  200
2   10003  2021  300  200
3   10001  2022  100  600
4   10002  2021  200  500
5   10003  2022  500  500
6   10001  2023  300  300
7   10002  2022  300  300
8   10003  2023  300  300

If you want the base year included, you can modify the column labels that end with a number, before using pivot_longer:

(df.rename(columns = lambda col: f"{col}YEAR" 
                                 if col.endswith(('1','2','3')) 
                                 else col)
   .pivot_longer(index='refnum', 
                 names_to= ("Base Year", ".value"), 
                 names_pattern=r".(\d)(.+)", 
                 sort_by_appearance=True)
 )

   refnum Base Year  YEAR  rev   gp
0   10001         1  2021  300  200
1   10001         2  2022  100  600
2   10001         3  2023  300  300
3   10002         1  2020  300  200
4   10002         2  2021  200  500
5   10002         3  2022  300  300
6   10003         1  2021  300  200
7   10003         2  2022  500  500
8   10003         3  2023  300  300

labels associated with .value stay as column headers, while the rest are lumped into a new column (base year)

Collectives™ on Stack Overflow

Stack data from columns to rows in pandas dataframe

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related