How to return dataframe containing column names of multiple dataframe

Question

I have multiple dataframes and would like a dataframe that contains all column names from said multiple dataframes.

For example :

# Existing Dataframes
df1 =
    df1_colA  df1_colB  df1_colC
0   1         2         3
1   4         5         6
2   7         8         9

df2 =
    df2_colA  df2_colB  df3_colC
0   10        11        12
1   13        14        15
2   16        17        18

df3 =
    df3_colA  df3_colB  df3_colC
0   30        31        32
1   33        34        35
2   36        37        38

I would like to get a dataframe like this :

names =
     df_names   col_names
0    df1        df1_colA
1    df1        df1_colB
2    df1        df1_colC
3    df2        df2_colA
4    df2        df2_colB
5    df2        df2_colC
6    df3        df3_colA
7    df3        df3_colB
8    df3        df3_colC

Help would be very appreciated and thank you in advance!

jezrael · Accepted Answer · 2022-05-09 05:36:35Z

If possible extract DataFrame names fom columns names use list comprehension with concat and last for new column in first position use DataFrame.insert with Series.str.extractSeries.str.extractall for values from columnsnames before _:

dfs = [df1, df2, df3]
df = pd.concat([df.columns.to_frame(name='col_names') for df in dfs], ignore_index=True)
df.insert(0, 'df_names', df['col_names'].str.extract('^(.*)_'))
print (df)
  df_names col_names
0      df1  df1_colA
1      df1  df1_colB
2      df1  df1_colC
3      df2  df2_colA
4      df2  df2_colB
5      df3  df3_colC
6      df3  df3_colA
7      df3  df3_colB
8      df3  df3_colC

Similar ide with flatten list comprehension:

dfs = [df1, df2, df3]
df = pd.DataFrame({'col_names': [x for df in dfs for x in df.columns]})
df.insert(0, 'df_names', df['col_names'].str.extract('^(.*)_'))
print (df)
  df_names col_names
0      df1  df1_colA
1      df1  df1_colB
2      df1  df1_colC
3      df2  df2_colA
4      df2  df2_colB
5      df3  df3_colC
6      df3  df3_colA
7      df3  df3_colB
8      df3  df3_colC

Alternative is create dictionary of DataFrames and in dict comprehension use concat, keys of dict create first level of MultiIndex, so not necessary parse columns names:

dfs = {'df1':df1, 'df2':df2, 'df3':df3}
df = (pd.concat({k:v.columns.to_frame(name='col_names') for k, v in dfs.items()})
        .droplevel(1)
        .rename_axis('df_names')
        .reset_index())

print (df)
  df_names col_names
0      df1  df1_colA
1      df1  df1_colB
2      df1  df1_colC
3      df2  df2_colA
4      df2  df2_colB
5      df2  df3_colC
6      df3  df3_colA
7      df3  df3_colB
8      df3  df3_colC

BeRT2me · Accepted Answer · 2022-05-09 05:37:13Z

1

dfs = [df1, df2, df3]
df = pd.DataFrame({'col_names': pd.concat(dfs).columns})
df['df_names'] = df['col_names'].str.split('_').str[0]
print(df)

Output:

  col_names df_names
0  df1_colA      df1
1  df1_colB      df1
2  df1_colC      df1
3  df2_colA      df2
4  df2_colB      df2
5  df2_colC      df2
6  df3_colA      df3
7  df3_colB      df3
8  df3_colC      df3

answered May 9, 2022 at 5:37

BeRT2me

13.3k2 gold badges18 silver badges39 bronze badges

Comments

Ynjxsjmh · Accepted Answer · 2022-05-09 05:30:53Z

0

You can try

dfs = [df1, df2, df3]

df = (pd.DataFrame({'col_names': [df.columns.tolist() for df in dfs]})
      .explode('col_names', ignore_index=True)
      .pipe(lambda df: df.assign(df_names=df['col_names'].str.split('_').str[0])))

print(df)

  col_names df_names
0  df1_colA      df1
1  df1_colB      df1
2  df1_colC      df1
3  df2_colA      df2
4  df2_colB      df2
5  df3_colC      df3
6  df3_colA      df3
7  df3_colB      df3
8  df3_colC      df3

If the order matters

df.insert(0, 'df_names', df.pop('df_names'))

print(df)

  df_names col_names
0      df1  df1_colA
1      df1  df1_colB
2      df1  df1_colC
3      df2  df2_colA
4      df2  df2_colB
5      df3  df3_colC
6      df3  df3_colA
7      df3  df3_colB
8      df3  df3_colC

answered May 9, 2022 at 5:30

Ynjxsjmh

30.3k7 gold badges43 silver badges64 bronze badges

1 Comment

BeRT2me Over a year ago

df = df[['df_names', 'col_names']] is probably an easier way of swapping the column orders in this case~

sammywemmy · Accepted Answer · 2022-05-09 06:24:13Z

One option is to append the columns (they are indexes), and repeat the df_names with the lengths of the columns for each dataframe, before creating a final dataframe:

dfs = [df1, df2, df3]

col_names = df1.columns.append([df.columns for df in dfs[1:]])

lengths = [len(df) for df in dfs] # or [*map(len, dfs)]

# only useful if you have lots of dataframes
# else, it is just easier to write ['df1', 'df2', 'df3']
df_names = [f"df{num+1}" for num, _ in enumerate(dfs)]

df_names = np.repeat(df_names, lengths)

df = {'df_names' : df_names, 'col_names': col_names}

pd.DataFrame(df, copy = False)


  df_names col_names
0      df1  df1_colA
1      df1  df1_colB
2      df1  df1_colC
3      df2  df2_colA
4      df2  df2_colB
5      df2  df3_colC
6      df3  df3_colA
7      df3  df3_colB
8      df3  df3_colC

Collectives™ on Stack Overflow

How to return dataframe containing column names of multiple dataframe

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related