python - Add multiple DataFrame series to new series in same DataFrame

Question

I have a dataset in a .csv which I imported into a DataFrame using pandas, organized in the following manner (obviously not real numbers):

 A   B   C   D    E    F 
 0  20   4   24   8    28
 1  21   5   25   NA   NA 
 NA  NA  6   26   10   30
 3  23   NA  NA  11   31

What I want to achieve is to save the data in two extra columns G and H in the same DataFrame so I get the following:

A  B  C  D  E  F  G   H
                  0   20
                  1   21
                  ...  ...
                  11   31

Where I would like to keep the same index for all data (so B belongs to A, D to C, F to E etc.). As you can see, the original dataset has some missing values, so I would also like to skip these if they are in there.

Now, I have looked into pandas append and concat, however I do not see how I can achieve what I wanted, especially with skipping the empty values (presumbly via data.dropna() or some other function?).

Answer 1

For example, your dataframe looks like this:

        keywords1   keywords2   keywords3   key1    key2    col2
0       blue        red         green       1       5       NaN
1       big         NaN         medium      2       6       3
2       bat         ball        goal        3       7       0

Now lets assume that you need to add first three columns and add to another column. So,

col_1 = ['keywords1', 'keywords2', 'keywords3']
df["new_col_1"] = df[col_1].apply(lambda x: ''.join(x.dropna()), axis=1)

As I understood that your data contains basically string type data, the above code should work fine. Otherwise, you can convert the columns into string using the.apply(str) function.

Older Solution:

You can perform the operation and put it in a pandas.core.series.Series. For example:

df = pd.read_excel('sample_excel.xlsx', engine='openpyxl') df

The output will be:

  key1    key2
0  1      5
1  2      6
2  3      7

Now, let's do some operations:

df['key3'] = df['key1'] + df['key1']
df

The output is:

  key1    key2    key3
0  1      5       6
1  2      6       8
2  3      7       10

Now save the new dataframe as excel file:

df.to_excel('new_sample.xlsx')