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 → stackoverflow.com/help/minimal-reproducible-example — Digital Farmer
– Digital Farmer, Commented Jul 16, 2022 at 15:40

Sunderam Dubey · Accepted Answer · 2023-02-19 16:46:43Z

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, 2023 at 16:46

Sunderam Dubey

8,83512 gold badges25 silver badges43 bronze badges

answered Jul 16, 2022 at 15:51

Ynjxsjmh

30.3k7 gold badges43 silver badges64 bronze badges

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

Collectives™ on Stack Overflow

Concate two dataframes by column

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related