2
$\begingroup$

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")
$\endgroup$
0

2 Answers 2

4
$\begingroup$

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)
$\endgroup$
8
  • 1
    $\begingroup$ This looks about right $\endgroup$
    – M__
    Commented Feb 28, 2022 at 2:33
  • 1
    $\begingroup$ 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. $\endgroup$
    – Supertech
    Commented Feb 28, 2022 at 13:38
  • 1
    $\begingroup$ 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. $\endgroup$
    – M__
    Commented Feb 28, 2022 at 13:49
  • 2
    $\begingroup$ 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. $\endgroup$
    – Supertech
    Commented Feb 28, 2022 at 21:05
  • 2
    $\begingroup$ 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. $\endgroup$
    – Supertech
    Commented Feb 28, 2022 at 21:05
2
$\begingroup$

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.

$\endgroup$
7
  • $\begingroup$ Thank you so much for explaining it so well $\endgroup$
    – Megha
    Commented Feb 28, 2022 at 8:43
  • $\begingroup$ 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. $\endgroup$
    – Supertech
    Commented Feb 28, 2022 at 13:45
  • 1
    $\begingroup$ 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. $\endgroup$
    – GOATNine
    Commented Feb 28, 2022 at 16:36
  • $\begingroup$ Yes, I remember now. This is much better $\endgroup$
    – M__
    Commented Feb 28, 2022 at 17:00
  • 2
    $\begingroup$ @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. $\endgroup$
    – Supertech
    Commented Feb 28, 2022 at 21:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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