Data-frame manipulation in python

Question

I have a csv file with two columns of a and b as below:

a b
601 1
602 2
603 3
604 4
605 5
606 6

I want to read and save data in a new csv file as below:

s id
601 1
602 1
603 1
604 2
605 2
606 2

I have tried this code:

data=pd.read_csv('./dataset/test4.csv')
list=[]
i=0
while(i<6):
 list.append(data['a'].iloc[i:i+3])
 i+=3
df = pd.DataFrame(list)
print(df)

by this out put:

 0 1 2 3 4 5
a 601.0 602.0 603.0 NaN NaN NaN
a NaN NaN NaN 604.0 605.0 606.0

First I need to save the list in a dataframe with following result:

 0 1 2 3 4 5
 601.0 602.0 603.0 604.0 605.0 606.0

and then save in a csv file. However I've got stuck in the first part.

Thanks for your help.

• cs95: So, every 3 elements constitute a new group?
• Elham: yes.every 3 elements constitute a new group

Answer 1

Assuming every 3 items in a constitute a group in b, just do a little integer division on the index.

data['b'] = (data.index // 3 + 1)
data
 a b
0 601 1
1 602 1
2 603 1
3 604 2
4 605 2
5 606 2

Saving to CSV is straightforward - all you have to do is call df.to_csv(...).

---

Division by index is fine as long as you have a monotonically increasing integer index. Otherwise, you can use np.arange (on MaxU's recommendation):

data['b'] = np.arange(len(data)) // 3 + 1
data
 a b
0 601 1
1 602 1
2 603 1
3 604 2
4 605 2
5 606 2

• MaxU - stand with Ukraine: I'd use np.arange(len(data))//3 + 1 instead of (data.index // 3 + 1) - as it will also work for string/datetime/etc. indexes...
• cs95: @MaxU Thank you, I will incorporate that into my answer.

Answer 2

By using you output

df.stack().unstack()
Out[115]: 
 0 1 2 3 4 5
a 601.0 602.0 603.0 604.0 605.0 606.0

Data Input

df
 0 1 2 3 4 5
a 601.0 602.0 603.0 NaN NaN NaN
a NaN NaN NaN 604.0 605.0 606.0

Answer 3

In [45]: df[['a']].T
Out[45]:
 0 1 2 3 4 5
a 601 602 603 604 605 606

or

In [39]: df.set_index('b').T.rename_axis(None, axis=1)
Out[39]:
 1 2 3 4 5 6
a 601 602 603 604 605 606