Python-Pandas convert columns into rows

Question

My database from excel has some information by Country for Years. The problem is each year is a different column header. For example:

Country      Indicator   1950    1951    1952
Australia       x         10      27     20
Australia       y          7      11      8
Australia       z         40      32     37

I want to convert each Indicator as a column header and make a column by year. Like this:

Country         year          x       y     z
Australia       1950         10       7     40
Australia       1951         27      11     32
Australia       1952         20       8     37

And I don't know how many countries are in the column. Years = 1950 to 2019

When you say database, do you mean you are using a SQL database or are you talking about an excel sheet? — jjgg
– jjgg, Commented May 9, 2020 at 0:44

BENY · Accepted Answer · 2020-05-09 00:47:00Z

3

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

answered May 9, 2020 at 0:47

BENY

324k22 gold badges176 silver badges250 bronze badges

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

Comments

sammywemmy · Accepted Answer · 2020-05-09 01:08:23Z

This is just an exploration ... @Yoben's solution is the proper way to do it via Pandas ... I just seeing what other possibilities there are :

#create a dictionary of the years
years = {'Year' : df.filter(regex='\d').columns}

#get the data for the years column
year_data = df.filter(regex='\d').to_numpy()

#create a dictionary from the indicator and years columns pairing
reshaped = dict(zip(df.Indicator,year_data))
reshaped.update(years)

#create a new dataframe
pd.DataFrame(reshaped,index=df.Country)

            x   y   z   Year
Country             
Australia   10  7   40  1950
Australia   27  11  32  1951
Australia   20  8   37  1952

You should never have to do this, as u could easily work within the dataframe, without the need to create a new one. The only time u may consider this is for the speed. Besides that, just something to explore

GeneticsGuy · Accepted Answer · 2020-05-09 00:46:48Z

It's not exactly what you are looking for, but if your dataframe is the variable df, you can use the transpose method to invert the dataframe.

In [7]: df                                                                                           
Out[7]: 
   col1   col2  col3
0     1   True    10
1     2  False    10
2     3  False   100
3     4   True   100

Transpose

In [8]: df.T                                                                                         
Out[8]: 
         0      1      2     3
col1     1      2      3     4
col2  True  False  False  True
col3    10     10    100   100

I think you have a multi-index dataframe so you may want to check the documentation on that.

Collectives™ on Stack Overflow

Python-Pandas convert columns into rows

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related