python - How to set column as index in a DataFrame

Question

Given below is my current pandas DataFrame:

         Balance before Salary   Salary
Month                                 
Jun-18                  27.20  15300.0
Jul-18                  88.20  15300.0
Aug-18                 176.48  14783.0
Sep-18                  48.48  16249.0
Oct-18                 241.48  14448.0
Nov-18                  49.48  15663.0

is it possible to convert the above DataFrame in the below format?

                        Month1  Month2  Month3  Month4  Month5  Month6
Balance before Salary   27.2    88.2    176.48  48.48   241.48  49.48
Salary                 15300    15300   14783   16249   14448   15663

Code

df = pd.DataFrame(salary_List)
newdf = df.groupby('date').sum()
newdf = df.groupby(pd.Grouper(key='date', freq='1M')).sum()
newdf.index = newdf.index.strftime('%b-%y')
newdf.index.name = 'Month'

Can anyone please help me on this?

Do you need transpose ? df = df.T ?

jezrael
– jezrael

2019-01-02 09:51:43 +00:00
Commented Jan 2, 2019 at 9:51 — jezrael
– jezrael, Commented Jan 2, 2019 at 9:51
How to change the column heading to Month1 Month2 etc..?

vrinda
– vrinda

2019-01-02 09:55:26 +00:00
Commented Jan 2, 2019 at 9:55 — vrinda
– vrinda, Commented Jan 2, 2019 at 9:55
Check my answer.

jezrael
– jezrael

2019-01-02 09:56:01 +00:00
Commented Jan 2, 2019 at 9:56 — jezrael
– jezrael, Commented Jan 2, 2019 at 9:56

jezrael · Accepted Answer · 2019-01-02 10:01:43Z

3

I think you need transpose by T and then if necessary change column names add list comprehension:

Notice:
Double groupby+sum is not necessary, once is enough, because aggregate same aggregate function, here sum.

df = pd.DataFrame(salary_List)

newdf = df.groupby(pd.Grouper(key='date', freq='1M')).sum().T
#python 3.6+
newdf.columns = [f'Month{x}' for x in range(1, len(newdf.columns) + 1)]

#python bellow
#newdf.columns = ['Month{}'.format(x) for x in range(1, len(newdf.columns) + 1)]
print (newdf)
                        Month1   Month2    Month3    Month4    Month5  \
Balance before Salary     27.2     88.2    176.48     48.48    241.48   
Salary                 15300.0  15300.0  14783.00  16249.00  14448.00   

                         Month6  
Balance before Salary     49.48  
Salary                 15663.00

edited Jan 2, 2019 at 10:01

answered Jan 2, 2019 at 9:54

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

1 Comment

anky Over a year ago

Nice use of column name formatting. :) +1 from me, pl have a look at my method too, got an idea. :)

anky · Accepted Answer · 2019-01-02 10:34:09Z

1

You an also get the Month and Year(for better clarity) and then transpose:

resetting the index to get the Month as a column:

df1 = df1.rename_axis('Month').reset_index()
df1.drop([0],inplace=True)

Then:

df['Month'] = df['Month'].apply( lambda x : pd.to_datetime(x).strftime('%b %Y')) # gets name of month and year
df_new = df1.T #transpose and save to new df
df_new.columns = df_new.iloc[0] # set columnnames as monthnames
df_new.drop('Month',inplace=True) # drop the extra row Month

Output:

edited Jan 2, 2019 at 10:34

answered Jan 2, 2019 at 10:11

anky

75.3k11 gold badges46 silver badges76 bronze badges

4 Comments

jezrael Over a year ago

Yes, but OP have a bit different input data, so your solution not working. Input data are column date fille by datetimes, so working newdf = df.groupby(pd.Grouper(key='date', freq='1M')).sum() very nice. Output of first DataFrame is after newdf.index.name = 'Month'

anky Over a year ago

I tested this with the same data that the OP has given, seems to work for me.

jezrael Over a year ago

Sure, but Given below is my current pandas DataFrame: is after paragraph of code

df = pd.DataFrame(salary_List) newdf = df.groupby('date').sum() newdf = df.groupby(pd.Grouper(key='date', freq='1M')).sum() newdf.index = newdf.index.strftime('%b-%y') newdf.index.name = 'Month'

anky Over a year ago

got it, i had the Month as a column.

Petros · Accepted Answer · 2019-01-02 09:55:27Z

0

What you want to do is called 'transpose'. You can get the transposed dataframe by calling the df.transpose() function. So for you simply :

df = pd.DataFrame(salary_List)
newdf = df.transpose()

Then with a simple loop you may change the column names to the ones yoyu want.

answered Jan 2, 2019 at 9:55

Petros

3773 silver badges15 bronze badges

Collectives™ on Stack Overflow

python - How to set column as index in a DataFrame

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related