Pandas adding values into the dataframe

Question

Here is my code where I'm trying to add/append new values to the third column:

        file = 'excel_name.xlsx'
        df = pd.read_excel(file, engine='openpyxl')
        print(df)
        df.insert(3, 'sub', [1, 7, 4, 1])
        print('test', df)

the output of print(df):

                      name  ...         sub
0                     test1  ...        NaN
1                     test2  ...        NaN
2                     test3  ...        NaN
3                     test4  ...        NaN

What I want to do:

                      name  ...         sub
0                     test1  ...        1
1                     test2  ...        7
2                     test3  ...        4
3                     test4  ...        1

Does your output come from the first print statement or the second? — Jock, Jul 15 '22 at 09:32
Please check out this solution : https://stackoverflow.com/a/51561012/8352083 — ZeroOne, Jul 15 '22 at 09:41

Hassan Rahamathullah · Answer 1 · 2022-07-15T09:39:23.660

2

you can do like this:

import pandas as pd
df = pd.read_excel('~/Downloads/excel_name.xlsx')
array = [1,4,7,1]
df['sub'] = array
print(df)

# output
    name  sub
0  test1    1
1  test2    4
2  test3    7
3  test4    1

Warning: Your array and the number of rows in your excel should match.

Edit: Your code also should work, please check the index which you are trying to add, if the last index of the df goes beyond three then it will throw error.

edited Jul 15 '22 at 09:39

answered Jul 15 '22 at 09:36

Hassan Rahamathullah

21
3

1

What's the solution if these two don't match? – Mehdi Aria Jul 15 '22 at 09:38
you have two solution, if you want to join across proper key, then you can use [pandas join](https://pandas.pydata.org/docs/user_guide/merging.html#database-style-dataframe-or-named-series-joining-merging) and second solution is, if you want the value to be sequentially added in the dataframe, then create array with null elements on the remaining value and use the above method. – Hassan Rahamathullah Jul 15 '22 at 09:50

score 2 · Accepted Answer · answered Jul 15 '22 at 09:48

2

you can use this. This will work for both even and uneven length of list which doesn't match for the length of columns

l = [1,7,4,1]
df.loc[range(len(l)),'sub'] = l

answered Jul 15 '22 at 09:48

Berlin Benilo

472
1
12

Pandas adding values into the dataframe

2 Answers2