text: user4783: I have downloaded two files:
  File1.csv from:
  https://depmap.org/portal/download/data_slicer/download?file_path=20220227%2F3c335b7d-c18e-4788-b2e6-512d71f0a339%2Fexport.csv&name=Metabolomics.csv
  File 1 looks like
  ,2-aminoadipate,3-phosphoglycerate,alpha-glycerophosphate
  ACH-000698,6.1127272,6.034197900000001,5.8968963
  ACH-000489,5.5774126,5.7270453,5.1114679,6.07325
  
  File2.csv from:
  enter link description here
  https://cog.sanger.ac.uk/cmp/download/model_list_20220205.csv
  File2 looks like:
   model_id,model_name,synonyms,model_type
   SIDM01774,PK-59,,Cell Line,Adherent
   SIDM00192,SNU-1033,,Cell Line,Adherent
  
  I want to replace ACH-* ids with SIDM* ids in File1.csv. The ACH-* id's for SIDM* ids are given in the "BROAD_ID" (col.no =40) column in the File2.csv
  Desired output
  ,2-aminoadipate,3-phosphoglycerate,alpha-glycerophosphate
  SIDM00523,6.1127272,6.034197900000001,5.8968963
  SIDM00835,5.5774126,5.7270453,5.1114679,6.07325
  
  I want to do this with python script. I tried some initial steps. Please forgive any grave mistake, as I am a beginner in Python
   import pandas as pd
  
   df = pd.read_csv("File1.csv", index_col=0).T
  
   print(df)
  
   models = pd.read_csv("File2.csv", index_col="model_id", usecols=["model_id", "BROAD_ID"]).rename(columns={"model_id": "Model"})
  
   df = df.join(models).rename_axis(index="Model Name").reset_index()
  
   print(df)
  
   df2 = df.set_index("Model").drop(columns="Model Name")
  
   df2.to_csv("Meta.tsv", sep="\t")
  
   df2.index = df2.index + ".Demeter"
   df2.T.rename_axis(index="Gene").to_csv("Meta_2.tsv", sep="\t")
  
  
  user3848: The basic strategy of merge 'left' (which I think is default) is the correct thing to do. However, I would be concerned by lack of a key between the two data sets and basically the merging will take place on the row order. Within merge is on= then the shared key, in the code by @supertech there is left-on='ACH_number', I dunno because ACH_number does not appear to be shared. The code needs testing.
  If you assign an index it will attempt to merge on the index (which I think will therefore fail). What I think you want is the row order 1,2,3,4 in each dataframe to be the key that is merged. Therefore not specifying key might be the right thing to do, what can happen is it will then use the row order as the left-key. I cannot remember.
  The insert is correct to reorder the columns to get the desired output.
  Finally you want,
  del merged_df['column_name']
  
  for each column you don't want. There is a way to loop through this, but the above keeps things simple. You then do the standard csv dump.
  Note, see comments on drop by @GOATNine, which strongly preferred.
  Setting and unsetting indexes is key to manipulating pandas but it is within a specific contex.
  Summary the command merge and insert are correct with the addition of del however, the code needs testing regarding merge left-on and assigning the index. It is much easier to print the output and visualise it.
  
  user14180: I was not sure about the exact output (columns) you desired. The following code should inner merge both data frames. It writes a copy of 'model_list' column as a first column. You could remove any unwanted columns. I am sure there are more elegant ways of doing it. Hope it helps. It's totally different approach than yours.
  import pandas as pd
  
  metabolics = pd.read_csv("Metabolomics.csv", sep=",", header=0) 
  model_list = pd.read_csv("model_list_20220205.csv", sep=",", header=0) 
  metabolics=metabolics.rename(columns={'Unnamed: 0':'ACH_number'}) #rename the unnamed column.
  merged_df = pd.merge(metabolics, model_list, left_on='ACH_number', right_on='BROAD_ID', how='inner') #join two files
  
  #insert model_id column at the beginning
  #You will have dupicae 'model_id' column
  merged_df.insert(0, "model_id", merged_df['model_id'], allow_duplicates=True) 
  merged_df.to_csv('results.csv', sep=",", index=False)
  print(merged_df.shape)
  
  
