Combining Pandas Dataframes

user_2582017 0
Viewed 83

I am pretty new in Pandas. So please bear with me. I have a df like this one

DF1
column1     column2(ids)   
a          [1,2,13,4,9]
b          [20,14,10,18,17]
c          [6,8,12,16,19]
d          [11,3,15,7,5]

Each number in each list corresponds to the column id in a second dataframe.

DF2
id.  value_to_change. 
1      x1
2      x2
3      x3
4      x4
5      x5
6      x6
7      x7
8      x8
9      x9 
.      .
.      .
.      .
20    x20

STEP1

  1. I want to iterate each list and select the rows in DF2 with the matching ids, AND create 4 dataframes since I have 4 rows in DF1. How to do this?

So for instance for the first row after applying the logic i would get this back

 id.    value_to_change
    1      x1
    2      x2
    13     x13
    14     x14
    9      x9

The second row would give me

  id.     value_to_change
    20      x20
    14      x14
    10      x10
    18      x18
    17      x17

And so on...

STEP 2

Once I have these 4 dataframes, i pass them as argument to a logic which returns me 4 dataframes. 2) How could I combine them into a sorted final one?

DF3
id.  new_value
1      y1
2      y2
3      y3
4      y4
5      y5
6      y6
7      y7
8      y8
9      y9 
.      .
.      .
.      .
20    y20

how could I go about this?

pandas dataframe python
4 Answers

It would be much easier and efficient to use a single dataframe like so

Initialization

df1 = pd.DataFrame({'label': ['A', 'B', 'C', 'D'], 'ids': [[1,2,13,4,9], 
[20,14,10,18,17], [6,8,12,16,19],[11,3,15,7,5]]})

# Some custom function for dataframe operations
def my_func(x):
     x['value_to_change'] = x.value_to_change.str.replace('x', 'y') 
     return x

Dataframe Operations

df1 = df1.explode('ids')
df1['value_to_change'] = df1.explode('ids')['ids'].map(dict(zip(df2.ids, df2.val)))
df1['new_value'] = df1.groupby('label').apply(my_func)['value_to_change']

Output

  label ids value_to_change new_value
0     A   1              x1        y1
0     A   2              x2        y2
0     A  13             x13       y13
0     A   4              x4        y4
0     A   9              x9        y9
1     B  20             x20       y20
1     B  14             x14       y14
1     B  10             x10       y10
1     B  18             x18       y18
1     B  17             x17       y17
2     C   6              x6        y6
2     C   8              x8        y8
2     C  12             x12       y12
2     C  16             x16       y16
2     C  19             x19       y19
3     D  11             x11       y11
3     D   3              x3        y3
3     D  15             x15       y15
3     D   7              x7        y7
3     D   5              x5        y5
user_51200490

This code will help with the first part of the problem.

import pandas as pd
df1 = pd.DataFrame([[[1,2,4,5]],[[3,4,1]]], columns=["column2(ids)"])
df2 = pd.DataFrame([[1,"x1"],[2,"x2"],[3,"x3"],[4,"x4"],[5,"x5"]], columns=["id", "value_to_change"])
df3 = pd.DataFrame(columns=["id", "value_to_change"])
for row in df1.iterrows():
    s = row[1][0]
    for item in s:
        val = df2.loc[df2['id']==item, 'value_to_change'].item()
        df_temp = pd.DataFrame([[item,val]], columns=["id", "value_to_change"])
        df3 = df3.append(df_temp, ignore_index=True)
df3

Note in the line s=row[1][0], you need to choose the index according to your dataframe, in my case it was [1][0]

Output

-For second part you can use pd.concat: Documentation

-For sorting df.sort_values: Documentation

user_146774350

First things first, this code should do what you want.

import pandas as pd

idxs = [
    [0,2],
    [1,3],
]

df_idxs = pd.DataFrame({'idxs': idxs})

df = pd.DataFrame(
    {'data': ['a', 'b', 'c', 'd']}
)

frames = []
for _, idx in df_idxs.iterrows():
    rows = idx['idxs']
    frame = df.loc[rows]
    # some logic
    print(frame)
    #collect
    frames.append(frame)
    
pd.concat(frames)

Note that pandas automatically creates a range index of none is passed. If you want to select on a different column, set that one as index, or use

df.loc[df.data.isin(rows)]

.

The pandas doc on split-apply-combine may also interest you: https://pandas.pydata.org/pandas-docs/stable/user_guide/groupby.html

user_106396940
  1. Use .loc and .isin to get new Dataframe with required rows in df2
  2. Do your logic on these 4 dataframes
  3. combine the resulting 4 dataframes using pandas.concat()
  4. sort the dataframe by ids using .sort_values()

Code:

import pandas as pd

df1 = pd.DataFrame({'column1     ': ['A', 'B', 'C', 'D'], 'ids': [[1,2,13,4,9], [20,14,10,18,17], [6,8,12,16,19],[11,3,15,7,5]]})
df2 = pd.DataFrame({'ids': list(range(1,21)), 'val': [f'x{x}' for x in range(1,21)]})

df_list=[]
for id_list in df1['ids'].values:
    df_list.append(df2.loc[df2['ids'].isin(id_list)])

# do logic on each DF in df_list

# assuming df_list now contains the resulting dataframes
df3 = pd.concat(df_list)
df3 = df3.sort_values('ids')
user_153478050
Related