Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-28T08:43:57.917Z
Answers.Text: Thank you so much for explaining it so well
Answers.UserDisplayName: Megha
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-28T13:45:00.303Z
Answers.Text: I do not know the reason why wget fails. Maybe someone else could help. I have clicked on the link on my Pc and it was sufficient to download. wget also failed for me.
Answers.UserDisplayName: Supertech
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-28T16:36:17.720Z
Answers.Text: If you're using pandas to drop the columns, you can just use df = df.drop(columns=['column1','column2'....]), or drop the assignment and use the option 'inplace=true' instead. This allows you to drop as many columns as you like at once, by making a string list of the column names.
Answers.UserDisplayName: GOATNine
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-28T17:00:05.810Z
Answers.Text: Yes, I remember now. This is much better
Answers.UserDisplayName: M__
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-28T18:27:43.053Z
Answers.Text: Could anyone please update the code. I am a begginer in python and I am not getting where should I include all the steps suggested. I want file1 with all the columns except column 1 (ACH-* ids) replaced by SIDM* id's.
Answers.UserDisplayName: Megha
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-28T18:55:02.483Z
Answers.Text: merge, insert, drop. I might test it if there are example tables (not links), or I was currently working with pandas. The info you supplied isn't clear enough for me so I can easily transfer it an IDE
Answers.UserDisplayName: M__
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-28T21:07:39.850Z
Answers.Text: @Megha see my comment under my answer. Your model_ file has anomalies in some of the cells therefore inner merge fails for 5 of the records.
Answers.UserDisplayName: Supertech
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-28T04:10:05.423Z
Answers.LastActivityDate: 2022-02-28T17:01:29.700Z
Answers.LastEditDate: 2022-02-28T17:01:29.700Z
Answers.UserDisplayName: M__
Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-28T02:33:23.550Z
Answers.Text: This looks about right
Answers.UserDisplayName: M__
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-28T13:38:47.013Z
Answers.Text: I agree with @M__ 's comment below. You would need to test the code make sure that merge on keys/columns as indicated give the desired output.
Answers.UserDisplayName: Supertech
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-28T13:49:11.043Z
Answers.Text: My pandas is rusty. The basic issue is that `joint` in SQL is `merge` in pandas. This might be why the OP used `joint` (except its pandas).  I was always told SQL `joint` merges via a common key and I carried that through into pandas.
Answers.UserDisplayName: M__
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-28T13:52:51.763Z
Answers.Text: I am no expert either. But I will look into this as I start using it recently more often.
Answers.UserDisplayName: Supertech
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-28T15:54:32.133Z
Answers.Text: Hi. I used the above script. There were two issues:
  1.  I am getting a result file with 973 rows, while the original file have 929 rows.
  2. I want only rows where a SIDM* code is available for ACH-*. Also in my desired output I don' t want the ACH-* code column.
Answers.UserDisplayName: Megha
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-28T21:05:30.250Z
Answers.Text: Code should be do the right thing. If you inner merge, your results file will have 923 lines excluding headers. Now, you file has 928 record. You are missing the following 5 records from results.> ACH-000338, ACH-000467, ACH-000710, ACH-000833, ACH-001063. The reason for is that one moodel_list file has irregular entries. For example if you look at the col-40, you will see that actual cell entry is "ACH-000338;ACH-000338", not "ACH-000338" thus pandas cannot merge these.
Answers.UserDisplayName: Supertech
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-28T21:05:34.383Z
Answers.Text: Either you live losing 5 records, or you need write another script that considers this kind of anomalies or update the tables. Hope this helps.
Answers.UserDisplayName: Supertech
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-03-01T08:30:33.530Z
Answers.Text: @Supertech: Thank you so much for getting back. The script is correctly getting the 923 matching id's. But when I saw the first column with awk. There were extra entries in the first column from the random columns of `model_list` file
Answers.UserDisplayName: Megha
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-27T23:19:21.717Z
Answers.LastActivityDate: 2022-02-27T23:19:21.717Z
Answers.UserDisplayName: Supertech
Body: <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>
  
Tags: <python><data-download><data-preprocessing>
Title: Replacing column 1 in file1 with matching ID's in file 2
ThreadText: # Replacing column 1 in file1 with matching ID's in file 2
  
  (Asked by: username_0 on 2022-02-27)
  
  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")
  
  ```
  
  **Tags:** python, data-download, data-preprocessing
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_2 on 2022-02-27. Score: 4 | Accepted answer
  
  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)
  
  ```
  
  > **Comments:**
  > **username_1**: This looks about right  
  > **username_2**: I agree with @username_1 's comment below. You would need to test the code make sure that merge on keys/columns as indicated give the desired output.  
  > **username_1**: My pandas is rusty. The basic issue is that `joint` in SQL is `merge` in pandas. This might be why the OP used `joint` (except its pandas).  I was always told SQL `joint` merges via a common key and I carried that through into pandas.  
  > **username_2**: I am no expert either. But I will look into this as I start using it recently more often.  
  > **username_0**: Hi. I used the above script. There were two issues: 1.  I am getting a result file with 973 rows, while the original file have 929 rows. 2. I want only rows where a SIDM* code is available for ACH-*. Also in my desired output I don' t want the ACH-* code column.  
  > **username_2**: Code should be do the right thing. If you inner merge, your results file will have 923 lines excluding headers. Now, you file has 928 record. You are missing the following 5 records from results.> ACH-000338, ACH-000467, ACH-000710, ACH-000833, ACH-001063. The reason for is that one moodel_list file has irregular entries. For example if you look at the col-40, you will see that actual cell entry is "ACH-000338;ACH-000338", not "ACH-000338" thus pandas cannot merge these.  
  > **username_2**: Either you live losing 5 records, or you need write another script that considers this kind of anomalies or update the tables. Hope this helps.  
  > **username_0**: @username_2: Thank you so much for getting back. The script is correctly getting the 923 matching id's. But when I saw the first column with awk. There were extra entries in the first column from the random columns of `model_list` file  
  
  ---
  
  ### Answer by username_1 on 2022-02-28. Score: 2
  
  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 @username_2 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 @username_3, 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.
  
  > **Comments:**
  > **username_0**: Thank you so much for explaining it so well  
  > **username_2**: I do not know the reason why wget fails. Maybe someone else could help. I have clicked on the link on my Pc and it was sufficient to download. wget also failed for me.  
  > **username_3**: If you're using pandas to drop the columns, you can just use df = df.drop(columns=['column1','column2'....]), or drop the assignment and use the option 'inplace=true' instead. This allows you to drop as many columns as you like at once, by making a string list of the column names.  
  > **username_1**: Yes, I remember now. This is much better  
  > **username_0**: Could anyone please update the code. I am a begginer in python and I am not getting where should I include all the steps suggested. I want file1 with all the columns except column 1 (ACH-* ids) replaced by SIDM* id's.  
  > **username_1**: merge, insert, drop. I might test it if there are example tables (not links), or I was currently working with pandas. The info you supplied isn't clear enough for me so I can easily transfer it an IDE  
  > **username_2**: @username_0 see my comment under my answer. Your model_ file has anomalies in some of the cells therefore inner merge fails for 5 of the records.  
  
  ---
  
  