CODEWITHSUNDEEP
pythonpandas
Villager A
Villager A

Reputation: 73

Using multiple columns as index in panda dataframe

I apologize if my question is rudimentary or has already been answered, I am still very new to programming.

I am trying to write a python scrips to automate the processing of a bunch of .csv files and write the data to different columns depending on which column the ID is on

for example,

import pandas as pd
df = pd.DataFrame({'ID1': ["A12", "A13", "A14"],'Data1': [0,0,0], 
               'ID2': ["B12", "B13", "B14"],'Data2': [0,0,0],})

giving 

      ID1  Data1  ID2  Data2
0  A12      0  B12      0
1  A13      0  B13      0
2  A14      0  B14      0

lets say I have the data for B14, I wish for the data to show up in Data2 on the same row as B14. using df.iloc is out of the question because I have around 400 data sets arranges over 8 columns.

my desired results are 

      ID1  Data1  ID2  Data2
0  A12      0  B12      0
1  A13      0  B13      0
2  A14      0  B14      somedata

Upvotes: 2

Views: 82

Answers (2)

Freek Wiekmeijer
Freek Wiekmeijer

Reputation: 4940

import pandas as pd
df = pd.DataFrame({'ID1': ["A12", "A13", "A14"],'Data1': [0,0,0], 
           'ID2': ["B12", "B13", "B14"],'Data2': [0,0,0],})

DataFrame is now:

   ID1  Data1  ID2  Data2
0  A12      0  B12      0
1  A13      0  B13      0
2  A14      0  B14      0

Add multi-level index:

df.set_index(['ID1', 'ID2'], inplace=True)

DataFrame is now:

         Data1  Data2
ID1 ID2              
A12 B12      0      0
A13 B13      0      0
A14 B14      0      0

Query on secondary index:

df2.xs('B14', level=1)

Results in:

     Data1  Data2
ID1              
A14      0      0

Upvotes: 0

John Sloper
John Sloper

Reputation: 1821

A bit unsure what you are asking here. If you want to insert data into the data frame in column Data2 where the ID2 is B14 you can do it like this:

df.loc[df.ID2 == "B14", "Data2"] = 1

Upvotes: 1

Related Questions