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

CC BY-SA 4.0
2022-02-28T08:43:57.917Z
Thank you so much for explaining it so well
Megha
CC BY-SA 4.0
2022-02-28T13:45:00.303Z
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.
Supertech
CC BY-SA 4.0
2022-02-28T16:36:17.720Z
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.
GOATNine
CC BY-SA 4.0
2022-02-28T17:00:05.810Z
Yes, I remember now. This is much better
M__
CC BY-SA 4.0
2022-02-28T18:27:43.053Z
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.
Megha
CC BY-SA 4.0
2022-02-28T18:55:02.483Z
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
M__
CC BY-SA 4.0
2022-02-28T21:07:39.850Z
@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.
Supertech
CC BY-SA 4.0
2022-02-28T04:10:05.423Z
2022-02-28T17:01:29.700Z
2022-02-28T17:01:29.700Z
M__
<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>

CC BY-SA 4.0
2022-02-28T02:33:23.550Z
This looks about right
M__
CC BY-SA 4.0
2022-02-28T13:38:47.013Z
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.
Supertech
CC BY-SA 4.0
2022-02-28T13:49:11.043Z
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.
M__
CC BY-SA 4.0
2022-02-28T13:52:51.763Z
I am no expert either. But I will look into this as I start using it recently more often.
Supertech
CC BY-SA 4.0
2022-02-28T15:54:32.133Z
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.
Megha
CC BY-SA 4.0
2022-02-28T21:05:30.250Z
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.
Supertech
CC BY-SA 4.0
2022-02-28T21:05:34.383Z
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.
Supertech
CC BY-SA 4.0
2022-03-01T08:30:33.530Z
@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
Megha
CC BY-SA 4.0
2022-02-27T23:19:21.717Z
2022-02-27T23:19:21.717Z
Supertech
<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>

<python><data-download><data-preprocessing>
Replacing column 1 in file1 with matching ID's in file 2
# 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 @M__ '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**: @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  

---

### 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 @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.

> **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**: @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.  

---

