如何连接这三个数据框

Question

我有三个数据框，分别是男性，女性，变性人

示例数据帧

df_Male

continent   avg_count_country   avg_age
  Asia          55                5
  Africa        65                10
  Europe        75                8

df_Female

continent   avg_count_country   avg_age
  Asia          50                7
  Africa        60                12
  Europe        70                0

df_Transgender

continent   avg_count_country   avg_age
  Asia          30                6
  Africa        40                11
  America       80                10

现在我像下面这样连接

frames = [df_Male, df_Female, df_Transgender]
df = pd.concat(frames, keys=['Male', 'Female', 'Transgender'])

正如你所看到的America存在于df_transgender，同样明智的欧洲存在于df_Male和df_Female

所以我必须把它浓缩成下面的样子，但不是手动的，因为有很多行

              continent  avg_count_country  avg_age
Male        0      Asia                 55        5
            1    Africa                 65       10
            2    Europe                 75        8
            3    America                 0        0
Female      0      Asia                 50        7
            1    Africa                 60       12
            2    Europe                 70        0
            3    America                 0        0
Transgender 0      Asia                 30        6
            1    Africa                 40       11
            2    America                80       10
            3    Europe                 0         0

所以对于其他continent值avg_count_country和avg_age应该是0

Answer 1

利用^{}，对@jpp的答案稍加修改就可以避免手动操作索引：

df = pd.concat([df_Male.assign(gender='Male'),
                df_Female.assign(gender='Female'),
                df_Transgender.assign(gender='Transgender')])

df.pivot('gender', 'continent').fillna(0).stack().astype(int)

                       avg_count_country  avg_age
gender      continent
Female      Africa                    60       12
            America                    0        0
            Asia                      50        7
            Europe                    70        0
Male        Africa                    65       10
            America                    0        0
            Asia                      55        5
            Europe                    75        8
Transgender Africa                    40       11
            America                   80       10
            Asia                      30        6
            Europe                     0        0

Answer 2

你可以重新索引一下。你知道吗

from itertools import product

# Get rid of that number in the index, not sure why you'd need it
df.index = df.index.droplevel(-1)
# Add continents to the index
df = df.set_index('continent', append=True)

# Determine product of indices
ids = list(product(df.index.get_level_values(0).unique(), df.index.get_level_values(1).unique()))

# Reindex and fill missing with 0
df = df.reindex(ids).fillna(0).reset_index(level=-1)

df现在是：

            continent  avg_count_country  avg_age
Male             Asia               55.0      5.0
Male           Africa               65.0     10.0
Male           Europe               75.0      8.0
Male          America                0.0      0.0
Female           Asia               50.0      7.0
Female         Africa               60.0     12.0
Female         Europe               70.0      0.0
Female        America                0.0      0.0
Transgender      Asia               30.0      6.0
Transgender    Africa               40.0     11.0
Transgender    Europe                0.0      0.0
Transgender   America               80.0     10.0

如果你想要另一个数字索引，那么你可以： df.groupby(df.index).cumcount()对每组中的值进行编号。你知道吗

Answer 3

可以在连接之前添加“性别”列。你知道吗

我们使用Categorical Data和groupby来计算笛卡尔积。这也会带来性能上的好处。你知道吗

df = pd.concat([df_Male.assign(gender='Male'),
                df_Female.assign(gender='Female'),
                df_Transgender.assign(gender='Transgender')])

for col in ['gender', 'continent']:
    df[col] = df[col].astype('category')

res = df.groupby(['gender', 'continent']).first().fillna(0).astype(int)

print(res)

                       avg_count_country  avg_age
gender      continent                            
Female      Africa                    60       12
            America                    0        0
            Asia                      50        7
            Europe                    70        0
Male        Africa                    65       10
            America                    0        0
            Asia                      55        5
            Europe                    75        8
Transgender Africa                    40       11
            America                   80       10
            Asia                      30        6
            Europe                     0        0