Concate two dataframes by column

Question

I have a problem with displaying my data in a correct format. What I want to do is display them side by side.

But when I'm doing:

gh = pd.concat([data[0], data[1]], keys=["Berlin", "London"], axis=1)

I get:

                   London               Berlin
                    val1 val2  val3      val1 val2  val3
                    mean mean  mean      mean  mean  mean
name      date
Berlin    2021-01    NaN  NaN   NaN     -3.13  0.11  4.42
          2021-02    NaN  NaN   NaN     -4.12  0.03  4.33
          2021-03    NaN  NaN   NaN      1.81  0.03  4.66

London    2021-01  -1.52  0.0  6.88       NaN   NaN   NaN
          2021-02  -2.20  0.0  7.44       NaN   NaN   NaN
          2021-03   3.16  0.0  7.05       NaN   NaN   NaN

The data is correct but this should look like this:

             London              Berlin
             val1 val2  val3     val1 val2  val3
             mean mean  mean     mean  mean  mean
   date
   2021-01  -1.52  0.0  6.88     -3.13  0.11  4.42
   2021-02  -2.20  0.0  7.44     -4.12  0.03  4.33
   2021-03   3.16  0.0  7.05      1.81  0.03  4.66

What can I do to get the data in the correct format?

If you can publish a reproducible example base Dataframe to make the case easier to see, we would appreciate it → https://stackoverflow.com/help/minimal-reproducible-example — Digital Farmer, Jul 16 '22 at 15:40

score 1 · Accepted Answer · edited Feb 19 '23 at 16:46

1

You can try DataFrame.xs to select data at a particular level of a MultiIndex so:

gh = pd.concat([data[0].xs("Berlin"), data[1].xs("London")], keys=["Berlin", "London"], axis=1)

edited Feb 19 '23 at 16:46

Sunderam Dubey

1
11
20
40

answered Jul 16 '22 at 15:51

Ynjxsjmh

28,441
6
34
52

Concate two dataframes by column

1 Answers1