original_text: 4783: <p>I have downloaded two files:</p>
  <p><strong>File1</strong>.csv from:
  <a href="https://depmap.org/portal/download/data_slicer/download?file_path=20220227%2F3c335b7d-c18e-4788-b2e6-512d71f0a339%2Fexport.csv&amp;name=Metabolomics.csv" rel="nofollow noreferrer">https://depmap.org/portal/download/data_slicer/download?file_path=20220227%2F3c335b7d-c18e-4788-b2e6-512d71f0a339%2Fexport.csv&amp;name=Metabolomics.csv</a></p>
  <p><strong>File 1</strong> looks like</p>
  <pre><code>,2-aminoadipate,3-phosphoglycerate,alpha-glycerophosphate
  ACH-000698,6.1127272,6.034197900000001,5.8968963
  ACH-000489,5.5774126,5.7270453,5.1114679,6.07325
  </code></pre>
  <p><strong>File2</strong>.csv from:
  enter link description here
  <a href="https://cog.sanger.ac.uk/cmp/download/model_list_20220205.csv" rel="nofollow noreferrer">https://cog.sanger.ac.uk/cmp/download/model_list_20220205.csv</a></p>
  <p>File2 looks like:</p>
  <pre><code> model_id,model_name,synonyms,model_type
   SIDM01774,PK-59,,Cell Line,Adherent
   SIDM00192,SNU-1033,,Cell Line,Adherent
  </code></pre>
  <p>I want to replace ACH-* ids with SIDM* ids in File1.csv. The ACH-* id's for SIDM* ids are given in the &quot;BROAD_ID&quot; (col.no =40) column in the File2.csv</p>
  <p>Desired output</p>
  <pre><code>,2-aminoadipate,3-phosphoglycerate,alpha-glycerophosphate
  SIDM00523,6.1127272,6.034197900000001,5.8968963
  SIDM00835,5.5774126,5.7270453,5.1114679,6.07325
  </code></pre>
  <p>I want to do this with python script. I tried some initial steps. Please forgive any grave mistake, as I am a beginner in Python</p>
  <pre><code> import pandas as pd
  
   df = pd.read_csv(&quot;File1.csv&quot;, index_col=0).T
  
   print(df)
  
   models = pd.read_csv(&quot;File2.csv&quot;, index_col=&quot;model_id&quot;, usecols=[&quot;model_id&quot;, &quot;BROAD_ID&quot;]).rename(columns={&quot;model_id&quot;: &quot;Model&quot;})
  
   df = df.join(models).rename_axis(index=&quot;Model Name&quot;).reset_index()
  
   print(df)
  
   df2 = df.set_index(&quot;Model&quot;).drop(columns=&quot;Model Name&quot;)
  
   df2.to_csv(&quot;Meta.tsv&quot;, sep=&quot;\t&quot;)
  
   df2.index = df2.index + &quot;.Demeter&quot;
   df2.T.rename_axis(index=&quot;Gene&quot;).to_csv(&quot;Meta_2.tsv&quot;, sep=&quot;\t&quot;)
  </code></pre>
   3848: <p>The basic strategy of <code>merge</code> 'left' (which I think is default) is the correct thing to do. However, I would be concerned by lack of a key between the two data sets and basically the merging will take place on the row order. Within <code>merge</code> is <code>on=</code> then the shared <code>key</code>, in the code by @supertech there is left-on='ACH_number', I dunno because <code>ACH_number</code> does not appear to be shared. The code needs testing.</p>
  <p>If you assign an index it will attempt to merge on the index (which I think will therefore fail). What I think you want is the row order 1,2,3,4 in each dataframe to be the key that is merged. Therefore not specifying <code>key</code> might be the right thing to do, what can happen is it will then use the row order as the left-key. I cannot remember.</p>
  <p>The <code>insert</code> is correct to reorder the columns to get the desired output.</p>
  <p>Finally you want,</p>
  <pre><code>del merged_df['column_name']
  </code></pre>
  <p>for each column you don't want. There is a way to loop through this, but the above keeps things simple. You then do the standard csv dump.</p>
  <p>Note, see comments on <code>drop</code> by @GOATNine, which strongly preferred.</p>
  <p>Setting and unsetting indexes is key to manipulating pandas but it is within a specific contex.</p>
  <p><strong>Summary</strong> the command <code>merge</code> and <code>insert</code> are correct with the addition of <code>del</code> however, the code needs testing regarding merge <code>left-on</code> and assigning the index. It is much easier to print the output and visualise it.</p>
   14180: <p>I was not sure about the exact output (columns) you desired. The following code should inner merge both data frames. It writes a copy of 'model_list' column as a first column. You could remove any unwanted columns. I am sure there are more elegant ways of doing it. Hope it helps. It's totally different approach than yours.</p>
  <pre><code>import pandas as pd
  
  metabolics = pd.read_csv(&quot;Metabolomics.csv&quot;, sep=&quot;,&quot;, header=0) 
  model_list = pd.read_csv(&quot;model_list_20220205.csv&quot;, sep=&quot;,&quot;, header=0) 
  metabolics=metabolics.rename(columns={'Unnamed: 0':'ACH_number'}) #rename the unnamed column.
  merged_df = pd.merge(metabolics, model_list, left_on='ACH_number', right_on='BROAD_ID', how='inner') #join two files
  
  #insert model_id column at the beginning
  #You will have dupicae 'model_id' column
  merged_df.insert(0, &quot;model_id&quot;, merged_df['model_id'], allow_duplicates=True) 
  merged_df.to_csv('results.csv', sep=&quot;,&quot;, index=False)
  print(merged_df.shape)
  </code></pre>
  