Using multiple columns as index in panda dataframe

Question

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

hi, I don't understand your question : what do you mean by " i wish for the data to show up in Data2 on the same row as B14" ? — florian
– florian, Commented Mar 14, 2019 at 9:51
sorry for the ambiguity, basically i wish for the data to show up to the right of B14, i added my desired results in edits — Villager A
– Villager A, Commented Mar 14, 2019 at 10:00

John Sloper · Accepted Answer · 2019-03-14 09:51:52Z

1

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

answered Mar 14, 2019 at 9:51

John Sloper

1,84112 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Freek Wiekmeijer · Accepted Answer · 2019-03-14 10:16:27Z

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

Collectives™ on Stack Overflow

Using multiple columns as index in panda dataframe

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related