Joining values in 3D pandas array on ", ", making it a 2D array

Question

I have a 3D dataframe, and I want to get all values of one x,y index across the z axis, where the z axis here moves between the original 2D dataframes. The way I am able to imagine it although forgive me if I'm mistaken because it's a little weird to visualize, if I got a vector of the x,y of x=0, y=0 it would be [1, 5, 3].

So my result would be a dataframe, where the df_2d[0][0] would be a string "1, 5, 3", and so on, taking all the values in the 3D dataframe.

Is there any way I can achieve this without looping through each cell index and accessing the values explicitly?

The data frame is defined as:

import pandas as pd

columns = ['A', 'B']
index = [1, 2, 3]

df_1 = pd.DataFrame(data=[[1, 2], [99, 57], [57, 20]], index=index, columns=columns)
df_2 = pd.DataFrame(data=[[5, 6], [78, 47], [21, 11]], index=index, columns=columns)
df_3 = pd.DataFrame(data=[[3, 4], [66, 37], [33, 17]], index=index, columns=columns)

df_3d = pd.concat([df_1, df_2, df_3], keys=['1', '2', '3'])

And then to get the original data out I do:

print(df_3d.xs('1'))

print(df_3d.xs('2'))

print(df_3d.xs('3'))



    A   B
1   1   2
2  99  57
3  57  20

    A   B
1   5   6
2  78  47
3  21  11

    A   B
1   3   4
2  66  37
3  33  17

Again, to clarify, if looking at this print I would like to have a combined dataframe looking like:

    A              B
1  '1, 5, 3'     '2, 6, 4'
2  '99, 78, 66'  '57, 47, 37'
3  '57, 21, 33'  '20, 11, 17'

Hey. Just want to undertand which cells are you trying to access. *df_3d.xs('1') df_3d.xs('2') df_3d.xs('3')* ?? — NoorJafri, Apr 11 '19 at 12:34
maybe this helps https://stackoverflow.com/q/14507794/9754169 — Yuca, Apr 11 '19 at 13:03

ResidentSleeper · Accepted Answer · 2019-04-11T14:22:03.810

0

Use .xs to get each level dataframe and reduce to combine all dataframe together.

from functools import reduce

# Get each level values
dfs = [df_3d.xs(i) for i in df_3d.index.levels[0]]

df = reduce(lambda left,right: left.astype(str) + ", " + right.astype(str), dfs)

df
            A           B
1     1, 5, 3     2, 6, 4
2  99, 78, 66  57, 47, 37
3  57, 21, 33  20, 11, 17

And if you want ' you can use applymap to apply the function on every element.

df.applymap(lambda x: "'" + x + "'")

              A             B
1     '1, 5, 3'     '2, 6, 4'
2  '99, 78, 66'  '57, 47, 37'
3  '57, 21, 33'  '20, 11, 17'

Or df = "'" + df + "'"

df
              A             B
1     '1, 5, 3'     '2, 6, 4'
2  '99, 78, 66'  '57, 47, 37'
3  '57, 21, 33'  '20, 11, 17'

edited Apr 11 '19 at 14:22

answered Apr 11 '19 at 13:25

ResidentSleeper

2,385
2
10
20

That's great, thank you very much for your answer. However, here you are making the df 3D by putting the dfs into a list, not as a combined dataframe as shown in my example. – Simen Russnes Apr 11 '19 at 14:00
@simernes Edited. – ResidentSleeper Apr 11 '19 at 14:22

Joining values in 3D pandas array on ", ", making it a 2D array

1 Answers1