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("DataFrame2 (Desired DataFrame)")

Snapshot can be found: [SnapShot of The DataFrame[1]

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 = df2[['GCF','Genome']]
df2['Genome'] = df2['Genome'].str.replace(' ', '_').str.replace('Unclassified','_').str.replace('__','_').str.replace('.','')
print("Updated table")
  • $\begingroup$ can you provide the snapshot of the dataframe. $\endgroup$ – Preetham Jan 21 at 11:04
  • $\begingroup$ @Preetham I just edited the post above :) What is on the left is what I have as input and on the right is what I desire as output $\endgroup$ – Biohacker Jan 21 at 11:34

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)
#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


    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)
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')
| improve this answer | |
  • $\begingroup$ thanks very much for your input on this! Looks great and a very nice approach taking into consideration the lists. I thought it might just be simpler with the normal pandas approach, but this is much better. Thanks once again :) $\endgroup$ – Biohacker Jan 22 at 14:05
  • $\begingroup$ @Biohacker Sure. Yes it is much simpler this way. However, the second approach is more used. Feel free to ask if there are further questions. $\endgroup$ – Fatemeh Asgarinejad Jan 22 at 21:29
  • $\begingroup$ I wanted to follow up with another question, I don't know why but I get the following error : organism = [df1['Organism'][i].replace(' ', '_') for i in range(len(df1))] #substituting ' ' by '_' AttributeError: 'float' object has no attribute 'replace' I tried changing all my data columns to strings and this didn't have any effect, some of my string values are a bit off I guess, see the example. $\endgroup$ – Biohacker Jan 29 at 16:30
  • $\begingroup$ Your [df1['Organism'] column might have some NULL values. You can check if it is true. $\endgroup$ – Fatemeh Asgarinejad Jan 29 at 18:36
  • 1
    $\begingroup$ Thanks for the input :) $\endgroup$ – Biohacker Feb 1 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.