0

I want to reshape a table in pandas . I have a table of the form:

date | country |state | population | num_cars
1    | c1      | s1   | 1          | 1
2    | c1      | s1   | 1          | 1
1    | c1      | s2   | 1          | 1
.
2    | c2      | s2   | 1          | 2
2    | c2      | s2   | 1          | 2

I want to turn it to this shape:

date |1_population | c1_s1_population | c1_s2_population...| c2_s1_populationc1_num_cars |c2_11_num_cars...

To explain, the initial data has pop and numbers by country, state for a date range. Now I want to convert into a number of columns of time series for each level (country, country-state)

How do I do this?

Dumbo
  • 1,068
  • 2
  • 12
  • 21
  • Possible duplicate of [how to switch columns rows in a pandas dataframe](https://stackoverflow.com/questions/31658183/how-to-switch-columns-rows-in-a-pandas-dataframe) – DJK Sep 26 '19 at 20:49
  • Can you post sample data? – Edeki Okoh Sep 26 '19 at 21:51

1 Answers1

1

As the source data sample, I used a DataFrame with 2 hypothetical countries, 3 states each:

    date country state  population  num_cars
0   1990     Xxx   Aaa         100        15
1   2010     Xxx   Aaa         120        18
2   1990     Xxx   Bbb          80         9
3   2010     Xxx   Bbb          88        11
4   1990     Xxx   Ccc          75         6
5   2010     Xxx   Ccc          82         8
6   1990     Yyy   Ggg          40         5
7   2010     Yyy   Ggg          50         6
8   1990     Yyy   Hhh          30         3
9   2010     Yyy   Hhh          38         4
10  1990     Yyy   Jjj          29         3
11  2010     Yyy   Jjj          35         4

To solve your problem, start with defining a reformatting function:

def reformat(grp, col):
    pop = grp[col]
    pop.name = grp.date.iloc[0]
    return pop

From a group of rows (grp) it takes a column of particular name (col), sets the name as date from the first row (the grouping key) and returns it.

As the initial step, group df by country and state:

gr = df.set_index(['country', 'state']).groupby('date')

Then compute 2 DataFrames, as the result of reformatting (applying the above function to each group, for both columns of interest:

df1 = gr.apply(reformat, col='population')
df2 = gr.apply(reformat, col='num_cars')

And having two partial results, merge them on indices:

pd.merge(df1, df2, left_index=True, right_index=True,
    suffixes=('_pop', '_cars'))

The result is:

country Xxx_pop         Yyy_pop         Xxx_cars         Yyy_cars        
state       Aaa Bbb Ccc     Ggg Hhh Jjj      Aaa Bbb Ccc      Ggg Hhh Jjj
date                                                                     
1990        100  80  75      40  30  29       15   9   6        5   3   3
2010        120  88  82      50  38  35       18  11   8        6   4   4

As you can see, the top level of MultiIndex on columns is "Country / population" and "Country / car No". The other level contains state names.

To trace how tis solution works, execute each step separately and inspect its result.

Valdi_Bo
  • 30,023
  • 4
  • 23
  • 41
  • how would you get everything on one level. So, let's keep these columns, as Xxx-AAA, Xxx_Bbb, .... and also add columns Xxx_pop at the same level so that Xxx_pop will be (1990: 355, 2010: 290 – Dumbo Sep 28 '19 at 03:07
  • - do one more groupby at country, and then merge ? or is there a standard, shorter way? – Dumbo Sep 28 '19 at 03:26
  • I took a "modular approach", so e.g. *reformat* function processes **only one** column. This is why later there is a need just to merge there two results (for both source columns). Of course, I don't insist that my approach is the only possible. I think it is quite elegant, so I presented just it, but maybe someone else will come up with a different solution. – Valdi_Bo Sep 28 '19 at 08:47