Renaming Column Elements of DataFrames

Question

I want to create a new DataFrame based upon the columns of another DataFrame, for which I would like to do the following:

1. Data Retrieval: Take partial strings from column elements from DataFrame1
2. Renaming Column Elements: Combine these strings and place into 2 new columns in DataFrame2

An example of the DataFrames can be seen below, where DataFrame1 is my actual data and the DataFrame2 is the type of data frame that I am interested in obtaining:

import pandas as pd
df1 = pd.DataFrame({'BGC Name': ['Hm106_120.region001', 'MBT13_26.region001',
'MBT23_64.region001', 'MBT36_100.region001'], 'GCF No': [1813, 1813, 1883,
1887], 'Organism': ['Streptomyces sp', 'Streptomyces sp', 'Listeria sp',
'Streptomyces sp'], 'BGC Class': ['PKSI', 'NRPS', 'PSKI', 'NRPS']})

df2 = pd.DataFrame({'GCF': ['PKSI_GCF1813', 'NRPS_GCF1813', 'PKSI_GCF1883',
'NRPS_GCF1887'],'Genome': ['Streptomyces_sp_Hm106', 'Streptomyces_sp_MBT13',
'Listeria_sp_MBT23', 'Streptomyces_sp_MBT36']})

print("DataFrame1 (Original DataFrame)")
print(df1)
print("DataFrame2 (Desired DataFrame)")
print(df2)

Snapshot can be found: [

The Code That I am trying to use

#Dataframe Copies
df1 = df1.copy() #self copy
df1.columns = df1.columns.str.strip().str.lower().str.replace(' ', '_').str.replace('(', '').str.replace(')', '')
df2 = df1.copy() #copy of original dataframe
#Change Column Names
df1.columns = ['BGC', 'GCF', 'Description', 'Product Prediction', 'BiG-SCAPE class', 'Organism']
df2.columns = ['BGC', 'GCF', 'Description', 'Product Prediction', 'BiG-SCAPE class', 'Organism']
#DataFrame Manipulation
df2['GCF'] = df1['BiG-SCAPE class'].astype(str) + df2['GCF'].astype(str)
df1[['BGC','BGC2']] = df1['BGC'].str.split('_',n=1,expand=True)
df2['Genome'] = df1['Organism'].astype(str) + df1['BGC'].astype(str)
df2.set_index('GCF')
df2 = df2[['GCF','Genome']]
df2['Genome'] = df2['Genome'].str.replace(' ', '_').str.replace('Unclassified','_').str.replace('__','_').str.replace('.','')
print(df1)
print("Updated table")
print(df2)

Answer 1

Here, first I created the column values as a list and then assigned them to be the new dataframe's columns. However, we could do it using the columns and without creating lists. It just seems easier this way.

#Initializing the new_df (df2) dataframe
import pandas as pd
new_columns =  ['GCF', 'Genome']
new_df  = pd.DataFrame(columns = new_columns)
#new_df
#df1.columns
#creating the column GCF of the new dataframe 
GCF = [str(df1['BGC Class'][i])+ '_GCF' + str(df1['GCF No'][i]) for i in range(len(df1))]
#creating the new column 'Genome' of the new dataframe
organism = [df1['Organism'][i].replace(' ', '_') for i in range(len(df1))] #substituting ' ' by '_'
BGC_name = [list(df1['BGC Name'].str.split('_'))[i][0] for i in range(len(df1))]
genome = [organism[i] + '_' + BGC_name[i] for i in range(len(df1))]
new_df['GCF'] = GCF
new_df['Genome'] = genome

Output:

    GCF             Genome
0   PKSI_GCF1813    Streptomyces_sp_Hm106
1   NRPS_GCF1813    Streptomyces_sp_MBT13
2   PSKI_GCF1883    Listeria_sp_MBT23
3   NRPS_GCF1887    Streptomyces_sp_MBT36

The second way in which we do not create lists and change the columns' values sort of inplace, is as following:

#Initializing the new_df (df2) dataframe
import pandas as pd
new_columns =  ['GCF', 'Genome']
new  = pd.DataFrame(columns = new_columns)
#new_df
#df1.columns
BGC_name = [list(df1['BGC Name'].str.split('_'))[i][0] for i in range(len(df1))]
new['GCF'] = df1['Organism'].str.replace(' ', '_')+ '_' + BGC_name
new['Genome'] = df1['BGC Class']+ '_GCF'+ df1['GCF No'].astype('str')
#new