Pandas using loc for assignment in a Multi Index DataFrame

Question

I have initialised a dataframe like this:

df = pd.DataFrame(columns=["stockname","timestamp","price","volume"])
df.timestamp = pd.to_datetime(df.timestamp, format = "%Y-%m-%d %H:%M:%S:%f")
df.set_index(['stockname', 'timestamp'], inplace = True)

Now I get stream of data from somewhere but for the sake of program let me write it like this:

filehandle = open("datasource")

for line in filehandle:
    line = line.rstrip()
    data = line.split(",")
    stockname = data[4]
    price = float(data[3])
    timestamp = pd.to_datetime(data[0], format = "%Y-%m-%d %H:%M:%S:%f")
    volume = int(data[6])

    df.loc[stockname, timestamp] = [price, volume]

filehandle.close()

print df

but this is giving error:

ValueError: cannot set using a multi-index selection indexer with a different length than the value

You know, you dont have to do all the heavy work of striping, spliting, just use pd.read_csv. If you add the sample of text file I would show you how to do that. — Bharath M Shetty
– Bharath M Shetty, Commented Dec 10, 2017 at 9:57

Bharath M Shetty · Accepted Answer · 2017-12-10 10:12:24Z

11

Specify the column names you are assigning data to i.e

df = pd.DataFrame(columns=["a","b","c","d"])
df.set_index(['a', 'b'], inplace = True)

df.loc[('3','4'),['c','d']] = [4,5]

df.loc[('4','4'),['c','d']] = [3,1]

      c    d
a b          
3 4  4.0  5.0
4 4  3.0  1.0

Also if you have a comma separated file then you can use read_csv i.e :

import io
import pandas as pd
st = '''2017-12-08 15:29:58:740657,245.0,426001,248.65,APPL,190342,2075673,249.35,244.2
        2017-12-08 16:29:58:740657,245.0,426001,248.65,GOOGL,190342,2075673,249.35,244.2
        2017-12-08 18:29:58:740657,245.0,426001,248.65,GOOGL,190342,2075673,249.35,244.2
        '''
#instead of `io`, add the source name
df = pd.read_csv(io.StringIO(st),header=None)
# Now set the index and select what you want 
df.set_index([0,4])[[1,7]]

                                   1       7
 0                          4                   
2017-12-08 15:29:58.740657 APPL   245.0  249.35
2017-12-08 16:29:58.740657 GOOGL  245.0  249.35
2017-12-08 18:29:58.740657 GOOGL  245.0  249.35

edited Dec 10, 2017 at 10:12

answered Dec 10, 2017 at 9:42

Bharath M Shetty

30.6k6 gold badges65 silver badges111 bronze badges

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

8 Comments

Tahseen Over a year ago

this worked. Now you see I have stockname and timeframe. I want to access all data of a particular stock but I cannot write df[df.index == "XYZ"] so how do I write to access a particular stock data from dataframe ?

Bharath M Shetty Over a year ago

@Tahseen I need to see how the data actually looks like

Tahseen Over a year ago

2017-12-08 15:29:58:740657,245.0,426001,248.65,APPL,190342,2075673,249.35,244.2

Tahseen Over a year ago

this one line of log and there would be many with same or different time stamp and same or different stockname. So in a single file, I am storing, multiple stocks tick prices at any particular time. That is why I indexed time and stockname

Tahseen Over a year ago

But this has worked perfectly file for me the way you did. Thanks

|

Sultan1991 · Accepted Answer · 2020-08-27 06:03:51Z

2

You might want to use df.at[index, column_name] = value to escape this error

answered Aug 27, 2020 at 6:03

Sultan1991

1016 bronze badges

Comments

AndreyF · Accepted Answer · 2017-12-10 09:44:39Z

1

I think what you are looking for is:

df.loc[a,b,:] = [c,d]

Here is an example with your dataframe:

for i in range(3):
    for j in range(3):
        df.loc[(str(i),str(j)),:] = [i,j]

Output:

edited Dec 10, 2017 at 9:44

answered Dec 10, 2017 at 9:07

AndreyF

1,8381 gold badge15 silver badges26 bronze badges

Collectives™ on Stack Overflow

Pandas using loc for assignment in a Multi Index DataFrame

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related